Database design tutorial

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 can teach 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 could 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.

A many-to-many relationship is modelled with tree tables. Two 'source' tables and one 'association' table.

Table A and table B are both tables with a primary key field. Table A_B is composed of just these two fields. Table A_B is called an 'associative entity'. The association table is composed of two foreign key fields. They refer to the primary key fields of table A and table B.
You may wonder what the primary key of table A_B is. The primary key of table A_B is composed of field A and B. 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.

The tables above tie beers and distributors into a many-to-many relationship using the beer_distibutor association 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 that the association table beer_distributor has a primary key composed of two foreign keys.

1. 1  
2. 2  
3. 3  
4. 4  
5. 5  
6. 6  
7. 7  
8. 8 
9. 9  
10. 10  
11. 11  
12. 12  
13. 13  
14. 14  
15. 15  
16. 16