Shane A. Stillwell
Database design for product options

Database design for product options

Hold on Cowboy

This blog post is pretty old. Be careful with the information you find in here. The Times They Are A-Changin'

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.