I'm setting up a database for a school project, and I'm wondering how to handle sizes, colors, and quantity (OnHand).

Currently, in my Products table, I have:

ProductId, ProductName, Category, Description, Cost, Price, OnHand, and Supplier, as field. I'm a little confused though, because, one may have 22 of product x as one row, but that doesn't tell you what sizes and colours are amongst the 22 products of that type.

I guess a bad way would be to have a record for each product x of a certain colour and size, but that seems pretty bad.

How does one handle this?

Thanks.

Tomasz

Either attributes such as colour and size should be part of the Product, with different product IDs for each, or you need a separate table e.g. Product_Variant that has this information linked to the Product.

The least of your worries is the number of rows you generate.

Yes, break them all down by size, color and quantity on hand.

Color(ColorID, ColorName)

Size(SizeID, SizeMeasure)

Product(ProductID, ..., OnHand, ColorID, SizeID)

Thanks very much.

Thanks. That is quite clear, but I'm wondering about one thing. If there is a ProductID 12345 that has different size, color variants, then the ProductID is no longer unique (currently I'm using it as primary key). What I mean is: there may be a ProductID 12345 with 10 in medium, red, and ProductID 12345 with 5 in small, black.

I guess, then, I should be using ProductNumber where I'm currently using ProductId, and then have ProductId unique?

Is this right? Excuse my ignorance. I'm learning...

Tomasz.

You would create a unique constraint on product number, size and color and use Product ID as a surrogate key.

certus, perhaps you meant to say use another field as a surrogate key

like, an autonumber

you can't "use ProductID as a surrogate key" because chances are, it has values like 'XHV203' and '0045YY'

you just automatically assumed ProductID was a meaningless number, didn't you

Okay. so I use ProductNumber (autonumber) for key, and then have as many records as there are variants of ProductID 12345?

So, if my Products table schema was:
ProductNumber, ProductID, ProductName, Color, Size, Cost, Price, OnHand, then my records would look like this:

1, 12345, My Widget, Red, S, 99.00 , 125.00, 33
2, 12345, My Widget, Red, M, 99.00 , 125.00, 22
3, 12345, My Widget, Blue, S, 99.00 , 125.00, 10
4, 12345, My Widget, Blue, M, 99.00 , 125.00, 19

Is that the way? By the way, what exactly is a "surrogate key". Is it simply a primary key that is not from the business data, but created to provide a primary key?

Thank you all for helping this newbie.

Tomasz

tomasz, that is right, your design is good, and yes, a surrogate key is "not from business data" -- a key from business data, like ProductID, is called a natural key

it is not necessary that the surrogate key be the primary key, but that is by far the most common application

Thanks Rudy. I will set up this way. Just wondering, though, back a bit in this thread certus said:


What would be the benefit of seperate tables for Color and Size, with
foreign keys to those tables in the Products table? Just wondering.

Thanks again.

Tomasz.

the benefit of those separate color and size tables is that they hold the definitions that the main product table "links to" with colorID and sizeID

this means you don't put the color name and size measures into the product table, and this in turn means that if you wanted to change the name of "fuschia" to "flaming purple" for example, you can do it in one place, and all products which have that color will, when they are printed out, show that new color name

tables like that, consisting of an ID and a description, are very often called "lookup" tables

So, lookup tables are not a necessity but a convenience? Do you think lookup tables are a better idea, rather than putting color name and size name in the product table? What is your opinion? Do it, don't do it? This is an exercise I'm doing for Coldfusion programming class, so I'm trying to model real world 'best practices'. I guess it might depend on the number of products a business has, how many variants, etc.

Thanks again.

Tomasz.

definitely, do it

Thanks, I did. Works like a charm. Many thanks Rudy and all.

Tomasz.