Access 2007[c] The Missing Manual

5.1. Relationship Basics

One of any database's key goals is to break information down into distinct, manageable pieces. In a well-designed database, you'll end up with many tables. Although each table records something different, you'll often need to travel from one table to another to get all the information you want.

To better understand relationships (of the non-romantic kind, anyway), consider an example. The following section demonstrates two ways to add information to the bobblehead database: one that risks redundant data, and one that avoids the problem by properly using a relationship.

5.1.1. Redundant Data vs. Related Data

Think back to the Dolls table you created in Chapter 1 to store a list of bobblehead dolls. One of the Dolls table's pieces of information is the Manufacturer field, which lists the name of the company that created each doll. Although this seems like a simple-enough detail, it turns out that to properly assess the value of a bobblehead, you need to know a fair bit more about the manufacturing process. You may want to know things like where the manufacturing company's located, how long it's been in business, and if it's had to fight off lawsuits from angry customers.

If you're feeling lazy, you could add all this information to the Dolls table, like so (the shaded columns are the new ones):

Table 5-1.

Your first reaction to this table is probably to worry about the clutter of all these fields. But don't panicin the real world, tables must include all the important details, so they often grow quite wide. (That's rule #3 of data design, from Section 2.5.4.) So don't let the clutter bother you. You can use techniques like column hiding (Section 3.1.4) to filter out the fields that don't interest you.

Although column clutter isn't a problem, another issue lurks under the surface in this exampleredundant data. A well-designed table should list only one type of thing. This version of the Dolls table breaks that rule by combining information about the bobblehead and the bobblehead manufacturer.

This situation seems innocent enough, but if you add a few more rows, things don't look as pretty:

Table 5-2.

Once you have two bobbleheads that were made by the same company (in this case, MagicPlastic), you've introduced duplicate data, the curse of all bad data-bases. (You'll recognize this as a violation of rule #4 of good database design, from Section 2.5.4.) The potential problems are endless:

• If MagicPlastic moves its plants from China to South Korea, you'll need to update a whole batch of bobblehead records. If you were using two tables with related data (as you'll see next ), you'd have just one record to contend with.

• It's all too easy to update the manufacturer information in one bobblehead record but miss it in another. If you make this mistake, you'll wind up with inconsistent data in your table, which is even worse than duplicate data. Essentially, your manufacturer information will become worthless because you won't know which record has the correct details, so you won't be able to trust anything.

• If you want to track more manufacturer-related information (like a contact number) in your database, you'll have to update your Dolls table and edit every single record . Your family may not see you for several few weeks.

• If you want to get information about manufacturers (but not dolls), you're out of luck. For example, you can't print out a list of all the bobblehead manufacturers in China (at least not easily).

It's easy to understand the problem. By trying to cram too many details into one spot, this table fuses together information that would best be kept in two separate tables. To fix this design, you need to create two tables that use related data . For example, you could create a Dolls table like this:

Table 5-3.

And a separate Manufacturers table with the manufacturer-specific details:

Table 5-4.

This design gives you the flexibility to work with both types of information (dolls and manufacturers) separately. It also removes the risk of duplication. The savings are small in this simple example, but in a table with hundreds or thousands of bobblehead dolls (and far fewer manufacturers), the difference is dramatic.

Now, if MagicPlastic moves to South Korea, you need to update the Location field for only one record, rather than many instances in an overloaded Dolls table. You'll also have an easier time building queries (Chapter 6) that combine the information in neat and useful ways. (For example, you could find out how much you've spent on all your MagicPlastic dolls and compare that with the amounts you've spent for dolls made by other manufacturers.)

5.1.2. Matching Fields: The Relationship Link

This bobblehead database shows you an example of a relationship . The telltale sign of a relationship is two tables with matching fields. In this case, the tip-off's the Manufacturer field, which exists in both the Dolls table and the Manufacturers table.

Using these linked fields, you can start with a record in one table and look up related information in the other. Here's how it works:

• Starting at the Dolls table , pick a doll that interests you (let's say Yoda). You can find out more information about the manufacturer of the Yoda doll by looking up "MagicPlastic" in the Manufacturers table.

• Starting at the Manufacturers table , pick a manufacturer (say, Rebobblicans). You can now search for all the products made by that manufacturer by searching for "Rebobblicans" in the Dolls table.

In other words, a relationship gives you the flexibility to ask more questions about your data, and get better answers.

5.1.3. Linking with the ID Column

In the previous example, the Dolls and Manufacturers tables are linked through the Manufacturer field, which stores the name of the manufacturing company. This seems like a reasonable designuntil you spend a couple of minutes thinking about what might go wrong. And databases experts are known for spending entire weeks contemplating inevitable disasters.

Here are two headaches that just may lie in store:

• Two manufacturers have the same company name . So how do you tell which one made a doll?

• A manufacturer gets bought out by another company and changes its name . All of a sudden, there's a long list of records to change in the Dolls table.

You might recognize these problems, because they're similar to the challenges you faced when you tackled primary keys (Section 2.4). As you learned, it's difficult to find information that's guaranteed to be unique and unchanging. Rather than risk problems, you're better off just relying instead on an AutoNumber field, which stores an Access-generated ID number.

Interestingly enough, you use the same solution when linking tables. To refer to a record in another table, you shouldn't use just any piece of informationinstead, you should use the unique ID number that points to the right record. Here's a redesigned Dolls table that gets it right by changing the Manufacturer field to ManufacturerID:

Table 5-5.

If you take a look back at the Manufacturers table (Section 5.1.1), then you can quickly find out that the manufacturer with the ID value 1 is MagicPlastic.

This design's the universal standard for databases. However, it does have two obvious drawbacks:

• The person adding records to the Dolls table probably doesn't know the ID of each manufacturer.

• When you look at the Dolls table, you can't tell what manufacturer created each doll.

To solve both these problems, use a lookup . Lookups show the corresponding manufacturer information in the Dolls table, and they also let you choose from a list of manufacturers when you add a record or edit the ManufacturerID field. (You saw how to use lookups with value lists in Section 4.4.1. You'll learn how to use lookups to bring together related tables, like Dolls and Manufacturers, in Section 5.2.5.)

5.1.4. The Parent-Child Relationship

No, this isn't a detour into feel-good Dr. Phil psychology. Database nerds use the labels parent and child to identify the two tables in a relationship, and keep track of which one's which.

Here's the analogy. As you no doubt know, in the real world a parent can have any number of children. However, a child has exactly one set of parents. The same rule works for databases. In the bobblehead database, a single manufacturer record can be linked to any number of doll records. However, each doll record refers to a single manufacturer. So according to the database world's strange sociology, Manufacturers is a parent table and Dolls is a child table. They're linked by a parent-child relationship .

It's important to realize that you can't swap the parent and child tables around without changing your relationship. It's incorrect to suggest that Dolls is the parent table and Manufacturers is the child table. You can see that such a suggestion would break the parent-child analogy: a single doll can't have more than one manufacturer, and a manufacturer isn't limited to creating a single doll. In order to prevent problems and all-around fuzzy thinking, you need to know exactly which table's the parent and which one's the child.

If you have database-savvy friends , you'll hear the term parent-child relationship quite a bit. The same relationship's also called a one-to-many relationship (where one is the parent and many represents the children, because a single parent record in one table can link to several child records in the other). It's the most common relationship, but not the only oneyou'll learn about two other types in Section 5.3 and Section 5.3.2.