Database Reference
In-Depth Information
Figure 10.47. The revised STAFF table with the new M
ANAGER
ID foreign key
You probably noticed that the “one” side of the relationship line points to the M
ANAGER
ID
field and the “many” side of the line points to the S
TAFF
ID field. This is perfectly accept-
able because a manager will manage one or more staff members, but a given staff member
reports to only one manager. (As you may have intuitively guessed, the “one” side of the
line commonly points to the primary key and the “many” side to the foreign key.)
As you work with self-referencing one-to-one and one-to-many relationships, take a mo-
mentandexamineeachtable'sstructurecarefully.You'lloccasionally findthatyoucan(or
may need to) modify and improve the existing structure in order to
eliminate
the relation-
ship. I know what you're wondering: “But why would I want to do that?”
Retrieving information from tables with these types of relationships can be tedious and
somewhatdifficult.(Adiscussionofthereasonsforthisis,unfortunately,outsidethescope
of this work.) Additionally, the very presence of the relationship can indicate the need for
new field and table structures.
Consider the STAFF table once again. Does it occur to you that if there is a need to track
staff members who are managers, there could be a need to track the departments they man-
age? If this is true, then there must be other facets of the departments that you need to
track in the database. You should now conduct a quick interview with the appropriate staff
