Data Modeling

Many to Many Relationships

In the data modeling world there are several types of relationships between entities. In this article we will tell you more about the many-to-many relationship type.

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between employees and projects: employees can work on various projects, and a project can have many employees working on it. Another example is the relationship between orders and product: you can order various products when placing an order, and each product is contained in zero or more orders.

many-to-many relationship

Many-to many relationship between Employee and Project.

Relational databases don't allow you to implement a direct many-to-many relationship between two tables because it is not possible to store the data efficient. For efficient processing, you can convert the many-to-many relationship tables into two one-to-many relationships by connecting these two tables with an intersection table that contains the keys of both tables.

Our data modeling tool DeZign for Databases, automatically resolves many-to-many relationships. This means that the tool will create the intersection entity for you when you create a many-to-many relationship between two entities.

To do so, Select the Insert Many-to-Many Relationship tool from the drawing tools menu (see figure 1). Now click on the Employee entity and release the mouse button when you are above the Project entity. DeZign for Databases will now automatically resolve the many-to-many relationship and will create the intersection entity Employee_Project for you (see figure 2). The primary key of the Employee entity and the primary key of the Project entity will both form the primary key of the new intersection entity.

draw a many-to-many relationship

Figure 1: Select the Insert Many-to-Many Relationship tool

create an intersection table

Figure 2:A new intersection entity is created.

Build your next data model with DeZign for Databases

Download the free DeZign for Databases trial


Learn Get products and technologies
  • Make your next data model with DeZign for Databases trial, available for download directly from Datanamic's download section.
  • Need (realistic) test data for your new database? Try out our data generator.