Hack 11. Use a Junction Table

Correctly model a many-to-many relationship.

It's easy to fall into the trap of assuming all relationships are of the one-to-many type. It's true that many data relationships do follow the one-to-many paradigm. For example, one person has zero or more telephone numbers. However, not all data is meant to be modeled in this way.

A perfect example of data that appears to fit the one-to-many model, but doesn't, is the relationship between instructors and students. On the one hand, one instructor does have many students, thereby proving a one-to-many relationship exists. On the other hand, one student has many instructorswhich is also a one-to-many relationship. So, what is the problem?

Figure 1-24 shows one way to model instructors and students. The instructor table occupies the one spot and the student table occupies the many spot. Instructors and students get together for appointments. This model works but emphasizes that instructors are of a different level than students, which might not be true.

Figure 1-24. An inefficient one-to-many relationship

In Figure 1-24, the student table is also required to have the instructor ID as the foreign key. This is acceptable, but now look at the appointments table; it considers appointments as belonging to students, but appointments belong to both instructors and students.

Figure 1-25 shows how to resolve the dilemma in the data model. Because appointments belong to both instructors and students, that is how the model should look. The appointments table serves as a junction table between instructors and students.

A junction table becomes the many table for two or more other tables. All the key fields of the one tables become foreign keys in the junction table. Any other pertinent fields are included in the junction table. In this example, the junction table has fields for the date and time the instructor and student will meet. Also, the student table no longer has the instructor ID as a foreign key. In this example, instructors and students have no hierarchy; therefore, it makes sense that one doesn't serve as a many to the other.

Figure 1-25. A better model