1

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.

andcl
  • 3,342
  • 7
  • 33
  • 61

1 Answers1

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"
Jon Wilson
  • 726
  • 1
  • 8
  • 23
  • Thank you for the response. Your schema looks similar to the one proposed by @DenisdeBernardy here: https://stackoverflow.com/questions/19144200/designing-a-sql-schema-for-a-combination-of-many-to-many-relationship-variation... but both of you place the `price` field right in `products` table (or SKUs table). In my case, the **price must be dynamically generated from the attributes** because I have options that are filled by customers themselves (e.g., an open number field with width or height...). In this case, I can not create a line for each possible width value... Any ideas? – andcl Jan 02 '19 at 18:27
  • In other words, what if `attributes` are not limited to only several options and can be user inputted to fully customise their products? – andcl Jan 02 '19 at 18:44
  • So would there be a price for Wood, plus an additional amount for 3mm, etc? – Jon Wilson Jan 02 '19 at 19:44
  • Yes, that is the idea. The customer will customise the product with, e.g., `thickness` (choosing from several available values from a dropdown), but also will specify the `width` manually (through a number input) and so on... And so the price should be dynamically generated from all attributes. Thanks in advance Jon. – andcl Jan 02 '19 at 19:49
  • 1
    It sounds like each product and attribute_value has it's own price, which you want to sum to get the total charge? If so you could add a price field to both your products and attribute_values tables. The linking and summing would happen in the invoice. You might need a detail table in the invoice for the attribute values for the same selected product. – Jon Wilson Jan 05 '19 at 17:53
  • Thanks, all of your answers conform a very good starting point :) – andcl Jan 05 '19 at 23:51