Table Relationships - Database Design for Mere Mortals - page 295

You can enter only one product number, quantity ordered, and quote price for any given
record; therefore, you'll have to enter a new record into the table for each item a customer
places on his order. Customer number 9001, for example, included eight items on an order
he made on May 16, so there are eight records in the table for this order alone.
Based on what you learned earlier in this chapter, you know that this is an improper way to
establish this relationship. You also know that you can establish the relationship properly
by creating and using a linking table. So you remove the P RODUCT N UMBER field from the
ORDERS table, establish the relationship in the appropriate manner, and revise the rela-
tionship diagram. Figure 10.43 shows the results of your work.
Figure 10.43. Properly establishing the many-to-many relationship between the
ORDERS and PRODUCTS tables
You've eliminated the redundant data in the ORDERS table, but you still have two minor
problems.
1. The Q UOTE P RICE and Q UANTITY O RDERED fields are no longer appropriate for the
ORDERS table; the ORDERS table's primary key does not exclusively identify
their values, and they bear no relationship to any of the remaining fields in the
table. They do, however, relate to a particular P RODUCT N UMBER that's part of a
given order within the ORDER DETAILS table.
2. You have duplicate data because there are two copies of the Q UOTE P RICE field:
one in the ORDERS table and another in the PRODUCTS table.
So you resolve the first problem by removing the Q UOTE P RICE and Q UANTITY O RDERED
fields from the ORDERS table and incorporating them within the ORDER DETAILS
table. You then resolve the second problem by deleting the Q UOTE P RICE field from the
PRODUCTS table; itmakes moresensetoassociate aquoteprice withaproductasit'sbe-