Skip to content Skip to main navigation Skip to footer

Resolving Many-to-Many Relationships

Many-to-many relationships occur when several records in a database table are linked with multiple records in another table. This article explains everything about many to many relationships.

Many-to-Many Relationships

Objectives

This article covers the following objectives:

  • Identify attributes which belong to many-to-many relationships
  • Demonstrate the steps to resolve a many to many relationship using an intersection entity
  • Identify the UID of an intersection entity and represent it in the entity relationship diagram

Purpose of the Many-to-Many Relationships

  • This lesson will help you complete your model – you may need to create new entities or new relationships based on the business needs.
  • It will also help you define the scope of your data model – you only model what is of importance to the business.

Relationship Hiding an Attribute

  • In a school, a STUDENT may study one or more SUBJECTs.
  • Each SUBJECT may be studied by one or more STUDENTs
  • When a student enrolls for a subject, we want to be able to record the grade they attain for that subject.
  • Which entity would the attribute “Grade” belong to?
  • If we put “Grade” in the STUDENT entity, how would we know which SUBJECT it is for?
  • If we put “Grade” in the SUBJECT entity, how would we know which STUDENT got that grade?
Relationship Hiding an Attribute

Resolution of a M:M Relationship

  • A third entity is needed to resolve the M:M relationship. This is called an “intersection” entity.
Resolution of a M:M Relationship

Since every attribute must belong to an entity, we need a third entity.

Intersection Entity

  • An intersection entity – ENROLLMENT – has been added, including the “Grade” attribute.
  • The original M:M relationship has become two 1:M relationships.
  • What would be the UID of the intersection entity?
Intersection Entity

Barred Relationships

  • The unique identifier (UID) of the intersection entity often comes from the originating relationships and is represented by the bars.
  • In this case, the relationships from the originating entities to the intersection entity are called “barred” relationships.
Barred Relationships

M:M Resolution Example TV Shows

  • Each TV show may be watched by one or more persons.
  • Each person may watch one or more TV shows.
M:M Resolution Example TV Shows

M:M Resolution Example Cleaning Services

  • Each company may provide one or more cleaning services.
  • Each cleaning service may be provided by one or more companies.
M:M Resolution Example Cleaning Services

Terminology

Key terms used in this tutorial included:

  • Barred relationship
  • Intersection entity

Summary

In this tutorial, you should have learned how to:

  • Identify attributes which belong to many-to-many relationships
  • Demonstrate the steps to resolve many-to-many relationships using an intersection entity
  • Identify the UID of an intersection entity and represent it in the entity relationship diagram

Was This Article Helpful?

-2
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *