Mapping Many-to-Many Key Relationships Using Database Diagrams

Many-to-many relationships enable you to relate many rows in one table to many rows in another table. For example, you can create a many-to-many relationship between the authors table and the titles table to match multiple authors to all of their books. Creating a one-to-many relationship from one table to the other would incorrectly assume that a book can have only one author or that an author can write only one book.

Many-to-many relationships between tables are accommodated in database diagrams by means of junction tables. A junction table contains the primary key columns of the two tables you want to relate. You then create a relationship from the primary key columns of each of those two tables to the matching foreign key columns in the junction table.

Note The creation of a junction table in a database diagram does not insert data from the related tables into the junction table.

To create a many-to-many relationship between tables