Many to Many Relationships
Many to Many Relationships are conceptually difficult for many people to grasp. Douglas Alder tackles the subject in an effort to explain it to one of his clients:
One-to-Many Relationships are the standard way of building a relational database. Sometimes a developer comes up against situations where they need something more complicated. Think of instances where you might need Many-to-Many Relationships. In the situation I am working on, we needed a way for multiple Customers to be linked to a single Form. The form was a waiver and customers were signing up for themselves and their children at the same time on the same waiver form. We needed a way to link the waivers not just to the parent, but also to the children.
The most common example is a database for a school: Students take many classes and each class has many students. Each class may also have many teachers and each teacher teaches many classes.
In each case, join tables are the answer to the problem.
Another bonus: If you use the join table as the context for the layout, it’s easy to pull in related records on either side of the join table. It’s also possible to store commonly needed information in the join table instead of the two tables being joined. Sometimes, this is easier for reporting purposes.
Join tables are powerful tools that allow the programmer to tame the tough programming challenges encountered on a regular basis.
Read the whole post over at Homebase.