Access 2007: The Missing Manual

Chapter 5. Linking Tables with Relationships

The tables you’ve seen so far lead lonely, independent lives. You don’t find this isolation with real-world databases. Real databases have their tables linked together in a web of relationships.

Suppose you set out to build a database that can manage the sales of your custom beadwork shop. The first ingredient’s simple enough—a Products table that lists your merchandise—but before long you’ll need to pull together a lot more information. The wares in your Products table are sold in your Orders table. The goods in your Orders table are mailed out and recorded in a Shipments table. The people in your Customers table are billed in your Invoices table. All these tables—Products, Orders, Shipments, Customers, and Invoices—have bits of related information. As a result, if you want to find out the answer to a common question (like, “How much does Jane Malone owe?” or “How many beaded wigs did we sell last week?”), you’ll need to consult several tables.

Based on what you’ve learned so far, you already know enough to nail down the design for a database like this one. But relationships introduce the possibility of inconsistent information. And once a discrepancy creeps in, you’ll never trust your database the same way again.

In this chapter, you’ll learn how to explicitly define the relationships between tables. This process lets you prevent common errors, like data in different tables that doesn’t sync up. It also ...