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 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.
Figure 1: Select the Insert Many-to-Many Relationship tool
Figure 2:A new intersection entity is created.
Build your next data model with DeZign for Databases
available directly from Datanamic's download section
Try Out DeZign for Databases (14 days free trial)Resources
Learn- DeZign for Databases: Learn more about DeZign for Databases.
- Introduction to data modeling: Learn the basic steps in designing/modeling a database.
- Display data types in a diagram: Learn how to display data type and/or domain info in the entity boxes on your diagram.
- 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.