Database Reference
In-Depth Information
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.
—
211
MANY-TO-MANY RELATIONSHIP CONSIDERATIONS
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
identify 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 customer number is 282 and the part number is DR93. Thus, the primary key for the Sales table must be
the combination 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 legiti-
mate. 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)
