Contents
Related Content
Create a many-to-many relationship in Access Learn how to create a many-to-many relationship in Access.

The many-to-many relationship

The many-to-many relationship is a relationship where multiple rows from table A can correspond to multiple rows in table B. An example of such a relationship is a school where teachers teach students. In most schools each teacher teaches multiple students and each student can be taught by multiple teachers.

The relationship between beer distributors and the beers they distribute is a many-to-many relationship too. A distributor will in most cases distribute more than one type of beer and each type of beer can be distributed by multiple distributors.

Note that in database design the question you should ask yourself is not whether a certain relationship exists at the moment, but if a certain relationship is likely to exist in the future. If at present all distributors distribute multiple types of beers, but each type of beer is distributed by only one distibutor, you are looking at a one-to-many relationship. (One distibutor has multiple beers, but each beer had only one distributor). Don't be tempted to model this situation in a one-to-many relationship. There is a good chance that in the future two or more distributors will distrubute the same type of beer and when that happens your database is not prepared with a one-to-many relationship between distibutors and beer types.

Modelling a many-to-many relationship

A many-to-many relationship is modelled with tree tables. Two 'source' tables and one junction table. The primary key of the junction table A_B is a composite key. It is made of two fields: the two foreign key fields that refer to the primary key of table A and table B.

Many

All primary keys must be unique. This implies that the combination of field A and B must be unique in table A_B.

The database design example below shows you the tables that could exist in the many-to-many relationship between Belgian beer brands and their distributors in the Netherlands. Notice how all combinations of beer_id and distibutor_id are unique in the junction table.

Beer tables

Beer

Duvel

The tables above tie beers and distributors into a many-to-many relationship using the beer_distibutor junction table. Note how 'Gentse Tripel' (157) is distributed by Horeca Import NL (157, AC001), Jansen Horeca (157, AB899) and Petersen Drankenhandel (157, AC009). Vice versa, Petersen Drankenhandel is the distributor of 3 beers from the beer table, namely Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) and Jupiler (163, AC009).

Note that in the tables above the primary keys fields are colored blue and underlined. In database design models, primary keys are usually underlined. Also note (again) that the association table beer_distributor has a primary key composed of two foreign keys. Junction tables always have a composite primary key.

There is one more important thing to notice about the many-to-many relationship. The many-to-many relationship consists of two one-to-many relationships. Both the beer table and the distributor table have a one-to-many relationship to the junction table.

Another many-to-many example: hotels bookings

As a final example let me show you how bookings of hotel rooms by guests could be modeled.

bookings-data-model

The junction table of this many-to-many relationship has extra fields.

In this example you see that there is a many-to-many relationship between guests and rooms. One room can be booked by many guests over time and over time a guest can book many rooms in the hotel. The junction table in this example is not a classic junction table that consists of just two foreign keys, but it is a separate entity that has a relationship with two other entities.

You will often encounter situations where the junction of two entitities is a new entity.