Section 5.3. More Exotic Relationships | Access 2007 for Starters[c] The Missing Manual

5.3. More Exotic Relationships

As you learned in Section 5.1.4, a one-to-many (a.k.a. parent-child ) relationship that links a single record in one table to zero, one, or more records in another table is the most common relationship. A single manufacturer could be linked to one bobble-head, several bobbleheads, or no bobbleheads at all.

Along with one-to-many relationships, there are two subtly different types of relationships: one-to-one relationships and many-to-many relationships. You'll learn about both in the following sections.

5.3.1. One-to-One Relationship

A one-to-one relationship links one record in a table to zero or one record in another table. People sometimes use one-to-one relationships to break down a table with lots of fields into two (or more) smaller tables.

A Products table may include detailed information that describes the product and its price, and additional information that describes how it's built. This information's important only to the people in the engineering department, so you may choose to split it into a separate table (named something like ProductsEngineering). That way, sales folks don't need to think about it when they're making an order. Other times, you might break a table into two pieces because it's simply too big. (Access doesn't let any table have more than 255 fields.)

You create a one-to-one relationship in the same way you create a one-to-many relationshipby dragging the fields in the Relationships tab (Figure 5-15). The only difference is that the linked fields in both tables need to be set to prevent duplicates. This way, a record in one table can (at most) be linked to a single record in the other table.

Note: A field prevents duplicates if it's set as the primary key for a table (Section 2.4), or if it has an index that prevent duplicates (Section 4.1.3).

Figure 5-15. When you link two fields that don't allow duplicates (and you have the Enable Referential Integrity option switched on), Access realizes that you're creating a one-to-one relationship. Access places the number 1 at each side of the line to distinguish it from other types of relationships. In this example, the ID column in the Products table and the ID column in the ProductsEngineering table are both primary keys of their respective tables, so there's no way to link more than one record in ProductsEngineering to the same record in Products.

WORD TO THE WISE
Approach One-to-One Relationships with Caution

One-to-one relationships are extremely rare in Access. Usually, features like column hiding (Section 3.1.4) and queries (Chapter 6) are better choices if you want to see only some of the fields in a table.

Splitting a table into two pieces complicates the design of your database, and you'd generally do it only if you have other reasons to separate the tables. Some possible examples include:

The two parts of the table need to be placed in separate databases so that different people can copy them to separate computers and edit them independently.
You want to stop prying eyes from seeing sensitive data. One way to do this is to put the information that should be secure into a separate table, and put that separate table in a different, more secure database file.
You have a table that stores huge amounts of data, like an Attachment field (Section 2.3.8) with large documents. In this case, you might get better performance by splitting the table. You might even choose to put one half of the table in a separate database.
Some of the data in your table's optional. Rather than include a lot of blank fields, you can pop it into a separate table. If you don't need to include this information, then you don't need to add a record to the linked table.

If you don't have these requirements, then you're better off creating a single large table.

5.3.2. Many-to-Many Relationship

A many-to-many relationship links one or more records in one table to one or more records in another table. Consider a database that tracks authors and books in separate tables. Best-selling authors don't stop at one book (so you need to be able to link one author to several books). However, authors sometimes team up on a single title (so you need to be able to link one book to several authors). A similar situation occurs if you need to put students into classes, employees into committees , or ingredients into recipes. You can even imagine a situation where this affects the bobblehead database, if more than one manufacturer can collaborate to create a single bobblehead doll.

Many-to-many relationships are relatively common, and Access gives you two ways to deal with them.

5.3.2.1. Junction tables

Junction tables are the traditional approach for dealing with many-to-many relation-ships, and people use them throughout the database world (including in industrial-strength products like Microsoft SQL Server). The basic idea's that you create an extra table that has the sole responsibility of linking together two tables.

Each record in the junction table represents a link that binds together a record from each table in the relationship. In the books and authors database, a single record in the junction table links together one author with one book. If the same author writes three books, then you need to add three records to the junction table. If two authors work on one book, then you need an additional record to link each new author.

Suppose you have these records in your Authors table:

Table 5-6.

ID	FirstName	LastName
10	Alf	Abet
11	Cody	Pendant
12	Moe	DeLawn

And you have these records in your Books table:

Table 5-7.

ID	Title	Published
402	Fun with Letters	January 1, 2007
403	How to Save Money by Living with Your Parents	February 24, 2008
404	Unleash Your Guilt	May 5, 2007

Here's the Authors_Books table that binds it all together:

Table 5-8.

ID	AuthorID	BookID
1	10	402
2	11	403
3	12	403
4	11	404

Authors_Books is a junction table that defines four links. The first record indicates that author #10 (Alf Abet) wrote book #402 (Fun with Letters). As you traverse the rest of the table, you'll discover that Cody Pendant contributed to two books, and two authors worked on the same book (How to Save Money by Living with Your Parents).

Tip: The junction table often has a name that's composed of the two tables it's linking, like Authors_Books.

The neat thing about a junction table is that it's actually built out of two one-to-many relationships that you define in Access. In other words, the junction table's a child table that has two parents. The Authors table has a one-to-many relationship with the Authors_Books table, where Authors is the parent. The Books table also has a one-to-many relationship with Authors_Books, where Books is the parent. You can define these two relationships in the Relationships tab to make sure referential integrity rules the day (Figure 5-16).

Although junction tables seem a little bizarre at first glance, most database fans find that they quickly become very familiar. As with the one-to-many relationships you used earlier, you can create lookups (Section 5.2.5) for the AuthorID and BookID fields in the Authors_Books table. However, you'll always need to add the Authors_Books record by hand to link an author to a book.

Figure 5-16. The many-to-many relationship between Authors and Books is really two one-to-many relationships that involve the Authors_Books table. Once you've defined these relationships, you can't link to an author or book that doesn't exist, and can't delete an author or book that has a record in the Authors_Books table.