Modeling a MANY-TO-MANY Relationship Using a Join Table

A "many-to-many" relationship is when one record in one table can be linked to several records in another table, and vice versa. For example: - One student (from the students table) can be enrolled in several courses (from the courses table). - One course can have several students enrolled.

The problem is, storing this kind of relationship directly is a pain. That’s where a join table comes to the rescue, storing these connections for us.

Real Life Example

Imagine you’ve got a students table and a courses table. If you try to add a column in each table to store all the related data, you’ll end up with chaos:

• In the students table, you’d have to store a list of all courses a student is enrolled in. But how do you store a list? Comma-separated? As an array? That’s a nightmare for querying.
• In the courses table, you’d have to store a list of students, which is just a headache.

So the right move is to create a third table that stores the connections between students and courses.

The Join Table: Our Lifesaver!

The join table (sometimes called a link table) solves all the problems. It contains two foreign keys:

• A foreign key to the students table.
• A foreign key to the courses table.

Each row in this table creates a link between a specific student and a specific course.

Creating Tables for a "Many-to-Many" Relationship

Let’s get our hands dirty! Here’s how we can create tables for connecting students and courses:

Step 1: Create the students Table

This is our students table. Here we store unique student IDs and their names.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — this is the unique student identifier (auto-incremented, thanks to SERIAL!).
• name — the student’s name.

Step 2: Create the courses Table

Now let’s make the courses table. Here we store unique course IDs and their titles.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — unique course identifier.
• title — course title.

Step 3: Create the Join Table enrollments

Now let’s make our magic link table. It has two columns, each a foreign key pointing to the relevant table.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Let’s break down the structure:

• student_id — foreign key referencing student_id from the students table.
• course_id — foreign key referencing course_id from the courses table.
• PRIMARY KEY (student_id, course_id) — the primary key is a combo of both foreign keys. This makes sure every connection is unique.

Inserting Data

Let’s add some data to see how it all works.

Step 1: Add Students

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Result:

Step 2: Add Courses

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Result:

Step 3: Add Records to enrollments

Now let’s enroll students in courses. For example:

• Alice is enrolled in Mathematics and History.
• Bob is only enrolled in Biology.
• Charlie is enrolled in all three courses.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice in Mathematics
(1, 2), -- Alice in History
(2, 3), -- Bob in Biology
(3, 1), -- Charlie in Mathematics
(3, 2), -- Charlie in History
(3, 3); -- Charlie in Biology

Result:

Queries for "Many-to-Many" Relationships

Now that we’ve got some data, let’s put it to use!

How do you find all courses a student is enrolled in?

For example, to find out which courses Alice (ID = 1) is enrolled in, run this query:

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Result:

How do you find all students enrolled in a specific course?

Let’s say we want to know who’s enrolled in Mathematics (ID = 1):

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Result:

How do you find students and their courses?

To get the full picture of who’s enrolled in what, run this query:

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Result:

The enrollments table makes our schema super flexible — we can easily add or remove connections between students and courses without touching the main tables. Thanks to JOIN queries, it’s a breeze to find out who’s enrolled in what. And foreign keys automatically make sure there are no mistakes — like accidentally enrolling a student in a course that doesn’t even exist.

Common Mistakes in "Many-to-Many" Relationships

Missing unique constraint: If you don’t set a PRIMARY KEY, you might accidentally add the same connection more than once.

Breaking data integrity: Trying to insert a record with a non-existent student_id or course_id will throw an error.

Wrong order of deleting data: If you delete a course from courses first, the records in enrollments will be left as "orphans". To prevent this, use ON DELETE CASCADE in your foreign key definitions.