5.3. More Exotic Relationships
As you learned in Section 5.2, 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 bobblehead, 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 Products-Engineering). 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 prevents duplicates (Section 4.1.3).
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.
18.104.22.168. Junction tables
Junction tables are the traditional approach for dealing with many-to-many relationships, 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 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.
22.214.171.124. Multi-value fields
Up until Access 2007, junction tables were the only option for many-to-many relationships. But to support the SharePoint integration features (Chapter 21), Access 2007 adds a new feature: multi-value fields .
As its name suggests, a multi-value field can store more than one value. This capacity neatly solves the problem of many-to-many relationships. The trick's to configure the linked field in the child table as a multi-value field. Reconsider the authors and books example. Without the junction table, you'd need to add an AuthorID column to the books table to indicate which author wrote a given Book:
But an ordinary field holds a single value. Thus, this table can indicate only one of the two authors for book #403.
However, if you change AuthorID to allow multiple values, you can enter a list of authors, like this:
Behind the scenes, a multi-value field actually uses a junction table. However, Access hides that detail from you, which makes it a bit easier to link related records.
In order to create a multi-value field, you need to use a lookup. As you've already seen (Section 5.3), you can choose to turn on this option in the last step of the Lookup wizard. Alternatively, if you already have a lookup in a field, you just need to make one minor modification. Open the table in Design view, choose the field that has the lookup (like ManufacturerID), and then, in the Field Properties section, click the Lookup tab. Look for the Allow Multiple Values property, and change it from No to Yes.
Note: Once you change your field to support multiple values, you can't switch back.
Figure 5-17 shows a multi-value lookup list in action.
Multi-value fields are available only if you're using the new .accdb database format (Section 1.2.2). You can't use them with an .mdb file (a database that was created in Access 2003 and hasn't been converted yet).
Multi-value fields also cause headaches if you want to upsize your database to SQL Server (as described in Chapter 20), because SQL Server doesn't support them. So if there's a possibility that you'll need to share your database with lots of people (say, in a large company), and you might move your data to a high- powered SQL Server database someday, avoid multi-value fields.
Note: Multi-value fields don't pose a problem if you want to upsize your database to SharePoint Server (as described in Chapter 21).