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 grayed-out columns are the new ones):
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.3.) 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:
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 databases. (You'll recognize this as a violation of rule #4 of good database design, from Section 2.5.4.) The potential problems are endless:
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:
And a separate Manufacturers table with the manufacturer-specific details:
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.)
Note: Access includes a tool that attempts to spot duplicate data in a table and help you pull the fields apart into related tables. (To try it out, choose Database Tools Analyze Analyze Table.) Although its a good idea in theory, this tool really isn't that useful. You'll do a much better job of spotting duplicate data and creating well designed tables from the start if you understand the duplicate-data problem yourself.
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.
Note: In this example, the fields that link the two tables have the same name in both tables: Manufacturer. However, you don't have to do it this way. You can give these fields different names , so long as they have the same data type.
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:
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:
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:
If you take a look back at the Manufacturers table (Section 5.1.2), 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:
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.2. You'll learn how to use lookups to bring together related tables, like Dolls and Manufacturers, in Section 5.2.)
Tip: For even more power, you can use a join query (Section 6.3). A join query lets you fill in all the manufacturer details alongside the doll information so you can view them side by side.
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 .
Tip: Don't think too hard about the parent-child analogy. It's not a perfect match with biological reality. For example, in the bobblehead database, you may create a manufacturer that doesn't link to any dolls (in other words, a parent with no children). You still call that record a parent record, because it's part of the parent table.
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.
Tip: If you have trouble identifying which table's the parent, there's a simple rule to steer you right. The child table always contains a piece of identifying information from the parent table. In the bobblehead database, the Dolls table contains the ManufacturerID field. On the other hand, the Manufacturer table doesn't have any doll information.
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.
Note: Relationships are so common in modern-day databases that software like Access is often described as a relational database management system (RDBMS). A database without relationships is about as common as a beachfront resort in Ohio.