First Timer: Product Inventory Table

Anonymous
2013-04-03T18:19:00+00:00

Creating a Product Inventory database to keep track of all the promotional items my company has. So far I have three tables: Suppliers, Inventory, and Event. In my Inventory table I have the following columns (in order): Product ID, Product Name, Description, Logo, Color, Size, Total Quantity, Supplier, Event, and Storage. There are so many different styles of shirts that we have accumulated over the years that for every shirt style there are multiple sizes and sometimes multiple colors. So with that, I have choosen to add the size and/or color to the product name as well as in the size and color columns.

For example:

Men's Wicking Polo: Small                             S

Men's Wicking Polo: Medium                        M

Blue Crinkle Cut Paper                                           Blue

Purple Crinkle Cut Paper                                        Purple

Will this create a problem? I didn't know how else to set it up because it didn't think the database would recognize that five Crinkle Cut Papers are different colors (making it five individual products because of the colors), if the product name on all of them is the same.

One more: I tried to run some relationships like between the Suppliers table and the suppliers column under Inventory but it didn't work.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2013-04-03T19:32:33+00:00

    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.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments