Microsoft Office Tutorials and References
In Depth Information
What Are Relationships?
Products table, you can find out which vendor you buy the product from by
finding the record in the Vendors table that has the same value in the
Relationships come in several flavors:
✦ One-to-many: One record in one table matches no, one, or many records
in the other table. The relationship in Figure 3-2 works this way because
one vendor can provide many products.
✦ One-to-one: One record in one table matches one record (or no record)
in the other table.
✦ Many-to-many: Zero, one, or many records in one table match zero, one,
or many records in the other table.
The next three sections explain these three types of relationships.
This type of relationship is the most common among tables. In a one-to-many
relationship, many records in one table can match one — and only one —
record in another table. Here are some examples of one-to-many relationships:
✦ Items in customer orders: If you run a store, customers frequently buy
several items at the same time. You may have an Orders table with one
record for each sale you make, but a sale can include several products.
(Maybe someone buys two books and a pair of socks.) One record in the
Orders table could match several records in the OrderDetails table.
✦ Vendors and invoices: If your company buys many items from another
company, you end up with a bunch of invoices from (and payments
to) that company. The relationship between the Vendors table and the
Invoices table in an accounting database is one-to-many.
✦ People living in states or provinces: The United States and Canada use
standard two-letter state and province abbreviations, and if you have an
address list, these codes should be correct. (Quick — is Quebec QU or PQ?
No peeking.) To make sure that you type the valid state and province codes
for the United States and Canada, you can create a StateProvinceCodes
table against which you can validate entries in the State field of your
Addresses table. One record in the StateProvinceCodes table can match
many records in the Addresses table.
You use a one-to-many relationship to avoid storing information from the
“one” table multiple times in the “many” table. You don’t want to store all
the information about each student in the record for every course, for
example — unless you want to hear the groan of an overloaded disk drive. Storing
each student’s information in one place (the Students table) and storing only
the student’s name and/or student ID in the CourseRegistrations table is
more efficient (and easier to maintain).