Capstone: designing a course platform

This is where it all comes together. Up to this point we have been building one database: a book review platform with users, authors, books, tags, profiles, and reviews. That schema has carried every lesson for the last seven sections. The point of a capstone, though, is to prove that you can pick up the same toolkit and apply it to something you have never modeled before. So we are going to leave the books platform alone and design a completely new database from scratch: a course platform, like the one hosting this course. Users sign up, browse courses, enroll, complete lessons, leave reviews, and track their progress. It sounds small when you say it in a sentence, but it exercises every pattern you have learned: one-to-many, many-to-many, junction tables with extra data, normalization, denormalization, indexes, full-text search, and of course migrations. And because you have been writing numbered migration files since Section 3, we will encode every part of this schema as a migration, one feature area at a time, just like you would in a real project.

Step 1: identify the entities

Before writing any SQL, list the nouns. These are the things your system needs to track:

• Users who take courses
• Courses which are collections of lessons
• Sections which group lessons within a course (like “Relationships” or “Performance”)
• Lessons which are individual pages of content
• Enrollments linking users to courses
• Progress tracking which lessons a user has completed
• Reviews where users rate and review a course
• Tags describing topics that courses cover

Step 2: map the relationships

User ──< Enrollment >── Course
User ──< Progress >── Lesson
User ──< Review >── Course
Course ──< Section ──< Lesson
Course ──< CourseTag >── Tag

──< means one-to-many. >── means the junction table connects two sides of a many-to-many.

A user can enroll in many courses, and a course can have many users. That is many-to-many, via the enrollments junction table. A user can complete many lessons, and a lesson can be completed by many users. Many-to-many again, via the progress table. A course has many sections, and each section has many lessons. That is a chain of one-to-many relationships. A course can have many tags, and a tag can belong to many courses. Many-to-many, via course_tags.

Step 3: plan the migrations

Here is the full schema. We will look at it all in one place first, because it is easier to see the shape of the data that way. But in practice you would split this into several migrations, one per feature area, like this:

• 001_create_users.sql
• 002_create_courses_and_sections.sql
• 003_create_lessons.sql
• 004_create_tags_and_course_tags.sql
• 005_create_enrollments.sql
• 006_create_progress.sql
• 007_create_reviews.sql
• 008_add_indexes.sql
• 009_add_denormalized_columns_and_triggers.sql
• 010_add_courses_fts.sql

Each file is a numbered migration, wrapped in a transaction, and recorded in schema_migrations after it runs. Same pattern you have been using all course. Here is the whole schema:

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE courses (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  difficulty TEXT NOT NULL DEFAULT 'beginner',
  duration TEXT,
  is_published INTEGER NOT NULL DEFAULT 0,
  lesson_count INTEGER NOT NULL DEFAULT 0,       -- denormalized
  avg_rating REAL,                                -- denormalized
  review_count INTEGER NOT NULL DEFAULT 0,        -- denormalized
  enrollment_count INTEGER NOT NULL DEFAULT 0,    -- denormalized
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE sections (
  id TEXT PRIMARY KEY,
  course_id TEXT NOT NULL,
  title TEXT NOT NULL,
  position INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE lessons (
  id TEXT PRIMARY KEY,
  section_id TEXT NOT NULL,
  title TEXT NOT NULL,
  slug TEXT NOT NULL,
  description TEXT,
  position INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE CASCADE
);

CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE course_tags (
  course_id TEXT NOT NULL,
  tag_id TEXT NOT NULL,
  PRIMARY KEY (course_id, tag_id),
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE TABLE enrollments (
  user_id TEXT NOT NULL,
  course_id TEXT NOT NULL,
  enrolled_at TEXT NOT NULL DEFAULT (datetime('now')),
  completed_at TEXT,
  PRIMARY KEY (user_id, course_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE progress (
  user_id TEXT NOT NULL,
  lesson_id TEXT NOT NULL,
  completed_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (user_id, lesson_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

CREATE TABLE reviews (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  course_id TEXT NOT NULL,
  rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
  body TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  UNIQUE (user_id, course_id)  -- one review per user per course
);

Notice the design decisions. TEXT primary keys (UUIDs) for all resources. NOT NULL on everything that must exist. DEFAULT values where appropriate. CHECK on rating to enforce the 1 to 5 range. UNIQUE (user_id, course_id) on reviews to prevent duplicate reviews. Composite primary keys on junction tables. ON DELETE CASCADE so deleting a course cleans up its sections, lessons, enrollments, and reviews.

In a real project you would split that schema into the seven migrations listed above, one per feature area, each one wrapped in BEGIN / COMMIT and recording itself in schema_migrations at the end. Migrations 001 through 007 are straightforward CREATE TABLE migrations that look exactly like the ones you have already written earlier in the course, so we will not rewrite them here line by line. The interesting migrations are the last three, because they bring together the more advanced topics (indexes, triggers, and full-text search) on top of the schema above. Those are the ones we will walk through in detail in the next three steps.

Step 4: add indexes

Tables 001 through 007 get the data shape right. The next migration adds indexes, because without them the catalog page would fall over as soon as you have real traffic. Here is 008_add_indexes.sql:

-- 008_add_indexes.sql
BEGIN;

-- Foreign keys (not automatically indexed in SQLite)
CREATE INDEX idx_sections_course_id ON sections(course_id);
CREATE INDEX idx_lessons_section_id ON lessons(section_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);
CREATE INDEX idx_progress_lesson_id ON progress(lesson_id);
CREATE INDEX idx_reviews_course_id ON reviews(course_id);
CREATE INDEX idx_reviews_user_id ON reviews(user_id);

-- Ordering (composite indexes for sorted queries)
CREATE INDEX idx_sections_position ON sections(course_id, position);
CREATE INDEX idx_lessons_position ON lessons(section_id, position);

-- Filtering
CREATE INDEX idx_courses_published ON courses(is_published);
CREATE INDEX idx_courses_difficulty ON courses(difficulty);

INSERT INTO schema_migrations (version, name) VALUES ('008', 'add_indexes');
COMMIT;

Every foreign key is indexed. Ordering columns have composite indexes for efficient sorted queries. The most common filters have indexes too. Notice the migration is wrapped in a transaction and records itself in schema_migrations at the end, with both version and name, exactly like every other migration you have written.

Step 5: denormalized columns and triggers

The courses table already has four denormalized columns baked into 002_create_courses_and_sections.sql: lesson_count, avg_rating, review_count, and enrollment_count. Without these, every course listing would need joins and aggregations to compute counts and averages. With them, the catalog page is a simple SELECT.

Those columns are not useful on their own. They need triggers to stay in sync. That is 009_add_denormalized_columns_and_triggers.sql:

-- 009_add_denormalized_columns_and_triggers.sql
BEGIN;

CREATE TRIGGER enrollment_count_insert AFTER INSERT ON enrollments BEGIN
  UPDATE courses SET enrollment_count = enrollment_count + 1 WHERE id = NEW.course_id;
END;

CREATE TRIGGER enrollment_count_delete AFTER DELETE ON enrollments BEGIN
  UPDATE courses SET enrollment_count = enrollment_count - 1 WHERE id = OLD.course_id;
END;

CREATE TRIGGER review_stats_insert AFTER INSERT ON reviews BEGIN
  UPDATE courses SET
    review_count = review_count + 1,
    avg_rating = (SELECT AVG(rating) FROM reviews WHERE course_id = NEW.course_id)
  WHERE id = NEW.course_id;
END;

CREATE TRIGGER review_stats_delete AFTER DELETE ON reviews BEGIN
  UPDATE courses SET
    review_count = review_count - 1,
    avg_rating = (SELECT AVG(rating) FROM reviews WHERE course_id = OLD.course_id)
  WHERE id = OLD.course_id;
END;

INSERT INTO schema_migrations (version, name) VALUES ('009', 'add_denormalized_columns_and_triggers');
COMMIT;

One migration. Four triggers. The counts stay correct no matter who inserts or deletes what.

Step 6: add full-text search

Course search is the last piece. We could use LIKE '%query%', but you know by now that will scan every row and ignore every index. FTS5 is the right answer. Here is 010_add_courses_fts.sql:

-- 010_add_courses_fts.sql
BEGIN;

CREATE VIRTUAL TABLE courses_fts USING fts5(
  title, description, content=courses
);

INSERT INTO schema_migrations (version, name) VALUES ('010', 'add_courses_fts');
COMMIT;

That is the schema. Ten migrations, each one small enough to review, each one tracked, each one reversible if you really need to roll back.

Step 7: common queries

Now that the schema is in place, here are the queries the application will actually run:

-- Course catalog (published courses with stats)
SELECT id, title, description, difficulty, duration,
       lesson_count, avg_rating, review_count, enrollment_count
FROM courses
WHERE is_published = 1
ORDER BY enrollment_count DESC;

-- Course detail with sections and lessons
SELECT s.title AS section_title, l.title AS lesson_title, l.id AS lesson_id
FROM sections s
JOIN lessons l ON l.section_id = s.id
WHERE s.course_id = ?
ORDER BY s.position, l.position;

-- User's enrolled courses with progress
WITH course_progress AS (
  SELECT
    e.course_id,
    COUNT(p.lesson_id) AS completed_lessons
  FROM enrollments e
  LEFT JOIN progress p ON p.user_id = e.user_id
    AND p.lesson_id IN (
      SELECT l.id FROM lessons l
      JOIN sections s ON l.section_id = s.id
      WHERE s.course_id = e.course_id
    )
  WHERE e.user_id = ?
  GROUP BY e.course_id
)
SELECT c.title, c.lesson_count, cp.completed_lessons,
       ROUND(100.0 * cp.completed_lessons / c.lesson_count) AS percent_complete
FROM course_progress cp
JOIN courses c ON c.id = cp.course_id;

-- Search courses
SELECT c.*
FROM courses_fts
JOIN courses c ON c.rowid = courses_fts.rowid
WHERE courses_fts MATCH ?
AND c.is_published = 1
ORDER BY courses_fts.rank;

The catalog query is a single SELECT with no joins. The course detail query joins two tables, both on indexed foreign keys. The progress query uses a CTE for clarity. The search query goes through FTS5. Every one of these queries is fast, and they are fast because of decisions encoded in the migrations above.

Design decisions mapped to lessons

Every decision in this schema connects back to something we covered:

Challenges

Challenge 1: Add course prerequisites. A course can require completion of other courses before enrollment. Model this relationship, and write it as migration 011_add_course_prerequisites.sql. (Hint: self-referencing many-to-many on the courses table.)

Challenge 2: Add quiz scores. Lessons can have quizzes. Users submit answers and get scores. Track attempts, scores, and best scores. What tables do you need, and how would you split them across migrations?

Challenge 3: Add an admin dashboard. Write queries for: most popular courses (by enrollment), highest rated courses, courses with no reviews, users who enrolled but never started, and average completion rate per course.

Challenge 4: Add bookmarks. Users can bookmark lessons to come back to later. Model the table. Should it have extra data? (Think about when the bookmark was created.) Write it as its own migration.

That is the whole course. You have covered every pattern needed to design, build, and maintain a database for a real application. Tables, relationships, keys, normalization, indexes, pragmas, full-text search, and a migration workflow that keeps every change versioned and safe. Take what you have learned and apply it to your own projects. The next schema you design will be better for it.