A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.
Relational database systems usually don't allow you to implement a direct many-to-many relationship between two tables. Consider the example of keeping track of invoices. If there were many invoices with the same invoice number and one of your customers inquired about that invoice number, you wouldn’t know which number they were referring to. This is one reason for assigning a unique value to each invoice.
To avoid this problem, you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table. Each record in a join table includes a match field that contains the value of the primary keys of the two tables it joins. (In the join table, these match fields are foreign keys.) These foreign key fields are populated with data as records in the join table are created from either table it joins.
A typical example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students.
The following example includes a Students table, which contains a record for each student, and a Classes table, which contains a record for each class. A join table, Enrollments, creates two one-to-many relationships—one between each of the two tables.
The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID uniquely identifies each class in the Classes table. The Enrollments table contains the foreign keys Student ID and Class ID.
1. Using the example above, create a table named Enrollments. This will be the join table.
2. In the Enrollments table, create a Student ID field and a Class ID field.
Join tables typically hold fields that might not make sense to have in any other table. You can add fields to the Enrollments table, such as a Date field to keep track of when someone started a class, and a Cost field to track how much a student paid to take a class.
3. Create a relationship between the two Student ID fields in the tables. Then create a relationship between the two Class ID fields in the tables.
Using this design, if a student registers for three classes, that student will have one record in the Students table and three records in the Enrollments table—one record for each class the student enrolled in.
•Join tables can access fields and data across tables without having to create a separate relationship. For example, to display a list of all the classes a student enrolled in, create a portal on a layout based on the Students table. Design the portal to show related records from the Classes table. Then add the appropriate fields from Classes to the portal. As you browse through records in the Students layout, the portal displays all the classes a particular student is enrolled in.