Many-to-Many Relationships: Solving the Puzzle

It was mentioned earlier that many-to-many relationships are slightly tricky. After you have an understanding of keys and have seen how they work in a simple ERD, the solution to the many-to-many problem becomes clearer. But first you should understand why it's a problem.

Assume you're building a class registration database. It's intended to show which students are enrolled in which classes. It sounds as if you just need to deal with two entities: students and classes.

Students and classes have a many-to-many relationship. One student may participate in many classes, whereas one class may contain many students. That sounds fine, but how would you actually construct the relationship?

Based on the fundamental rule mentioned earlier, you need a primary key for each entity. Student needs a Student ID, and Class needs a Class ID. If you look at things from the student side for a moment, you know that one student can have many classes. Accordingly, from that viewpoint, Student and Class have a one-to-many relationship. If that's the case, from what you now know about foreign keys, each Class record should store a Student ID to indicate the student record to which it relates .

This won't work, though, for the simple reason that one class can contain many students. This means that the Student ID attribute in Class would have to contain not just one student ID, but a list of student IDs ”one for each enrolled student. The same would be true in the other direction: Each student record needs a Class ID attribute that stores a list of all classes in which the student is enrolled.

One rule of relational database design that has already been touched on is that it's almost always a bad idea to store lists of things in database fields.

As a general rule, when you find you're using a field to store a list of some kind, that's a sign that you need to add another entity to your system where you can then store the list items as single records. This should suggest to you that the many-to-many problem can't be solved without some kind of additional entity. This is true, and it leads to a simple rule:

Resolve a many-to-many relationship by adding an additional entity "between" the two in question.

Figure 5.14 shows an ERD for students and classes with an additional entity to solve the many-to-many problem.

Figure 5.14. An ERD for students and classes.

This middle entity is often called a join table . Each of the "outer" entities now has a one-to-many relationship with this middle entity. Not surprisingly, then, the middle entity has two foreign keys, because it's on the "many" side of two different relationships. It needs to hold both a student ID and a class ID.

NOTE

In previous versions of FileMaker, where each database table was a separate physical file, the term join file was also widely used.

What, if anything, does this entity represent in the real world, and what should it be called? One useful exercise, after you've diagrammed a one-to-many relationship, is to say to yourself (for a join table that resolves a many-to-many relationship between entities A and B), "This entity represents the association of one A with one B." In the example of students and classes, the middle entity represents the association of a specific student with a specific class. If you think of the entity as a database table (which it will almost certainly become), then each row of the table holds one student ID and one class ID. If such a row holds the student ID for student number 1009023 (Sam Tanaka) and the class ID for class H440 (History of the Sub-Sahara), then this record tells us that Sam is enrolled in History 440. This also suggests a good name for the entity: Enrollment. Each record in this table records the enrollment of one student in one class.

Attributes in a Join Entity

You've seen that this "join entity" needs, at the very least, two foreign keys: one pointing to each "side" of a many-to-many relationship. What other attributes does it need?

We emphasized earlier that "every entity, without exception, should have a primary key." Does this mean you should be adding an Enrollment ID to the Enrollment entity? Well, not necessarily . Often the two foreign keys, taken together, constitute a unique key in themselves . In the enrollment example, it wouldn't make sense to have a student enrolled twice in the same class. So student ID 1009023 and class ID H440 should never both occur in the same record more than once. This is an example of something we haven't discussed much: a multi-column key . Often, the two foreign keys in a join entity constitute a primary key when taken together.

You'll need to assess this situation for yourself. If the two foreign keys together constitute a primary key, you're off the hook. But if the combination of those two keys isn't unique, then you need an additional primary key in the join entity.

Besides primary and foreign keys, are there other attributes that are appropriate in a join entity? Well, looking at the example of students and classes, you might wonder where you'd store an important piece of information such as a student's GPA. A student has only one GPA at one time, so you should store that as an attribute of the student. But what about course grades? Where do you record Sam's grade for H440? Well, Sam can be enrolled in many courses, and so can receive many grades. So it's not appropriate to try to store the grade somewhere on Sam's student record. It belongs instead on the enrollment record for that specific course. And, if attendance were being taken, Sam's attendance would logically go on his enrollment record as well.

Additional Many-to-Many Examples

Resolving many-to-many relationships correctly is something that becomes easier with practice. We'll present a few more examples here, just to make the concepts clearer.

Actors and Movies

One actor may be in many movies, and one movie generally involves several actors. To resolve this, you need a join entity containing an Actor ID and a Movie ID. This entity records the participation of one actor in one movie: an appropriate name might be Role or Casting. Do Actor ID and Movie ID together form a primary key? Put differently, can a single actor appear more than once in the same movie? Well, yes ”some virtuoso actors occasionally take several roles in a movie. So you'd want a Role ID in addition to the other two keys.

Books and Libraries

One library obviously holds many books. But can one book be in many libraries? It depends. If you mean a physical copy of a book, the answer is no. If by "book" you mean something more like a "title," the answer is yes. Only one library can hold a given physical copy of Ole Rolvaag's Giants in the Earth . But as a book title, it can be held by many libraries.

Let's concentrate on the idea of the book as a title. In this case, the relationship of Titles to Libraries is many to many. The join entity contains a Title ID and a Library ID. Is this combination of keys unique? No, it isn't. One library may hold several physical copies of Giants in the Earth . So, if you call your join entity a Holding, you can either add a special Holding ID, or add something else, such as a copy number, as an additional attribute. In the latter case, the combination of Title ID, Library ID, and Copy Number would be unique, and would constitute a primary key.

TIP

Good names for join entities can greatly increase the clarity of your designs. If you can find a descriptive name like Role, Enrollment, or Holding, you should use it. If no clearer name presents itself, we recommend naming the join entity by a combination of the names of the entities it's joining: AttorneyClient, for example.