First Timer: Product Inventory Table

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.

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.

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.

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.