Database Reference
In-Depth Information
Note
Many database designers will use M
ANAGER
ID as the primary key name in the
MANAGERS table and the foreign key name in the DEPARTMENTS table. I
choose to use E
MPLOYEE
ID instead for these reasons.
• MANAGERS is a subset of the EMPLOYEES table, so it shares the same
primary key (E
MPLOYEE
ID).
• It keeps the field in conformance with the Elements of the Ideal Field.
(It re-
tains a majority of its characteristics when it appears in more than one table.)
• It keeps the field in conformance with the
Elements of a Foreign Key
.
(You'll
learn about foreign keys later in this chapter.)
• It removes any possible ambiguity or doubt about the true nature of a foreign
key. (I'll explain this in more detail during the discussion of the Elements of a
Foreign Key.)
There is no absolute right or wrong way to do this—in the end, the approach you
use is simply a matter of style. Once you decide which approach you want to use,
however, make certain you use it consistently.
Thereisasmallchangeinthewayyou'lldiagramtherelationshipsfromthispointforward.
You should now use the primary key as the beginning point and the foreign key as the end
point of the relationship line. (The only exception will be when you're diagramming the
relationship between a subset table and its parent data table.) Making this minor modifica-
tion will help you visualize the relationships more clearly and make it easier to identify the
fields that establish the relationship.
The One-to-Many Relationship
Thetechniqueyouusetoestablishaone-to-manyrelationshipissimilartotheoneyouused
to establish a one-to-one relationship. You simply take a copy of the primary key from the
table on the “one” side of the relationship and incorporate it within the table structure on
the “many” side, where it then becomes a foreign key. For example, consider the one-to-
many relationship between the BUILDINGS and ROOMS tables shown in
Figure 10.36
.
