Hi,

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?

I designed a system back in 2003, which is now used by Google, Mozilla and many others, where we create a main "Master" product. It can be only a "holder" for sub products, or can be a product in its own right if required.

If master is a product in its own right, then is simpler not to have a separate table for it ( I never have, even when its just a holder). Sub products can be assigned to the master using a "master_id". This record best in its own table, so a sub product can have many masters (or be a single product in its own right).

There is nothing inherently slow with a join, nor an update based on master_id.