March 4, 2011, 7:40 a.m.
posted by suppafly
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.
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). 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).
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.
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:
And you have these records in your Books table:
Here's the Authors_Books table that binds it all together:
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).
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.