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.
sku (PK) title
SetId (PK) SetName
SetId (FK) ElementID (PK) Element Name
sku (fk) elementid (fk) PK = sku,elementid
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.
Did this help you out? It took me a few days to piece together all this information together, I hope this saves you some time (who knows, maybe the future me will be thankful I wrote this down). Let me know your thoughts. email@example.com