There are 3 main types of database relationships in relational database design.
- Many-to-Many relationship
- One-to-Many (or Many-to-One) relationship
- 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
- Employee
- Duty_Shifts
Attributes of Employee
- Employee_id
- Employee_name
Attributes of Duty_Shifts
- Duty_shift_id
- Duty_shift
Relationship
Many “Employee” can work in many “Duty_Shifts”.
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.
- The first table is for the “Employee” entity.
- The second table is for a relationship that many “Employee” can “work _in” many “Duty_Shift”.
- The third table is for the “Duty_shift” entity.
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.
Download Slides
- Download PPT: One to Many Database relationship examples ppt slides presentation
- Download PDF: One to Many Database relationship examples ppt slides presentation
Video Lecture
Next Articles
- One-to-Many (or Many-to-One) relationship
- One-to-One relationship
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.