Capstone: Design a Course Platform Database

Build a small course platform schema with users, courses, enrollments, lessons, and progress. Define primary and foreign keys, constraints, useful timestamps, and indexes around expected access patterns. Think in terms of entities and relationships first, then write queries that answer product questions such as most-enrolled courses or a user's course progress.

A useful capstone includes schema creation, seed data, read queries, updates inside transactions, a migration, and a performance review using EXPLAIN. The goal is not merely to make SQL run; it is to make the data model enforce the domain's important invariants.

Core capstone schema

CREATE TABLE courses (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE enrollments (
  user_id BIGINT NOT NULL REFERENCES users(id),
  course_id BIGINT NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, course_id)
);

CREATE INDEX idx_enrollments_course
ON enrollments (course_id);

💡 This excerpt assumes a users table already exists. The composite primary key prevents duplicate enrollments, while the course index can help course-centric lookups and counts.

Key Takeaways

Model entities and relationships before writing queries.
Use constraints to encode important invariants.
Index based on expected access patterns and verify with plans.

Knowledge Check

Which key prevents the same user from enrolling in the same course more than once?

Capstone: Design a Course Platform Database

STAY UPDATED WITH BYTES!