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.

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.