DATABASE DESIGN 2: DESIGN METHOD - Concepts of Database Management - page 210

Customer

CustomerNum CustomerName RepNum LastName FirstName

148 Al's Appliance and Sport 20 Kaiser Valerie

282 Brookings Direct 35 Hull Richard

356 Ferguson's 65 Perez Juan

FIGURE 6-22 One-to-one relationship implemented in a single table

210

The solution is to choose either the customer number or the rep number as the primary key and make
the other column the alternate key. In other words, the DBMS should enforce the uniqueness of both cus-
tomer numbers and rep numbers. Because each customer and each sales rep will appear in exactly one row,
there is a one-to-one relationship between them.
Although this solution is workable, it has two features that are not particularly attractive. First, it com-
bines columns of two different entities into a single table. It certainly would seem more natural to have one
table with customer columns and a second table with sales rep columns. Second, if it is possible for one
entity to exist without the other (for example, when a customer has no sales rep), this structure is going to
cause problems.
A better solution is to create separate tables for customers and sales reps and to include the primary key
of one of them as a foreign key in the other. This foreign key would also be designated as an alternate key.
Thus, you could choose either

Rep (RepNum, LastName, FirstName, CustomerNum)
Customer (CustomerNum, CustomerName)

or

Rep (RepNum, LastName, FirstName)
Customer (CustomerNum, CustomerName, RepNum)

Samples of these two possibilities are shown in Figure 6-23. In either case, you must enforce the unique-
ness of the foreign key that you added. In the first solution, for example, if customer numbers need not be
unique, all three rows might contain customer 148, violating the one-to-one relationship. You can enforce the
uniqueness by designating these foreign keys as alternate keys. They will also be foreign keys because they
must match an actual row in the other table.

Solution 1:
Rep

Customer

RepNum LastName FirstName CustomerNum CustomerNum CustomerName

148 Al's Appliance and Sport

20 Kaiser Valerie 148

282 Brookings Direct

35 Hull Richard 282

356 Ferguson's

65 Perez Juan 356

Solution 2:
Rep Customer

RepNum LastName FirstName CustomerNum CustomerName RepNum

20 Kaiser Valerie 148 Al's Appliance and Sport 20

35 Hull Richard 282 Brookings Direct 35

65 Perez Juan 356 Ferguson's 65

FIGURE 6-23 One-to-one relationship implemented by including the primary key of one table as a foreign key

(and alternate key) in the other table

How do you make a choice between the possibilities? In some cases, it really makes no difference which
arrangement you choose. Suppose, however, you anticipate the possibility that this relationship may not