Keys - Database Design for Mere Mortals - page 216

b. If the answer is no, remove the field from the table, move to the next field, and
repeat the question. (Be sure to determine whether you can add the field you
just removed to another table structure, if appropriate, or discard it completely
because it is truly unnecessary.)
4. Continue this procedure until you've examined every field value in the record.
You know that you'll have to remove any field containing a value that E MPLOYEE N UMBER
does not exclusively identify. E MPLOYEE N UMBER does exclusively identify the value of
each field in the test record, however, so you use it as the official primary key for the
EMPLOYEES table and mark its name with the letters “PK” in the table structure. You
then repeat this process with the rest of the tables in Mike's new database until every table
has a primary key.
Remember to keep these rules in mind as you establish primary keys for each table.
• Each table must have one—and only one—primary key.
• Each primary key within the database should be unique—no two tables should
have the same primary key (unless one of them is a subset table).
As you work through the tables in Mike's database, you remember that the SERVICES
table is a subset table. You created it during the previous stage of the design process (in
Chapter 7 ) , and it represents a more specific version of the subject represented by the
PRODUCTS table. The P RODUCT N AME field is what currently relates the PRODUCTS
tabletotheSERVICESsubsettable.Younowknow,however,thatasubsettable must have
thesameprimarykeyasthetabletowhichitisrelated,soyou'lluseP RODUCT N UMBER (the
primary key of the PRODUCTS table) as the primary key of the SERVICES table. Figure
8.13 shows the PRODUCTS and SERVICES tables with their primary keys.