First Timer: Product Inventory Table

Your Inventory table is not correctly normalized as it includes redundancies.  You need to decompose it into set of related tables like this:

Products:

....ProductID (PK)

....ProductName

....SupplierID

....etc.

Colors

....Color (PK)

Sizes

....Size (PK)

You have a many-to-many relationship type between Products and Colors, and between Products and Sizes.  A many-to-many relationship type is modelled by a table which resolves the relationship type into two or more one-to-many relationship types.  So to model the relationship type between Products and Colors you'd have a table:

ProductColors

....ProductID (FK)

....Color (FK)

and for that between Products and Sizes:

ProductSizes

....ProductID (FK)

....Size (FK)

The primary key of each of these tables is a composite one made up of the two columns in each case; the tables are said to ne 'all key'.  These two tables are themselves related in a many-to-many relationship type, which is modelled by a further table.  It is this table which is the Inventory table if you are simply recording the current stock position and storage location for each product:

Inventory

....ProductID (FK)

....Color (FK)

....Size (FK)

....StockInHand

....StorageLocation

The primary key of this table is again a composite one, this time made up of the three columns ProductID, Color, and Size,  As no part of a primary key can be Null, each product must have both a colour and a size.  This won't be appropriate in some cases of course, so in the both the Sizes and Colors table you must include a row with a value N/A or similar.  The other imporatnt point is that ProductID and Color on the one hand, and ProductID and Size on the other hand are each composite foreign keys referencing the composite primary keys of ProductColors and ProductSizes respectively, so the relationships with thse tables should be on the two columns in each case.

You might be wondering why ProductSizes and ProductColors tables are needed at all.  It would be possible to operate the database without these tables, with the Inventory table modelling a ternary (3-way) many-to-many relationship type between Products, Colors and Sizes, but it would be perfectly possible to insert a row into this table which included a size or colour inappropriate to the product in question.

If we take Men's Wicking Polo as an example, there would be one row for this in Products.  If this is available in S, M and L sizes there would be three rows in ProductSizes with the same ProductID value, 42 say, and S, M and L in the Size column.  If it is available in red and blue in the S and M sizes, but only in blue in the L size, then in the Inventory table there would be the following rows:

42    S    Red

42    S    Blue

42    M   Red

42    M   Blue

42    L    Blue

In each row would be the current stock in hand, storage location etc of each in other columns in the table.

This is a very simple inventory system where the quantities in stock are updated as items are added to or removed from stock.  In most cases however, the stock level would not be stored, but computed on the basis of individual transactions.  The current stock in hand is simply the sum of the quantities per item added to stock less the sum of the quantities per item removed from stock.  You'll find a very simple example of this which I put together a while ago for another user here as Inventory.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

As well as purchases and sales you'd need to take account of other things such as stock written off or adjustments as a result of a stock-take.  In real life inventory databases can be very complex.

Ok so I have my Product table

ProductID (FK)

SupplierID (FK)

ProductName

BUT when I try to save it, it says ' Index or primary key cannot contain a null value'.

So for the Color table and ColorProduct table would it look like this?y for me to understand without having someone sitting next to me guiding me through it. 

Not quite; what you'd have would be this:

Table Name: Products

ProductID    ProductName

42                Men's Wicking Polo

Table Name: Colors

Color

Red

Blue

Table Name: Sizes

Size

S

M

L

XL

Table Name: ProductColors

ProductID    Color

42               Red

42               Blue

Table Name: ProductSizes

ProductID    Size

42               S

42               M

Table Name: Inventory

ProductID    Color    Size    StockInHand

42               Red      S        15

42               Red     M         8

42               Blue     M       11

42               Blue     L          5

These rows mean that there are 15 small red men's wicking polo shirts in stock, 8 medium red, 11 medium blue and 5 large blue.  Further rows would represent other colors and sizes of men's wicking polo shirts stocked and the same would be true of other products represented in the Products table and for which there would also be rows in the ProductColors and ProductSizes tables which would represent the colors and sizes appropriate to each product.  Note that there must be at least one row in each of these tables, so if we take your example of Crinkle Cut Paper, for which we'll assume a ProductId of 66, there would be one row in Products for this, two rows in ProductColors and, as this product apparently does not have sizes a row in ProductSizes:

ProductID    Size

66               N/A

The Sizes table would have a row with a value N/a.  The ProductSizes table is modelling a many-to-many relationship type between Products and Sizes.  It's key is a composite one of the two columns because, in combination, these must be distinct within the table, e.g. you can only have one row with a ProductID of 42 and a Size of M.  Even if you were to introduce an autonumber column ProductSizeID as the primary key the other two columns would still be a key, what's known as a candidate key, so would have to be included in a unique index to prevent duplicates.

What you have here is a set of tables. each of which represents and entity type, e.g. products or colors, but some of those entity types are relationship types between other entity type, e.g. ProductColors.  The tables are normalized, which is the formal process for eliminating redundancy by 'decomposing' a table which includes redundancy in to two or more related tables.  Redundancy is when the same 'fact' is stated more than once in a table, in separate rows, or in rows in different tables.  This allows for inconsistent data to be entered and puts the integrity of the database at risk.

It's important to understand that the entity type and relationship types in a database have an existence in the real world, because a relational database is a model of the underlying reality.  Designing a relational database is essentially the identification of these real world entity types and relationship types and representing them as related tables.  If the database accurately models the reality it will behave in the same way as the reality being modelled, which is of course exactly what we want it to do.  So, the importance of getting the model right cannot be overstressed.  Once you've done that the interface in terms of the forms and subforms required will by and large be self evident.

Although it has nothing to do with your specific requirement you might find it helpful to take a look at the file ParentActivities.zip in my SkyDrive folder to which I posted a link earlier.  This illustrates a basic many-to-many relationship type, between parents and activities (it was originally produced for a user wanting this) and how that relationship type is modelled by tables, and how this is represented in a form/subform.  These are the basic building blocks you'll need to use in develop[ping your own application.

PK is Primary Key (which could be your Product Key). And yes, FK is Foreign Key.

Stock-Take generally refers to a physical Inventory count. Inventory applications handle current inventory in one of two ways, by physical counts or by transactions. Most use transactions. This means you need a table that records every movement of stock either in or out. So if John needs 3 shirts to give to clients he's going golfing with, a transaction is entered indicating John took 3 shirts. Which 3 shirts John took depends on how you set up the app. Other transactions that affect inventory are write offs (you change your log and give old logo items to charity) or shrinkage (moths got into a box of shirts and they were thrown out).

I agree with Ken's recommendation that you create a more vertical structure. The difference between the Product table and the Inventory table is that the Inventory table is used to identify individual items based on ALL their attributes. The Product table is used to identify a class of product (i.e. Men's wicking Polos or Crinkle Paper). These classes of product are further identified by color and size.

The difference between the Color and ProductColor should be evident. Color just identifies colors and can be used with any product. ProductColor identifies a specific product by their color.

What does FK stand for? Foreign Key?  I am assuming PK = product key. Just as an add in...i am not familar with stock and inventory terminology. Stock written off? stock-take? I was just told to make an inventory database in Access without every doing this before. So what your saying is that I should still keep the Inventory table but add a colors and sizes table? What would be the difference between the Product table and Invenotry table in your scenario? As well as the difference between the Color and ProductColor tables and Size and ProductSize table?

I understand what you mean by normalizing it and some of the break down, but I'm not quite grasping the your explainations and reasonings. I would really like to impress my boss and your help is very appreciated.