Many-to-Many Relationships in MySQL and Relational Database Design

Fundamentals of Many-to-Many (N:M) Relationships

  • Definition of Many-to-Many Relationship: A Many-to-Many (N:M) relationship occurs when multiple records in one database table are associated with multiple records in another database table.
  • Record Mapping Visualized:
    • In an N:M structure, an individual record on the left can link to zero, one, or multiple records on the right.
    • Concurrently, an individual record on the right can link back to zero, one, or multiple records on the left.

Diagram illustrating connections between multiple records in two sets

Case Study: Doctors and Patients System

  • Problem Statement:

    • A hospital system manages information about doctors and patients.
    • A hospital has many doctors who treat many patients.
    • A doctor may treat several patients, or may not have any patients at all (e.g., an administrative doctor).
    • A patient always has one doctor but may be treated by a team of doctors (one or more doctors).
    • Attributes collected for each doctor:
    • Doc_ID (Doctor Identifier)
    • Doc_Name (Doctor Name)
    • Doc_specialty (Doctor Specialty)
    • Attributes collected for each patient:
    • Pat_ID (Patient Identifier)
    • Pat_Name (Patient Name)
    • Pat_addr (Patient Address)
    • Pat_age (Patient Age)
  • Entity Identification Process:

    • To determine candidate entities from a problem scenario, underline all nouns.
    • Apply the selection rule: Is there more than one of them?
    • Hospital: Only one hospital exists in the scenario context; therefore, "Hospital" does not become an entity.
    • Doctor: Multiple doctors exist; therefore, "Doctor" becomes an entity.
    • Patient: Multiple patients exist; therefore, "Patient" becomes an entity.
  • Cardinality and Relationship Name:

    • DOCTOR to PATIENT: A doctor treats zero or more patients (0..N0..N).
    • PATIENT to DOCTOR: A patient is treated by one or more doctors (1..N1..N).
    • The forward relationship is named Treats.
    • The reverse relationship is named Treated by.

Crow's foot ERD showing Many-to-Many relationship between DOCTOR and PATIENT

Resolving Many-to-Many Relationships with Associative Entities

  • Relational Model Limitation:

    • Relational database management systems (RDBMS) cannot directly implement or store a Many-to-Many relationship.
    • Attempting to store array values or comma-separated lists in a single field violates First Normal Form (1NF).
  • Failure Mode 1: Adding Foreign Keys to the DOCTOR Table:

    • Sample DOCTOR data:
    • D1 | Joseph Fixit | heart
    • D2 | Sue Sewitup | orthopedic
    • D3 | Mac Quack | internist
    • Sample PATIENT data:
    • P1 | Mary | ... | 25
    • P2 | George | ... | 34
    • P3 | Harry | ... | 76
    • P4 | Sophie | ... | 44
    • P5 | Lamisha | ... | 31
    • Relationship instances:
    • D1 treats P2 and `P3````
    • D2 treats P1, P3, and P5
    • D3 treats P2, P4, and P5
    • Storing patient IDs directly in the DOCTOR table creates multi-valued columns (PatID: P2, P3 for D1), which is invalid in relational database design.

DOCTOR table invalidly storing multiple PatIDs in a single cell

  • Failure Mode 2: Adding Foreign Keys to the PATIENT Table:
    • Storing doctor IDs directly in the PATIENT table similarly creates multi-valued columns (DocID: D1, D3 for P2; D1, D2 for P3; D2, D3 for P5), which is invalid.

PATIENT table invalidly storing multiple DocIDs in a single cell

  • The Associative Entity Solution:

    • Create an intermediate bridging table termed an Associative Entity, Associative Table, or Joining Table.
    • The associative table decomposes the N:M relationship into two separate One-to-Many (1:N) relationships.
    • Each record in the associative table pairs exactly one doctor with one patient.
  • Data Representation in the Associative Table (DOCTOR-PATIENT):

    • DocID: D1 | PatID: `P2````
    • DocID: D1 | PatID: `P3````
    • DocID: D2 | PatID: `P1````
    • DocID: D2 | PatID: `P3````
    • DocID: D2 | PatID: `P5````
    • DocID: D3 | PatID: `P2````
    • DocID: D3 | PatID: `P4````
    • DocID: D3 | PatID: `P5````

Associative table listing distinct pairs of DocID and PatID

  • Complete Relational Model Structure:
    • DOCTOR table contains doctor-specific non-key attributes (DocName, Specialty).
    • PATIENT table contains patient-specific non-key attributes (PatName, Addr, Age).
    • DOCTOR-PATIENT table links DocID and PatID.

Completed tables and ERD diagram for Doctor-Patient relationship

Implementing N:M Relationships in MySQL

  • Primary and Foreign Key Configuration:
    • In MySQL, the associative entity table (e.g., DOCTOR_PATIENT) uses a Composite Primary Key consisting of (doc_ID, patient_ID).
    • Both doc_ID and patient_ID individually serve as Foreign Keys (FK) referencing the Primary Keys of the DOCTOR and PATIENT tables, respectively.

MySQL Workbench ERD showing DOCTOR, DOCTOR_PATIENT, and PATIENT tables

  • Identifying Relationships:
    • The relationships connecting the parent entities to the associative entity are identifying relationships.
    • An identifying relationship indicates that the Foreign Key attributes in the associative table are part of its own Primary Key.
    • In MySQL Workbench ER diagrams, an identifying relationship is represented visually by a solid line rather than a dashed line.

MySQL Workbench showing solid lines for identifying relationships

Practice Exercise: The Quidditch Problem

  • Scenario Details:
    • Students take many classes (zero or more).
    • Each class has many students (one or more).
    • Parent Entities and Primary/Non-Key Attributes:
    • STUDENT: StudentID (PK), First, `Last````
    • CLASS: CRN (PK), Classname, `Term````
    • Relationship names: takes / Taken by.

Initial ERD for Student and Class Many-to-Many relationship

  • Associative Entity Requirements:
    • Name: STUDENT-CLASS or STUDENT_CLASS.
    • Primary Key: Composite key (StudentID, CRN).
    • Foreign Keys: StudentID referencing STUDENT, CRN referencing CLASS.
    • Additional Attribute (Payload): Include grade representing the grade the student earned in that specific class.