A hefty part of designing a relational database is dividing the data
elements into related tables. Once you're ready to start working with
the data, you rely on relationships between the tables to pull the data
together in meaningful ways. For instance, order information is useless
unless you know which customer placed a particular order. By now, you
probably realize that you don't store customer and order information in
the same table. Instead, you store order and customer data in two
related tables and then use a relationship between the two tables to
view each order and its corresponding customer information at the same
time. If normalized tables are a relational database's foundation, then
relationships are the cornerstone.
You share many
relationships with members of your family. For instance, you and your
mother are related. You have only one mother, but she may have several
children. You and your siblings are related—you may have many brothers
and sisters and, of course, they'll have many brothers and sisters as
well. If you're married, both you and your spouse have a spouse—each
other—but only one at a time. Database relationships are very similar in
that they're associations between tables. There are three types of
- One-to-one: Both tables can have only
one record on either side of the relationship. Each primary key value
relates to only one (or no) record in the related table. They're like
spouses—you may or may not be married, but if you are, both you and your
spouse have only one spouse. Most one-to-one relationships are forced
by business rules and don't flow naturally from the data. In the absence
of such a rule, you can usually combine both tables into one table
without breaking any normalization rules.
The primary key table contains only one record that relates to none,
one, or many records in the related table. This relationship is similar
to the one between you and a parent. You have only one mother, but your
mother may have several children.
- Many-to-many: Each
record in both tables can relate to any number of records (or no
records) in the other table. For instance, if you have several siblings,
so do your siblings (have many siblings). Many-to-many relationships
require a third table, known as an associate or linking table, because
relational systems can't directly accommodate the relationship.
the time you get around to establishing relationships between the
related tables, you'll probably be very familiar with the data.
Consequently, associations are more obvious at this point than when you
started. Your database system relies on matching values found in both
tables to form relationships. When a match is found, the system pulls
the data from both tables to create a virtual record. For instance, you
might want to see all the books written by a particular author. In this
case, the system would match values between the Books and the Authors
tables. It's important to remember that most of the time, the resulting
record is dynamic, which means any change made to the virtual record
will usually work its way back to the underlying table.
matching values are the primary and foreign key values. (The relational
model doesn't require that a relationship be based on a primary key. You
can use any candidate key in the table, but using the primary key is
the accepted standard.) You learned about primary keys in Part 2—a
primary key uniquely identifies each record in a table. A foreign key
is, simply put, one table's primary key in another table. As such,
there's not much for you to do—simply add the primary key field to the
related table, as a foreign key.
The only consideration is that a
foreign key field must be of the same data type as the primary key.
Some systems allow one exception to this rule and will allow a
relationship between a number and an autonumbering field (such as
AutoNumber in Access of Identity in SQL Server). In addition, foreign
key values can be Null, although the recommendation is that you not
leave a foreign key Null without a very specific reason to do so.
Chances are you may never work with a database that requires this