Convert a Many-to-Many Relationship into Two One-to-Many Relationships

Read the Resolved Diagram in Both Directions

• Student → Enrollment: one student has zero, one or many enrollment rows. Each enrollment belongs to exactly one student.
• Class → Enrollment: one class has zero, one or many enrollment rows. Each enrollment belongs to exactly one class.

A student with no classes is represented by a Student row and no Enrollment rows, not an Enrollment row with a NULL ClassID. Required foreign keys enforce the references in existing enrollment rows; they do not force every parent to have a child row.

Create the Three Tables

The following SQL uses explicit identifiers rather than vendor-specific automatic numbering. Run it in an empty practice schema. Grade is nullable because a new enrollment may not yet have a grade.

CREATE TABLE Student (
    StudentID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(60) NOT NULL,
    FirstName VARCHAR(60) NOT NULL
);

CREATE TABLE Class (
    ClassID INT NOT NULL PRIMARY KEY,
    ClassName VARCHAR(120) NOT NULL,
    ClassDept VARCHAR(100)
);

CREATE TABLE Enrollment (
    StudentID INT NOT NULL,
    ClassID INT NOT NULL,
    EnrolledOn DATE NOT NULL,
    Grade VARCHAR(5),
    CONSTRAINT pk_enrollment PRIMARY KEY (StudentID, ClassID),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    CONSTRAINT fk_enrollment_class
        FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);

CREATE INDEX ix_enrollment_class ON Enrollment(ClassID);

The primary key prevents a duplicate pair. It does not make StudentID individually unique or ClassID individually unique. Repeated individual identifiers are essential to representing many-to-many associations.

The example does not request cascading deletes. With foreign-key enforcement enabled, a referenced parent cannot simply be deleted while its enrollments remain. Choose retention and deletion policies deliberately; deleting an enrollment itself does not delete its parents.

Many database engines support the composite primary key with an index whose leading column is StudentID. In such an engine, another index on StudentID alone may be unnecessary. The ClassID index supports lookups from the other direction; inspect your engine's indexes and query plans before adding more.

Insert and Read the Pairings

INSERT INTO Student (StudentID, LastName, FirstName) VALUES
    (53985, 'Jones', 'Cindy'),
    (62292, 'Cruz', 'Leo');

INSERT INTO Class (ClassID, ClassName, ClassDept) VALUES
    (854, 'Finance', 'Business'),
    (768, 'Statistics', 'Math');

INSERT INTO Enrollment (StudentID, ClassID, EnrolledOn, Grade) VALUES
    (53985, 854, '2026-09-01', 'A'),
    (53985, 768, '2026-09-01', 'B'),
    (62292, 854, '2026-09-02', 'A');

SELECT s.FirstName, s.LastName, c.ClassName, e.Grade
FROM Enrollment AS e
JOIN Student AS s ON s.StudentID = e.StudentID
JOIN Class AS c ON c.ClassID = e.ClassID
ORDER BY s.StudentID, c.ClassID;

This is a three-row subset of the earlier lessons' enrollment example, with illustrative dates and grades added. Cindy appears twice in the report because she has two enrollments. Her name is still stored once in Student. Likewise, Finance is stored once in Class even though two students take it.

When a Surrogate Key Is Useful

CREATE TABLE Enrollment (
    EnrollmentID INT NOT NULL PRIMARY KEY,
    StudentID INT NOT NULL,
    ClassID INT NOT NULL,
    EnrolledOn DATE NOT NULL,
    Grade VARCHAR(5),
    CONSTRAINT uq_enrollment_pair UNIQUE (StudentID, ClassID),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    CONSTRAINT fk_enrollment_class
        FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);

Keep Additional Rules Separate

• Minimum participation: a foreign key does not enforce “every class must have at least three students.” Such rules need an appropriate workflow, transaction or database mechanism, often checked when a class is confirmed rather than while it is being created.
• Maximum participation: a simple row-level CHECK does not count other enrollment rows. A capacity rule needs a design that remains correct when concurrent transactions enroll students.
• History: a recorded grade belongs to a specific enrollment or attempt. An order's agreed UnitPriceAtOrder is similarly a historical association fact, distinct from a product's current list price.
• Other domains: OrderItem links orders and products; EmployeeProject links employees and projects. Determine whether repeated product lines or assignment periods require a different association key.

Verify the Design Before Moving On

• Accept the same student with another class and the same class with another student.
• Reject a second copy of an existing student/class pair.
• Reject missing parent references and NULL values in either required foreign key.
• Allow a student or class to exist without enrollments under the stated optional-participation rule.
• Join to retrieve parent names, and keep enrollment-specific attributes with the pair.

A composite primary key^[3] identifies the pairing; foreign keys validate its members. Neither constraint alone expresses every business rule. The next lesson develops mandatory and optional participation.

Resolving Many-to-Many Relationships - Exercise