Postgres in depth · Chapter 11 of 65
Instructors, courses, lessons
By the end of this chapter you can connect tables with foreign keys, choose what happens when a referenced row is deleted, and model a many-to-many relationship with a junction table. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Designing LCO properly
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Kabir wants instructor pages: a photo, a bio, a city, and every course the instructor teaches. Today courses.instructor is text, typed again for each course, so "Neha Kapoor" and "Neha Kapoor" (two spaces) are two different people, and there is nowhere to put Neha's bio. Courses also need sections and lessons, and Arjun needs to know which student bought which course.
These are relationships between things: an instructor has many courses, a course has many sections, a student has many courses and a course has many students. A table on its own cannot say any of that; a foreign key can.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
instructors.sql: instructors, and courses that point at them
Create instructors: id (bigint identity generated by default, the primary key), name (text, required), email (text, required, unique), bio and city (text, optional), joined_at (timestamptz, required).
Then replace chapter 10's courses (it is still empty) with the same table, except that the instructor text column becomes instructor_id: a bigint, required, that must match an instructor's id. An instructor who still has courses cannot be deleted. Keep chapter 10's other columns and rules (slug unique, the level check, courses_price_not_negative, the defaults).
- Task 2
sections.sql: sections and lessons
Create sections: id (bigint identity by default, primary key), course_id (bigint, required, a course's id), position (integer, required), title (text, required). A course never has two sections at the same position.
Create lessons: id (bigint identity by default, primary key), section_id (bigint, required, a section's id), position (integer, required), title (text, required), duration_seconds (integer, required, more than 0), is_free_preview (boolean, required, default false), transcript (text, optional). A section never has two lessons at the same position.
Deleting a course deletes its sections, and deleting a section deletes its lessons.
- Task 3
enrollments.sql: which student bought which course
Create enrollments, the junction table between students and courses: student_id (bigint, required, a student's id), course_id (bigint, required, a course's id), enrolled_at (timestamptz, required), price_paid (numeric(10, 2), required), completed_at (timestamptz, optional).
A student enrolls in a course at most once: the pair is the primary key. Deleting a student deletes their enrollments; a course that has enrollments cannot be deleted.
- Task 4 · interview question
Interview: categories.sql, a tree of categories
LCO's categories form a tree: Web Development, with Frontend under it, with React under that. Create categories: id (bigint identity by default, primary key), name (text, required, unique) and parent_id (bigint, optional): the id of the category it sits under, or NULL for a top-level category.
parent_id must be the id of an existing category, and a category that still has categories under it cannot be deleted.
What you will learn
- Foreign keys: a column that must match a row in another table.
- ON DELETE: refuse, cascade, or set to NULL when that row goes.
- One-to-many and many-to-many, and the junction table in between.
- Self-references: a table whose rows point at other rows of itself.
- Unindexed foreign keys: why deleting can be slow.
More in Designing LCO properly
Build "Instructors, courses, lessons" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.