Many to Many relationship examples in Database

There are 3 main types of database relationships in relational database design.

  1. Many-to-Many relationship
  2. One-to-Many (or Many-to-One) relationship
  3. One-to-One relationship

Many-to-Many Relationship exists when many records in the 1st table having a relationship with many records in the 2nd table and similarly many records in the 2nd table having a relationship with many records in the 1st table.

Many-to-Many Relationship Examples

Let’s begin with an ERD design in which Many “Employee” can work in many “duty_shifts”.

Entities

  1. Employee
  2. Duty_Shifts

Attributes of Employee

  1. Employee_id
  2. Employee_name

Attributes of Duty_Shifts

  1. Duty_shift_id
  2. Duty_shift

Relationship

Many “Employee” can work in many  “Duty_Shifts”.

many to many relationship ERD in database systems
Figure: Entity Relationship Diagram of Many to Many Relationship

How to convert ERD into database tables and showing the M to M relationship?

Now, let’s convert this ERD Design to database tables.

As, we have two entities and one relationship, so we need to make three tables, two tables for two entities and one table for this relationship.

  1. The first table is for the “Employee” entity.
  2. The second table is for a relationship that many “Employee” can “work _in” many “Duty_Shift”.
  3. The third table is for the “Duty_shift” entity.

many to many relationship in DBMS

Non-Key Attributes: are the attributes without any key.

Primary-Key Attributes: are the attributes with the primary key and used for the unique identification of any row. Any value in the primary key attribute can’t be repeated again and again.

Foreign- Key Attributes are the attributes that set a foreign key. A foreign key is the primary key of one table that is linked with the field of another table.

Detailed information about keys is available in the keys tutorial.

Advantages of using the composite key in Many to Many relationships

In this example, we have set the primary key together(Composite Key) with two attributes “Employee_id” and “Duty_Shift_id” because the values of one of these attributes can’t be unique separately and values can repeat again and again. To overcome this issue, we have set the composite key on two attributes and after setting the composite key values will be unique for each row.

Advantages of using composite key in Many to Many relationship

Download Slides

Video Lecture

 

Next Articles

Many-to-Many relationship example in SQL | when to use a Many-to-Many relationship | Many-to-Many relationship diagram | Many to Many relationships in MySQL | Many to Many relationships in the SQL server | Many to Many relationships in XAMPP PHP Myadmin | Many-to-Many relationship ER diagram.

Foreign Key behaving differently on  Cascade strict and SET Null