Database Reference
In-Depth Information
COMPANY
PART
CompanyName
PartNumber
City
Country
Volume
PartName
SalesPrice
ReOrderQuantity
QuantityOnHand
FK???
(a) The Foreign Key Has No Place in Either Table
COMPANY
PART
CompanyName
PartNumber
City
Country
Volume
PartName
SalesPrice
ReOrderQuantity
QuantityOnHand
COMPANY_PART_INT
CompanyName (FK)
PartNumber (FK)
Figure 6-10
Transformation of N:M
Relationship Between Strong
Entities
(b) Foreign Keys Placed in ID-Dependent Intersection Table
With this design, a given PartNumber may appear in many rows of COMPANY so that many
companies can supply the part. But how do we show that a company can supply many parts?
There is only space to show one part. We do not want to duplicate the entire row for a com-
pany just to show a second part; such a strategy would result in unacceptable data duplica-
tion and data integrity problems. Therefore, this is not an acceptable solution, and a similar
problem will occur if we try to place the primary key of COMPANY, CompanyName, into
PART as a foreign key.
The solution is to create a third table, called an intersection table . 1 Such a table
shows the correspondences of a given company and a given part. It holds only the primary
keys of the two tables as foreign keys, and this combination of keys serves as the compos-
ite primary key of the intersection table itself. The intersection holds only the key data;
it contains no other user data. For the example in Figure 6-10(a) we create the following
intersection table:
COMPANY_PART_INT ( CompanyName , PartNumber )
The COMPANY_PART_INT table has one row for each company-part combination.
Notice that both columns are part of the primary key and that each column is a foreign key to
a different table. Because both columns are keys of other tables, intersection tables are always
ID-dependent on both of their parent tables and the relationships with the parent tables are
identifying relationships.
Thus, the database design in Figure 6-10(a) is drawn with an ID-dependent COMPANY_
PART_INT intersection table and identifying relationship lines. Like all ID-dependent tables,
1 While we use the term intersection table in this topic, this table structure is known by many other names. In
fact, Wikipedia lists eleven alternate names, including intersection table, junction table, bridge table , and associa-
tion table . While we reserve the term association table for an association relationship (as explained later in this
chapter), your instructor may prefer one of the other terms for this table structure. For more information, see
the Wikipedia article Junction table at http://en.wikipedia.org/wiki/Junction_table .
 
 
Search WWH ::




Custom Search