My current system houses different variants of Products in different tables;

  • Productgroups
  • Productcolors
  • Productsizes
  • Productmodels

Products can reside under 1 Productgroup and can have multi-variance Colors, Sizes and Models.

The logic on filling the database now fully relies on the website-scripts; meaning when I make a new Product in my back-office I am filling and relating the Colors, Sizes and Models to the product and make sure they have a combination of themselves.

This structure quickly grows; like 3 Color-variants of 6 Sizes and 2 Models already convert into 36 different products.

I wonder if there is a more logical way to this Database-wise. The problem is that in the future it might be possible that a product will get another property (let's say Sublabel). How could I make this method ever-extendable?

I read about EAV, but that's really too much info to go on for now. Before I dive in the deep, I wonder if there are other approaches on this matter.

  • 1
    Please provide the full design you have now (columns, primary keys, foreign keys, other constraints, whatever you have) and not only table names. – ypercubeᵀᴹ May 12 '13 at 19:14
  • What DBMS are you using? – StanleyJohns May 13 '13 at 6:26

If you don't want to go for a full EAV solution, you could try something like this:

  (other fields)


  product_id (fk to product_base.id)
  product_option_type_id (fk to product_option_types.id)

This will let you have data that looks like this:

ID  |  name | product_group_id
1   |  Foo  | 1

ID  |  description
1   |  colour
2   |  size
3   |  model
4   |  sublabel

ID  | product_id | product_option_type_id  | description
1   |  1         | 1                       | red
2   |  1         | 1                       | green
3   |  1         | 2                       | small
4   |  1         | 2                       | large
5   |  1         | 3                       | special edition
6   |  1         | 3                       | standard edition
7   |  1         | 4                       | Bar

So you end up with a "Foo" product that has 2 colours (red and green), two sizes (small and large), two models (standard and special) and a sublabel (Bar).

Note that in product_options, you'll need a unique constraint on (product_id, product_option_type_id, description).

you could try to go further and attempt to normalize the values of product_options.description to a separate table, but that might be more complicated than it's worth.

  • 1
    I can see why you choose this design, but it's also important to point out the disadvantages. if you want to check if the product line has a Blue , Large , Special Edition T-Shirt you'll have to access the product_options table 4 times (once for each product_option) or do a nasty pivot. this is a classic example of the trade of between flexibility and speed. personally, i think that you'll query the table much more often then you'll add attributes. – haki May 13 '13 at 17:46
  • @haki: Yes, that's a fair point. I suppose views could be created to filter on specific product_option_type_ids, but that wouldn't resolve any possible performance problems, it would just make queries a little more readable. – FrustratedWithFormsDesigner May 13 '13 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.