Data model for products with attributes, with different prices for each attributes combination

Question

I am developing a generic e-commerce solution using a relational database (MySQL), and I want to manage products like this:

• Each product (e.g. wood, plastic, steel) will have several attributes (e.g. thickness, width, height, color).
• In turn, each attribute can have several values (e.g. 3 mm or 5 mm for thickness, blue or red for color, etc.), which have a different price in each case.

So far so good, my current approach counts on 4 tables:

• products (id, name, desc)
• attributes (id, name, desc, type)
• attribute_values (id, attribute_id, value)
• attribute_product (attribute_id, product_id, price)

With this data model, I can combine products and attributes well, for example:

• Define a product called wood which have 2 attributes (thickness and height) which in turn have 3 mm and 5 mm options for thickness and blue and red options for color...

The problem comes with price calculation because if I declare price in attribute_product pivot table, I still do not know which value is selected for that specific attribute and product.

How could I modify the data model to be able to somehow 'link' products with attribute values? Thanks in advance.

Answer 1

Right, a linking table like attribute_product only link 1 instance to 1 instance, and your products can have multiple attributes, so that model doesn't work.

What you are calling the attribute_product is what most people would say is the product. It is the thing that has a price. So I would just call that "products". What you are calling products are perhaps product_categories.

So the attribute tables would be child tables to it.

• product_categories (id, desc) -- eg. "Wood"
• products (id, name, desc, category_id, price) -- eg. "Wood: 3x5mm Red"
• attributes (id, name, desc, type) -- eg. "thickness"
• product_attributes (id, product_id, attribute_id, value) -- multiple rows per product; eg. "3mm" "red"