Database Relationships  «Prev  Next»

Lesson 4Creating many-to-many relationships
ObjectiveCreate a many-to-many relationship.

Creating Many-to-Many Relationships in Access

Creating many-to-many relationships is different from the other two types of relationships only in that many-to-many relationships take about twice as many steps as the other two. Many-to-many relationships are in fact two one-to-many relationships. You can see that here in this figure:
Many-to-many relationships are in fact 2 one-to-many relationships
Three tables and their relationships in Microsoft Access. Below is the detailed analysis:
  1. Tables:
    • Clients Table:
      • Fields:
        • ClientNo (Primary Key)
        • Company
        • FirstName
        • LastName
        • Address
    • Consultants Table:
      • Fields:
        • ConsultantID (Primary Key)
        • LastName
        • FirstName
        • SSN
        • ConsultingTypeID
    • Projects Table:
      • Fields:
        • ProjectID (Primary Key)
        • ClientNo (Foreign Key)
        • ConsultantID (Foreign Key)
        • ProjectDescription
        • StartDate

2) Relationships:
  1. Clients → Projects:
    • One-to-Many relationship:
      • ClientNo in the Clients table is related to ClientNo in the Projects table.
      • A single client can have multiple projects.
  2. Consultants → Projects:
    • One-to-Many relationship:
      • ConsultantID in the Consultants table is related to ConsultantID in the Projects table.
      • A single consultant can work on multiple projects.

Summary of the Relationships: - The `Projects` table acts as a **junction table** between the `Clients` and `Consultants` tables, facilitating a **many-to-many** relationship between clients and consultants through projects. Many-to-many relationships are in fact two "one-to-many" relationships

Note that in this case, the Projects table is referred to as the Junction table, in that it creates a junction between the Clients and Consultants tables. Some of your many-to-many relationships will simply evolve from you establishing one-to-many relationships. But there are those cases where you will purposefully create a many-to-many relationship, so here are the general steps:
  1. Create the tables necessary for your data. Remember that because Access represents many-to-many relationships by using two one-to-many relationships, you will be using three tables. Again, look at the figure above to see how the three tables are related.
  2. Open the Relationships Layout window, by choosing Relationships from the Tools menu.
  3. Clear the Relationships Layout window by clicking the Clear Layout toolbar button.
  4. Use the Add Table dialog to add the tables you want. In this case, Clients, Projects, and Consultants.
  5. Create the new relationships by using drag and drop with the necessary fields, as described in the last lesson.
View the following images which describe the steps listed above.

These are the tables to be related
1) Here are three tables to be related

After you click the ClientNo field in Clients, you drag the cursor over to the ClientNo field in Projects and let go of the mouse
2) After you click the ClientNo field in Clients, you drag the cursor over to the ClientNo field in Projects and let go of the mouse.

The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship
3) The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship.

Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen.
4) Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen. Then you click the Create button.


You can now see the first relationship established for the one-to-many relationship.
5) You can now see the first relationship established for the one-to-many relationship.

You can now see the first relationship established for the one-to-many relationship.
6) Now you repeat the same steps for the Projects and Consultants table. You would start by clicking the ConsultantID field Projects, the dragging the cursor over the ConsultantID field in Consultants and letting go of the mouse

The Edit Relationships dialog again appears.
7) The Edit Relationships dialog again appears.

You check the Referential Integrity checkbox and then click the Create button
8) Again, you check the Referential Integrity checkbox and then click the Create button

Apply, Filter, Sort
9) The diagram above shows the final relationship between tables

Creating many-to-many relationships

In database design, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent record for which there are many children in B and vice versa. For instance, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors. Because most database management systems only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table (also called cross-reference table), say, 
AB with two one-to-many relationships A -> AB and 
B -> AB.

In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).

Database Relationships - Quiz

Click the Quiz link below to answer a few questions about working with different types of relationships. Database Relationships - Quiz

Creating Many To Many Relationships - Exercise

Taking what you just saw in the SlideShow, work on the following exercise.
Creating Many To Many Relationships - Exercise
In the next lesson, you will learn how to use relationships to manage referential integrity.

SEMrush Software