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

always be one-to-one. Suppose there is a likelihood in the future that a sales rep might represent more than
one customer but that each customer still will be assigned to exactly one sales rep.
The relationship would then be one-to-many, and it would be implemented with a structure similar to
Solution 2. In fact, the structure would differ only in that the rep number in the Customer table would not be
an alternate key. Thus, to convert from the second alternative to the appropriate structure would be a simple
matter—you would remove the restriction that the rep number in the Customer table is an alternate key.
This situation would lead you to favor the second alternative.

MANY-TO-MANY RELATIONSHIP CONSIDERATIONS

206

Complex issues arise when more than two entities are related in a many-to-many relationship. For example,
suppose Premiere Products needs to know which sales reps sold which parts to which customers. In this
example, there are no restrictions on which customers a given sales rep may sell to or on the parts that a sales
rep may sell. Sample data for this relationship is shown in Figure 6-24.

Sales

RepNum CustomerNum PartNum

20 148 AT94

20 282 DR93

35 148 DR93

35 148 DW11

65 282 AT94

65 282 DR93

65 356 AT94

FIGURE 6-24 Sample Sales data

The first row in the table indicates that sales rep 20 sold part AT94 to customer 148. (The number of units
sold to the customer is not important in this example.) The second row indicates that sales rep 20 sold part
DR93 to customer 282.

Q&A

Question: What is the primary key of the Sales table?
Answer: Clearly, none of the three columns (RepNum, CustomerNum, and PartNum) alone will uniquely iden-
tify a record. The combination of RepNum and CustomerNum does not work because there are two rows on
which the rep number is 35 and the customer number is 148. The combination of RepNum and PartNum does
not work because there are two rows on which the rep number is 65 and the part number is AT94. Finally, the
combination of CustomerNum and PartNum does not work because there are two rows on which the cus-
tomer number is 282 and the part number is DR93. Thus, the primary key for the Sales table must be the com-
bination of all three columns, as follows:
Sales ( RepNum , CustomerNum , PartNum )

Attempting to model this particular situation as two (or three) many-to-many relationships is not
legitimate. Consider the following code and the data shown in Figure 6-25, for example, in which the same data
is split into three tables.

RepCustomer (RepNum, CustomerNum)
CustomerPart ( CustomerNum , PartNum )
PartRep (PartNum, RepNum)