Microsoft Office Tutorials and References
In Depth Information
What Are Relationships?
The Products table and the Vendors table are related because the record for
each product includes the name or ID code of a vendor, and the record for
each vendor, of course, includes the name or ID code of the vendor.
Multiple products may come from one vendor. Figure 3-2 shows how such a
one-to-many relationship (more about that in a minute) works in a database
for a store that sells movies.
Well, sure, you could store product information and vendor information
together, in one big table, but you’d soon be sorry. You may want to add
fields to the Products table to contain the address of the vendor from which
you bought the product. But here’s the problem: Whenever a vendor’s
address changes, you have to make that change in the record for every item
you buy from that vendor. What a pain!
A key principle of database design is this: Store each piece of information
once. If you store information more than once, you have to update it more
than once. (In real life — trust us on this one — if you update it in some
places but not in others, you end up with a mess.)
Here’s a piece of geekspeak: Normalizing a database means figuring out the
most efficient way to divide the information into tables so that each piece of
information is stored only once and related information is connected. This
chapter steps you through the process.
How relationships work
Sorry, we have no advice for the lovelorn here. Luckily, relationships
between tables are much simpler than relationships between people. To
make two tables related, you specify one or more fields in one table that
match the same number of fields in the other table. In Figure 3-2, earlier
in this chapter, the Products table relates to the Vendors table because
the Vendor field in the Products table contains values that match the
VendorCode field in the Vendors table. When you look at a record in the