Database Reference
In-Depth Information
child table; stated another way, a record in the child table
must
have a related record in the
parent table. The roles you assign to the tables usually depend on the subjects they rep-
resent, although there will be instances when you can assign the roles rather arbitrarily. In
Figure10.32
,
forexample,youwouldmostlikelyassigntheparentroletotheSTAFFtable
and the child role to the COMPENSATION table. This is a reasonable assumption because
it would be completely illogical to have a record in the COMPENSATION table that is not
related to a record in the STAFF table.
Figure 10.32. Which table would you pick as the parent table?
Inthecasewhereoneofthetablesisa
subset
table,youwill
usually
assignthechildroleto
the subset table. There are instances, however, when you can assign the parent role to the
subset table.
You establish a one-to-one relationship by taking a copy of the parent table's primary key
and incorporating it within the structure of the child table, where it then becomes a
foreign
key.
(Theterm
foreign key
isderivedfromthefactthatthechildtablealreadyhasaprimary
key of its own, and the primary key you are introducing from the parent table is “foreign”
to the child table.) In most one-to-one relationships, however, the foreign key
also
serves
as the child table's
primary
key.
Figure 10.33
illustrates how you would establish the relationship between the STAFF and
FACULTYtables.STAFFistheparenttableinthiscasebecausearecordintheFACULTY
table
must
be related to a record in the STAFF table; faculty members are drawn from the
school's staff. If you were to follow the procedure you just learned, you would take a copy
of the STAFF table's primary key and incorporate it as a foreign key in the FACULTY
table. This is unnecessary, however, because FACULTY is already a properly defined sub-
