Multi-variance products 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.
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.