SQL Server doubts in design

Question

I have a table components that has the general information for the pieces that can be used in a machine (reference, manufacture...). But I can have a very different types of pieces, for example, valves, engines... And so on. So each type of pieces has different type of specifications data.

For example, valve has information about its diameter, but the engine has information about his power. It has no sense to have information about the power of a valve.

I am thinking in two options. First, to have a unique table with all the data for the specifications, so I have many fields ValveDiamter, ValveMaterial.... EnginePower, EngineType, etc. In this case, the problem is that for a piece, I would have all the fields with null value less the few fields with the information of the type of pice.

Other solution is to have one table of specifications for each type of piece. So I would have a table ValveSpecificaionts, EngineSpecifications, etc. This makes me to have a lot tables, but only with the information that I need according to the type of piece.

I wonder what will be the best option. Any other alternative?

Answer 1

To make a fully informed decision we would need more information (mostly about how it will be used, and how the pieces interrelate, if at all), but as a general rule, I would recommend using more tables with only the columns that apply to each piece.

This has the advantage of being more tailored to the piece which is easier to work with and more effecient for many (but not all!) typical usage patterns. This is also likely the pattern you would see if you deliberately normalize your tables, which is often worth doing.

Along with that, it is likely to be help prevent certain types of inconsistencies (such as ensuring that a piece does not receive an attribute that doesn't apply to it).

Answer 2

It sounds like you're deciding between 1 table with many fields (where most fields will be unused for any one record), and many tables with a few fields (where most fields will be almost always used). Here's a few questions which may help:

1. Will the "Valve" data be used by different queries than the "Engine" data or will they all be used at the same time? If you use the many tables approach, then you'll have to constantly string those tables together in union queries which could be a hassle.
2. How much data will be stored and how often will it be accessed? For speed purposes, you may be better off with a smaller number of tables.

Answer 3

There are a few different ways to deal with something like this:

1. As you have said is to have a single large table and null unused attributes. This works well if there are a set of common attributes and a relatively small number of outliers.
2. Create a separate attributes table for each item type.
3. Create a single attributes table and store attribute names and values with the item id as a foreign key.
4. Create an xml column and store the attribute set as xml.

The best one depends on the total number of attributes, the number of attributes in common, and whether or not the attributes need to be searchable.