In a relational database, a many-to-many relationship exists when multiple rows in one table are related to multiple rows in another table. A classic example is students and courses — a student can enroll in many courses, and a course can have many students enrolled in it.

The Problem with Direct Linking

You cannot directly link two tables in a many-to-many relationship using a single foreign key column. Adding a course_id column to the student table only allows one course per student, and adding a student_id column to the course table only allows one student per course. Neither works.

The Solution: A Junction Table

The standard approach is to introduce a third table — commonly called a junction table or association table — that holds foreign keys from both sides of the relationship. Each row in this table represents one enrollment: one student in one course.

Creating the Tables

CREATE TABLE student (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    email   VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE course (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    description TEXT
);

CREATE TABLE enrollment (
    student_id  INT NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

Key points about the enrollment table:

• The composite primary key (student_id, course_id) ensures a student cannot enroll in the same course twice.
• ON DELETE CASCADE means that if a student or course is deleted, their enrollment records are automatically removed.
• enrolled_at is a useful metadata column to track when the enrollment happened.

Querying Students Enrolled in a Course

To fetch all students enrolled in a given course, join enrollment with student and filter by course_id.

SELECT
    s.id          AS student_id,
    s.name        AS student_name,
    s.email,
    e.enrolled_at
FROM enrollment e
INNER JOIN student s ON s.id = e.student_id
WHERE e.course_id = 2
ORDER BY s.name;

Querying Courses Enrolled by a Student

To fetch all courses a given student is enrolled in, join enrollment with course and filter by student_id.

SELECT
    c.id          AS course_id,
    c.title       AS course_title,
    c.description,
    e.enrolled_at
FROM enrollment e
INNER JOIN course c ON c.id = e.course_id
WHERE e.student_id = 1
ORDER BY c.title;

The junction table pattern is the standard way to model many-to-many relationships in any RDBMS. It keeps your schema normalized and makes both query directions equally straightforward with a simple JOIN.

Hope this helps!