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.

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:
DOCTORtoPATIENT: A doctor treats zero or more patients ().PATIENTtoDOCTOR: A patient is treated by one or more doctors ().- The forward relationship is named Treats.
- The reverse relationship is named Treated by.

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
DOCTORdata: D1|Joseph Fixit|heartD2|Sue Sewitup|orthopedicD3|Mac Quack|internist- Sample
PATIENTdata: P1|Mary|...|25P2|George|...|34P3|Harry|...|76P4|Sophie|...|44P5|Lamisha|...|31- Relationship instances:
D1treatsP2and `P3````D2treatsP1,P3, andP5D3treatsP2,P4, andP5- Storing patient IDs directly in the
DOCTORtable creates multi-valued columns (PatID:P2, P3forD1), which is invalid in relational database design.
- Sample

- Failure Mode 2: Adding Foreign Keys to the PATIENT Table:
- Storing doctor IDs directly in the
PATIENTtable similarly creates multi-valued columns (DocID:D1, D3forP2;D1, D2forP3;D2, D3forP5), which is invalid.
- Storing doctor IDs directly in the

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````

- Complete Relational Model Structure:
DOCTORtable contains doctor-specific non-key attributes (DocName,Specialty).PATIENTtable contains patient-specific non-key attributes (PatName,Addr,Age).DOCTOR-PATIENTtable linksDocIDandPatID.

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_IDandpatient_IDindividually serve as Foreign Keys (FK) referencing the Primary Keys of theDOCTORandPATIENTtables, respectively.
- In MySQL, the associative entity table (e.g.,

- 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.

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.

- Associative Entity Requirements:
- Name:
STUDENT-CLASSorSTUDENT_CLASS. - Primary Key: Composite key
(StudentID, CRN). - Foreign Keys:
StudentIDreferencingSTUDENT,CRNreferencingCLASS. - Additional Attribute (Payload): Include
graderepresenting the grade the student earned in that specific class.
- Name: