Lesson 11: Relationship Modeling: Many-to-Many (M:N) and Junction Tables

The M:N relationship is the most complex to model directly, as it cannot exist naturally in the relational structure.

Defining a Many-to-Many Relationship

A M:N relationship means that one record in Table A can relate to many records in Table B, and one record in Table B can relate to many records in Table A.

Example: Students and Courses

• A Student can enroll in many Courses.
• A Course can be taken by many Students.

The Solution: The Junction Table

To resolve a M:N relationship, we introduce a third table called a Junction Table (or Association Table/Bridge Table). This table converts the M:N into two separate 1:N relationships.

Implementation Steps:

1. Identify the two main entities (Students and Courses).
2. Create a new junction table (Enrollments).
3. The junction table contains Foreign Keys pointing to the Primary Keys of BOTH original tables.

sql -- 1. Main Table (Students) CREATE TABLE Students ( StudentID INTEGER PRIMARY KEY, StudentName VARCHAR(100) );

-- 2. Main Table (Courses) CREATE TABLE Courses ( CourseID INTEGER PRIMARY KEY, CourseTitle VARCHAR(100) );

-- 3. Junction Table (Enrollments) CREATE TABLE Enrollments ( EnrollmentID INTEGER PRIMARY KEY, -- Optional PK for the link itself StudentID INTEGER, -- FK 1 (Many side of Student) CourseID INTEGER, -- FK 2 (Many side of Course) EnrollmentDate DATE,

FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Now, a student can have multiple entries in Enrollments, and a course can have multiple entries in Enrollments.