Design of Product within SubProducts (MySQL)


I have a Product, which consist of SubProducts of different types.

For example,
Product = SubProductA&SubProductB, or
Product = SubProductA&SubProductA&SubProductB, etc.

Each SubProduct has different structure, one SubProduct is dominant (there are much more records of that product, then others), but still it is possible to build some kind of ‘universal’ structure. But I don’t like this idea:

  • mess with the names,
  • ‘extra empty’ rows,
  • bad understanding,
  • possible problems with adding new SubProducts, etc.

I like the idea to separate each SubProduct to different tables, make SuperSubProduct table with ProductId and type of SubProduct. And table for each SubProduct with SuperSubProductId in it. So Product consists many SuperSubProduct, each is connected with SubProduct.

The problem I see is with operations on DB in that case: for SELECT on current Product I need to join it with SubProducts tables somehow, but in ‘universal’ case I don’t need to do joints. Also, if one SubProduct changed, I need to update ‘update time’ of Product, in ‘universal’ case I can take a MAX on one table.

So it is seems ‘slower’ for me to separate. Even I like this idea. And speed is important, on other hand.

What are your design suggestions for my case?

could you please give some actual examples