Many-to-many relationships

Many-to-many relationship is a type of cardinality that refers to a relationship between two entities in an entity relational diagram (between two tables in a database). A simple example would be a relationship between the entities student and course. Each student can have multiple courses and each course is for multiple students.

Graphically, the many to many relationship is usually represented in a logical diagram with crow’s foot notation.

many to many relationship in logical model (not supported)

In a relational database, this relationship is then usually implemented using a join table, otherwise known as a junction or associative table with two one-to-many relationships. It is thus a model that can be represented as follows.

many-to-many relationship and junction table in er diagram

As can be seen, the join table contains foreign keys from both referenced tables.
(Read more about the differences between primary and foreign keys).

Many-to-many relationships in Moon Modeler

Since Moon Modeler is used to design data models as well as generate SQL scripts, the entity-relationship diagram is displayed as the objects in the database will be physically created. Therefore, it is usually necessary to create a join table in the diagram as well.

Procedure for creating the relationships

  • Create three tables: student, course, and student_course.
  • Then create two relationships. Click the Relationship button on the main toolbar and then click on the student table and then the student_course table. Use the same procedure to create a relationship between the course and student_course tables.
associative table with default primary key

Now it is up to you whether you want to keep the primary key in the student_course table (which is inserted into the new table when you add it into the diagram) or remove the primary key and mark both columns of the foreign key as the primary key, creating the primary foreign key.

join table with primary foreign key

Relationships between array fields or array columns

Note: This feature will be available in Moon Modeler version 4.2.

In projects for MongoDB and also PostgreSQL it is possible to visualize many-to-many relationships without the join table (junction collection). These are cases where the table column (collection field) in one or both tables (collections) is an array.

To change the relationship from one-to-many to many-to-many, select the Many to value from the cardinality settings for the parent table.

cardinality for parent table

Additional information can be added to the diagram in the form of cardinality captions, which are displayed above the relationship line. To display the captions, select Display cardinality captions from the Display menu.

captions for cardinality settings

Types of relationships in ER diagrams

Get started today and try Moon Modeler – ER diagram tool!

Download Read Quick Start Guides