I am trying to get best design for DB. I need Products table, and would like to have some attributes, yet they are different for each product type. I have few products types. I know that this can be done with single table but I would like to know if
there is a better approach. Thanks.
It is very difficult to say what is the best for you from this small amount of details.
It is not uncommon to do this supertables and subtables. In this approach you have a table Product with the main attribute that applies to all products. Then you have one subtable per product type with the specific attributes. If some product types have
the same attributes you can lump them into the same table. You could also have sub-sub-tables is some types have a couple of attibutes in common but also some specific attributes.
Basically, this design mimicks how you would set up an object hierarchy.
But this model may not work well, if the product types are less well-defined, or if there are very many types. Having fifty subtables is probably not appealing.
Another factor here is what you will use the attributes for. Will to make computations from them? Will they control business rules? Or are they just part of a set of values to present?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
