Skip to content

Many to many relationships

A primary key - foreign key pair can be used to represent a 1:* relationship; however, they are not the only kind you will come across. Consider a library database that stores information about books and borrowers. The ER diagram might look like this:

Books and borrowers

The diagram tells us that a BORROWER may borrow zero to many BOOKS, and that a BOOK may be borrowed by zero to many BORROWERS. The relationship is therefore *:*, so where does the foreign key go?

The answer is that you can't represent a *:* relationship directly using a relational database. You always have to resolve it into a set of 1:* relationships. This actually turns out to be very easy to do, and the solution is always the same:

  1. Create a third table to represent the relationship
  2. Add the primary keys from the original tables as foreign keys in the new one

The result is shown below. In effect, you have inserted the third table in between the original ones. Notice the multiplicity labels on the new relationships. Because the third table has foreign keys to both of the original tables, it is at the "many" end of both of the new relationships.

Resolving a many-to-many relationship

In library terminology, a "loan" is an instance of borrowing an item. It is therefore the natural name for our new table. At the moment, the LOAN table only contains two foreign keys. In many cases, that is all that is required, and the table known as a bridge table or a link table. Its only function is to resolve the *:* problem. In this case, though, there is some more information that needs to be attached to a LOAN, such as the date the item was borrowed, and the date it is due to be returned.