Database Reference
In-Depth Information
Zacharythenstudiesthetablematrixcloselyandusestheappropriateformulatodetermine
the true relationship between each pair of tables. Here is what he's discovered so far.
CUSTOMERS and INVOICES bear a one-to-many relationship. (1:1 + 1:N = 1:N)
EMPLOYEES and INVOICES bear a one-to-many relationship. (1:1 + 1:N = 1:N)
PRODUCTSandINVOICESbearamany-to-manyrelationship.(1:N+1:N=M:N)
Now he diagrams the relationships, places them in a folder, and heads to Starbucks for his
meeting with Mike.
At the meeting, Mike and Zachary work on verifying the relationships. They both determ-
ine that the three relationships are indeed correct, and then Zachary brings Mike's atten-
tion to the PRODUCTS and VENDORS tables. He's not quite sure about the relationship
between them, so he discusses the matter with Mike.
Z
ACHARY
: “I wanted to ask you about the relationship between the PRODUCTS
and VENDORS tables. Can a single product be associated with one or
more vendors?”
M
IKE
: “Yes, in a manner of speaking. What I mean is that a single type of
product—such as a bike lock—
can
be associated with one or more
vendors. But I give each lock its own product number and treat it as a
distinct item, regardless of the vendor who supplies it. Now, if the true
meaning of your question is whether a single
record
in the PRODUCTS
table can be associated with one or more records in the VENDORS
table, then the answer is no because each record in the PRODUCTS
table contains a reference to
only one
vendor in the VENDORS table.”
Z
ACHARY
: “I thought as much. In that case, there's a one-to-many relationship
between the VENDORS and PRODUCTS tables. I automatically
figured that a single vendor could be associated with many products in
the PRODUCTS table.”
Zachary now diagrams the one-to-many relationship between the VENDORS and
PRODUCTS tables and continues with the next step.
He establishes each one-to-many relationship by taking a copy of the primary key from the
parent table and incorporating it within the structure of the child table (where it serves as
a foreign key) and then revises the relationship diagram accordingly.
Figure 10.66
shows
one of his revised diagrams.
