Database Reference
In-Depth Information
Samples of these tables are shown in Figure 6-20. This design clearly forces a sales rep to be related to a
single customer. Because the customer number is a column in the Rep table, there can be only one customer
for each sales rep. Likewise, this design forces a sales rep to be related to a single customer.
Rep
RepNum
LastName
FirstName
CustomerNum
20
Kaiser
Valerie
148
35
Hull
Richard
282
209
65
Perez
Juan
356
Customer
CustomerNum
CustomerName
RepNum
148
Al's Appliance and Sport
20
282
Brookings Direct
35
356
Ferguson's
65
FIGURE 6-20
One-to-one relationship implemented by including the primary key of each table as a foreign key in the other
Q&A
Question: What is the potential problem with this solution?
Answer: There is no guarantee that the information will match. Consider Figure 6-21, for example. The data
in the first table indicates that sales rep 20 represents customer 148. The data in the second table, on the
other hand, indicates that customer 148 is represented by sales rep 35! This solution may be the simplest
way of implementing a one-to-one relationship from a conceptual standpoint, but it clearly introduces the
risk of update anomalies and inconsistency in the database. The programs themselves would have to ensure
that the data in the two tables match, a task that the design should be able to accomplish on its own.
Rep
RepNum
LastName
FirstName
CustomerNum
20
Kaiser
Valerie
148
35
Hull
Richard
282
65
Perez
Juan
356
Customer
CustomerNum
CustomerName
RepNum
148
Al's Appliance and Sport
35
282
Brookings Direct
20
356
Ferguson's
65
FIGURE 6-21
Implementation of a one-to-one relationship in which information does not match
To avoid these types of problems when creating one-to-one relationships, the first solution is to create a
single table such as this:
Customer (CustomerNum, CustomerName, RepNum, LastName, FirstName)
A sample of this table is shown in Figure 6-22. Which column should be the primary key? If it is the
customer number, there is nothing to prevent all three rows from containing the same rep number. On the
other hand, if it is the rep number, the same would hold true for the customer number.
