Exclusive Identification

One principle of database design that I'd like to re-emphasize is that a primary key should exclusively identify the value of each field within a given record. Each field value for any particular record should be unique in the database, except where it's used as matching data for establishing a relationship. If the primary key doesn't exclusively identify the field, either it's unnecessary or it belongs in another table.

This is a difficult concept to comprehend. Let's look at the final list of tables and fields in Table 3.26 to see how it applies. Consider the table Programs, where ProgramID is the primary key. Each of the nonkey fieldsName, Location, Synopsis, Notesis uniquely identified by the primary key. GenreID is not unique, but because it is the primary key of the Genres table, you need the field to establish the relationship between the two tables.

Let's take an example to see when the primary key would not exclusively identify a field. Consider an actor's biography. Because a given actor could appear on more than one show, simply knowing the ProgramID would not uniquely identify the value in the Biography field. If an actor appeared on three different programs, there would be three separate records for the actor's biography.

Note that "exclusivity" and "uniqueness" do not imply that the same value cannot appear many times in a table or in other tables in the database. For example, consider the field Gender in the Actors table. Obviously, there are only two possible values, male and female. So the value itself will be repeated many times throughout the table. And it's entirely possible to have a field that identifies gender in more than one table (for example, in an order-entry database, a Gender field in a Customers table and a Gender field in a Suppliers table for a supplier's representative).

But the ActorID for a record that describes Jennifer Lopez will uniquely identify her as a woman, and the ActorID for Arnold Schwarzenegger will uniquely identify him as man. There should be no other record in the database in which the gender of actors is identified as such.