Mapping Many-to-Many Relationships to a Database Diagram

Many-to-many relationships enable you to relate many rows in one table to many rows in another table. For example, you could 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 either table would incorrectly assume 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 columns in the junction table.

To create a many-to-many relationship between tables

1. In your database diagram, add the tables that you want to create a many-to-many relationship between.
   
   
2. Create a third table by right-clicking the diagram and choosing New Table from the shortcut menu. This will become the junction table. For details, see Adding Tables to a Database Diagram.
   
   
3. In the Choose Name dialog box, change the system-assigned table name. For example, the junction table between the titles table and the authors table is now named titleauthors. For details, see Renaming a Table.
   
   
4. Copy the primary key columns from each of the other two tables to the junction table. You can add other columns to this table, just as you can to any other table. For details, see Copying Columns from One Table to Another.
   
   
5. In the junction table, set the primary key to include all the primary key columns from the other two tables. For details, see Defining a Primary Key.
   
   
6. Define a one-to-many relationship between each of the two primary tables and the junction table. For details, see Creating a Relationship Between Tables.

Note   The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a junction table, see Creating Insert Queries.