Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

1 Answer 1

up vote 1 down vote accepted

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

product_base
------------
  id
  product_group_id
  (other fields)


product_option_types
--------------------
  id
  description

product_options
---------------
  id
  product_id (fk to product_base.id)
  product_option_type_id (fk to product_option_types.id)
  description

This will let you have data that looks like this:

product_base
ID  |  name | product_group_id
------------|-----------------
1   |  Foo  | 1

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

product_options
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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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