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.

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.

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.

So for the Color table and ColorProduct table would it look like this?

Color Table:

Product Key          Color

1                      Red

2                      Blue

3                      Clear

4                      Purple

ect....

ColorProduct Table:

Product Key   Product Name         Color

?            Men's Wicking Polo    2

what would be the key?  (this is assuming that the polo is blue.)

I hate saying this but can you please dumb it down some? This is not easy for me to understand without having someone sitting next to me guiding me through it.

Close

Color Table:

ColorID          Color

1                      Red

2                      Blue

3                      Clear

4                      Purple

ect....

ColorProduct Table:

Primary Key   ProductID               ColorID

?             Men's Wicking Polo    2

A Primary Key is not necessary for the ColorProduct Table since its a junction table. You can either set ProductID and ColorID as a composite key or place a unique index on the combination. However, if you want you can add a Autonumber as the PK.