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

data types as long as they are large enough to be unique given the table's
potential row count. Also, you can create a composite PK (a PK that uses
more than one column), but we do not recommend using composite PKs
if you can avoid it. The reason? If you have four columns in your PK, then
each table that references this table will require the same four columns.
Not only does it take longer to build a join on four columns, but also you
have a lot of duplicate data storage that would otherwise be avoided.
To recap, here are the rules you should follow when choosing a PK
from your candidate keys.

Avoid using string columns.

■

Use integer data when possible.

■

Avoid composite primary keys.

■

Given these rules, let's look at a table and decide which columns to use
as our PK. Figure 3.1 shows a table called Products. This table has a cou-
ple of candidate keys, the first being the model number. However, model
numbers are unique only to a specific manufacturer. So the best option
here would be a composite key containing both Model Number and
Manufacturer. The other candidate key in this table is the SKU. An
SKU (stock-keeping unit) number is usually an internal number that can
uniquely identify any product a company buys and sells regardless of
manufacturer.

F IGURE 3.1

A Products table in need of a primary key