Creating the enrollments table to link students and courses

Let's nerd out a bit more. Let's walk through the steps of building a MANY-TO-MANY structure in detail. Ready?

A MANY-TO-MANY relationship between students and courses can't be represented directly in a single table. One student can enroll in several courses, and one course can have several students attending at the same time.

To solve this, we create an intermediate table called enrollments, which will store info about enrollments, i.e., which student-course pairs exist. This not only keeps our data consistent, but also makes it easy to add more features, like enrollment date.

What does the enrollments table look like?

You already know that the enrollments table is the central link between the students and courses tables. Here's its structure:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Unique record ID
    student_id INT REFERENCES students(student_id), -- Foreign key to students table
    course_id INT REFERENCES courses(course_id),    -- Foreign key to courses table
    enrollment_date DATE DEFAULT CURRENT_DATE   -- When the student enrolled in the course
);

Let's break down each line:

• enrollment_id: This is the unique identifier for each record. Every student enrolled in a course should have a unique ID.
• student_id: Shows which student is enrolled. This is a foreign key that references the students table (student_id column).
• course_id: Shows which course the student is enrolled in. This column is linked to the courses table (course_id column).
• enrollment_date: A handy extra that shows the enrollment date. We use DEFAULT CURRENT_DATE to automatically set the current date when a record is created.

Creating the students and courses tables

Before we go further, let's make sure we already have the students and courses tables:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Unique student identifier
    name TEXT NOT NULL,                 -- Student name
    email TEXT NOT NULL UNIQUE          -- Student email, to avoid duplicates
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Unique course identifier
    title TEXT NOT NULL,                -- Course title
    description TEXT,                   -- Course description
    start_date DATE                     -- Course start date
);

Notice that we've added some useful details here, like unique emails for students and a course description in the courses table.

Linking it all together

Now that our tables are ready, let's create the enrollments table:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Unique enrollment ID
    student_id INT NOT NULL REFERENCES students(student_id), -- Foreign key
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Foreign key
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Enrollment date
);

Inserting data into the tables

Alright, the tables are ready, but they're bored without data. Let's add some students, courses, and their enrollments:

Insert students:

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

Insert courses:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Programming Basics', 'Course for beginner programmers.', '2023-11-01'),
    ('Databases', 'Learning SQL and relational databases.', '2023-11-15'),
    ('Web Development', 'Building websites and web apps.', '2023-12-01');

Insert enrollment records:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin in "Programming Basics"
    (1, 2), -- Alex Lin in "Databases"
    (2, 2), -- Maria Chi in "Databases"
    (3, 3); -- Otto Song in "Web Development"

Here, student_id and course_id match the IDs in the corresponding tables.

Checking relationships with queries

Get all enrollments:

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

Result:

Practice task for you

Try adding a few more students and courses, then enroll them in the enrollments table. For example, add a course called "Machine Learning" and enroll 1-2 students in it. Use the query above to check your results.

Possible common mistakes

When working with foreign keys and intermediate tables, there are a few gotchas people often run into:

1. Missing record in the parent table: If you try to add a record to enrollments with a student_id or course_id that doesn't exist in the students or courses tables, you'll get an error. The foreign key keeps a close eye on this.

2. Breaking data integrity on delete: If you delete a student or course that's already used in the enrollments table, and you haven't set up ON DELETE CASCADE, you'll get an error.

3. Duplicate records: Make sure you don't enroll a student in the same course more than once, unless that's what your business logic wants.

Now you've got a working model for representing a MANY-TO-MANY relationship between students and courses in PostgreSQL. This structure is super common in real-world apps, like learning management systems, CRMs, and a bunch of others. On to the next lecture!

2

Task

SQL SELF, level 20, lesson 1

Locked

Creating the `enrollments` table

Creating the `enrollments` table

Open

2

Task

SQL SELF, level 20, lesson 1

Locked

Displaying Combined Enrollment Data

Displaying Combined Enrollment Data

Open