PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 76

cordless phones. We separate objects into a subtype cluster because even
though a phone is a phone, different types will require that we track dif-
ferent attributes. For example, on a cordless phone, you need to know the
working range of the handset and the frequency on which it operates, and
with a corded phone, you could track something like cord length. These
differences are tracked in the subtypes, and all the common attributes of
phones are held in the supertype.
How do you go about physically implementing a subtype cluster in
SQL Server? You have three options. The first is to create a single table
that represents the attributes of the supertype and also contains the attri-
butes of all the subtypes. Your second option is to create tables for each of
the subtypes, adding the supertype attributes to each of these subtype ta-
bles. Third, you can create the supertype table and the subtype tables, ef-
fectively implementing the subtype cluster in the same way it was logically
modeled.
To determine which method is correct, you must look closely at the
data being stored. We will walk through each of these options and look at
the reasons you would use them, along with the pros and cons of each.
Supertype Table
You would choose this option when the subtypes contain few or no differ-
ences from the data stored in the supertype. For example, let's look at a
cluster that stores employee data. While building a model, you discover
that the company has salaried as well as hourly employees, and you decide
to model this difference using subtypes and supertypes. After hashing out
all the requirements, you determine that the only real difference between
these types is that you store the annual salary for the salaried employees
and you need to store the hourly rate and the number of hours for an
hourly employee.
In this example, the subtypes contain very subtle differences, so you
could build this subtype cluster by using only the supertype table. For this
situation, you would likely create a single employee table that contains all
the attributes for employees, including all three of the subtype attributes
for salary, hourly rate, and hours. Whenever you insert an hourly em-
ployee, you would require that data be in the hourly rate and hour columns
and that the salary column be left NULL. For salaried employees, you
would do the exact opposite.