Page 9 of 12PREVNEXT

Create relationships for a new database

The tables in a many-to-many relationship


You have a many-to-many relationship when a single record in one table can relate to many records in another, and a single record in that second table can also relate to many records in the first. For example, say your company has several types of computers and several technicians, with each technician certified to work on some, but not all, of the computers. Each technician can be related to more than one computer, and in turn, each computer can be related to more than one technician.

To track who can work on a given machine, you create a many-to-many relationship by adding the primary keys from both sides of the relationship to a third table, called a junction or link table. In other words, a many-to-many relationship is really just a pair of one-to-many relationships.

The picture shows a typical many-to-many relationship. You can see it has a relatively simple structure — a junction table with a pair of foreign keys and some related fields.

You use the data in that junction table to answer questions such as, "Which computers did a given technician work on last month?"

You'll create a many-to-many relationship in the practice, and links in the Quick Reference Card take you to more information about this type of relationship.

Page 9 of 12PREVNEXT