Database design for product options
Hold on Cowboy
This blog post is pretty old. Be careful with the information you find in here. It's likely dead, dying, or wildly inaccurate.
I have tried in vain for many hours trying to figure out how to set up a database so that I can model the following. I think I can set it up with PHP logic to work, but I can’t find a purely SQL way to model this.
You have products Each product can have options such as Color and/or Size. You want the options to be globally configured yet granular for each products.
Product Table
sku (PK) title
Sets Table
SetId (PK) SetName
Elements Table
SetId (FK) ElementID (PK) Element Name
Product Options
sku (fk) elementid (fk) PK = sku,elementid
Different combinations
For example. If a shirt has Small, Medium, Large options as well as Cotton, Wool then the combinations are Small, Wool Medium, Wool Large, Wool Small, Cotton Medium, Cotton Large, Cotton
There is a price, vendor sku, quantity, etc for each of those combinations.
How do you model that in a DB?
Update 2008-09-19: I think if you use the Entity - Attribute - Value (EAV) model for the database structure it might work. Just a hunch.