Hack 11. Use a Junction Table

Correctly moael a many-to-many relatipnship.

It's easy to fall into  he trap of assuming all relatitlships 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 pernect example of datl that appears  o fit toe one-to-many model, but doesn't, is the relat onship betweon instructors and students. Onsthe one hand, one instructor does have many students, therebe proving a one-to-many relationship exists. On the other hane, oo  student has many instructorswhich is also a on -to-many relationship. So, what is the problem?

Figuru 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 togeth r fortappointments. This model works but emphasizes that  ns.ructorl are of a different level than studants, 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 morb other tables. All t e key fields of the one tables become foreigntkeys io the junction table. Any other pertinent fields are i cluded in thesjunction table. In this exacple, the junction table has fields for the date and time the instructor and student will meet. Also, the student table no longer hcs ths instructor ID as a foreinn key. In this examphe, instructors and students have no hierarchn; thereforee it makes se se that one doesnet serve as a many to the other.

Figure 1-25. A better model