Have each product table keyed by its own auto-incrementing ID.

Then, have a main ID table, and all it will have is three fields: It's own key, and the product table key, and a number that points to which product table the product is in.

Whenever you want to add a new product, first you create a new record in the product table, generating a unique key for the record. Then, you create a new record in the main ID table, inserting the key from the product table as well as the product table number.

As for each product having different attributes - these can (and should be) stored in separate "product attribute" tables.

Try to normalize the tables, one approach:

table "productgroups":
id ¦ productgroupname ¦ moreinfo...

table "productattributenames":
id ¦ groupid ¦ name

table "productattributevalues":
id ¦ nameid ¦ value

table "products":
id ¦ productgroupid ¦ productname

table "productattributes":
id ¦ productid ¦ productattributnameid ¦ value

That is:
*One table for groups, one row per group (rings, watches etc)
*One table for attributes to products, any amount of rows per product
*One table for attributevalues, any amount of rows per attribut, even zero if you want to use free-text input etc for this attributename (table useful for creating drop-downs, checkboxes etc so you dont hardcode these values into a web form)
*One table for products, one row per product - all mandatory common values in columns here
*One table for "extra" product attributes, any number of rows referencing back to "products", "productattributenames" and "productattributevalues"

A bit more to grasp but a lot more scalable and flexible :)
Now you can use joins to build "new" tables and your scripting language to build nice interfaces, reports etc.

Also read this nice article:
[dev.mysql.com...]

Ok one more question, regarding the productatributenames table for example. A bracelet, and a necklace are in two different groups, however they both have lengths. Since the productattributenames table has a group id for each group of products, does that mean the length of a bracelet, and the length of a necklace are refereing to 2 different attributes?

Thanks,
Ryan

table "productattributevalues":
id ¦ nameid ¦ value
table "productattributes":
id ¦ productid ¦ productattributnameid ¦ value

I have seen a lot of commercial systems use this approach and reports/queries can be a big problem. Its a quick fix when youre in a rush to design some flexibility in but has massive problems later on. Its also less normalised than the original model posted.

Attribues can be common to many different products for example color, size, why not just have attrivutes tables that have a value of N/A for some products.

Sometimes a generic approach with a few null values or constraints is better than unnecessary flexibility.

[edited by: aspdaddy at 11:59 pm (utc) on Oct. 4, 2006]

for the ExtraAttribute table.

ID = int
attributeID = int
productID = int
value =? (what if your attributes are all different datatypes?)

for the ExtraAttribute table.

ID = int
attributeID = int
productID = int
value =? (what if your attributes are all different datatypes?)

any ideas?

so a basic table with a whole bunch of attributes (most end up being null) are better than linked tables?

No 1 product table and one table per attribute would be my prefered one. You could have attribute values called n/a if you dont want to alow null values.

Depends on you criteria for better though :) Do you want it to actually work well for inserts, updates, deletes, reporting or just *seem* a correct model.

A good design approach is to spec out your queries and reports first and see which model allows the simpler SQL

No 1 product table and one table per attribute would be my prefered one. You could have attribute values called n/a if you dont want to alow null values.

Depends on you criteria for better though :) Do you want it to actually work well for inserts, updates, deletes, reporting or just *seem* a correct model.

A good design approach is to spec out your queries and reports first and see which model allows the simpler SQL

But i was wondering your structure then would be like this?

ProductTable
pid ¦ name ¦ .. other common fields

AttributeTable
aid ¦ a_name ¦ a_value

If this is correct, how do you overcome different a_value's having different datatypes?

thanks a bunch!

Ryan

You got the right answer above from falsedawn imo.

tblProduct(ProductID,SizeID,ColorID,MaterialID)
tblSize(ID,Name)
tblColor(ID,Name)
tblMaterial(ID,Name)

Wont this mean that every time i try to get the details on a product I will have to do multiple queries? 1 Query grabbing the product row, then a query for each table that has value thats not null?

example.
Necklace Length: 17inches
Bracelet Length: 17inches <-- dosn't make sense?

maybe there is no way to do this perfectly?

Just choose one design, build it, write your queries then if it sucks try another design.

You have at least 4 possible designs from this post alone, all will work,all have advantages and disadvantages , no one here will know for cvertain which is the best for your requirements.

HTH :)