Ms Office Tune Up

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.

Popularity: 3% [?]