Online Course Platform

Design the schema for an online course platform: instructors publish courses made of sections and lessons, students enroll, progress is tracked per lesson, quizzes are graded, and a certificate is issued on completion.

Clarifying Questions

Can a course be edited after students enroll, and do they see the new version?
Is progress only lesson completion, or also a video position for resume?
Are quizzes auto-graded multiple choice, or do some need manual review?
Do enrollments expire, or are they lifetime?
What counts as completion: every lesson, or every required lesson plus a passing quiz?

Core Requirements

Store courses with ordered sections and ordered lessons of different kinds (video, article, and quiz).
Enroll students and record how they got access.
Track per-lesson completion and the video resume position.
Store quiz questions, attempts, and scores.
Show course progress as a percentage on the student dashboard cheaply.
Issue a certificate that anyone can verify.

Canonical schema (PostgreSQL)

CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  instructor_id UUID NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft', 'published', 'archived')),
  required_lesson_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ
);

CREATE TABLE sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  position INTEGER NOT NULL,
  UNIQUE (course_id, position) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE lessons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  section_id UUID NOT NULL REFERENCES sections(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('video', 'article', 'quiz')),
  title TEXT NOT NULL,
  position INTEGER NOT NULL,
  duration_seconds INTEGER,
  is_required BOOLEAN NOT NULL DEFAULT true,
  content JSONB NOT NULL,
  UNIQUE (section_id, position) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE enrollments (
  id BIGSERIAL PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id),
  student_id UUID NOT NULL,
  source TEXT NOT NULL CHECK (source IN ('purchase', 'subscription', 'grant')),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ,
  completed_required INTEGER NOT NULL DEFAULT 0,
  completed_at TIMESTAMPTZ,
  UNIQUE (course_id, student_id)
);

CREATE TABLE lesson_progress (
  enrollment_id BIGINT NOT NULL REFERENCES enrollments(id) ON DELETE CASCADE,
  lesson_id UUID NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  status TEXT NOT NULL CHECK (status IN ('started', 'completed')),
  position_seconds INTEGER NOT NULL DEFAULT 0,
  completed_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (enrollment_id, lesson_id)
);

CREATE TABLE quiz_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lesson_id UUID NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  position INTEGER NOT NULL,
  prompt TEXT NOT NULL,
  choices JSONB NOT NULL,
  correct_choice SMALLINT NOT NULL,
  points SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE quiz_attempts (
  id BIGSERIAL PRIMARY KEY,
  enrollment_id BIGINT NOT NULL REFERENCES enrollments(id) ON DELETE CASCADE,
  lesson_id UUID NOT NULL REFERENCES lessons(id),
  answers JSONB NOT NULL,
  score SMALLINT NOT NULL,
  max_score SMALLINT NOT NULL,
  passed BOOLEAN NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE certificates (
  id BIGSERIAL PRIMARY KEY,
  enrollment_id BIGINT UNIQUE NOT NULL REFERENCES enrollments(id),
  verification_code TEXT UNIQUE NOT NULL,
  issued_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Key Indexes

CREATE INDEX idx_enrollments_student ON enrollments(student_id, enrolled_at DESC)

The student dashboard lists their courses, most recent first.

CREATE INDEX idx_lessons_course_outline ON lessons(course_id, section_id, position)

Loads a course outline in display order with one range scan.

CREATE INDEX idx_progress_lesson_completed ON lesson_progress(lesson_id) WHERE status = 'completed'

Instructor analytics count completions per lesson to find where students stop.

CREATE INDEX idx_attempts_enrollment_lesson ON quiz_attempts(enrollment_id, lesson_id, submitted_at DESC)

Finds a student's latest or best attempt on a quiz.

CREATE INDEX idx_enrollments_course ON enrollments(course_id, enrolled_at)

The instructor roster and enrollment trend for one course.

Key Decisions to Defend

Sections and lessons use integer positions with deferred unique constraints; a reorder rewrites a few dozen rows inside one transaction, so simple integers are enough.
Progress is keyed by enrollment, so each course a student takes keeps its own progress and removing an enrollment cleans up in one place.
enrollments.completed_required is a counter updated with each required lesson completion, so the dashboard divides by courses.required_lesson_count instead of counting rows.
Quiz attempts keep the submitted answers as JSONB with the score, so a corrected question can be regraded.
Certificates carry a random verification_code for a public verify page, and the sequential id stays internal.

Likely follow-ups

· An instructor deletes a lesson that 10,000 students completed, what happens to their progress and certificates?
· Add course versioning so enrolled students keep the curriculum they started.
· Show instructors the lesson where most students stop.
· Support cohorts with fixed start dates and deadlines.

Dive Deeper

Course platforms test ordered content, per-user progress, and derived state. Interviewers look for progress keyed to the enrollment, a cheap progress percentage, and a plan for edits after students have started. The versioning follow-up is the real test: editing a live course changes what complete means, and strong candidates decide explicitly whether enrolled students see the new curriculum.

All Database Design Prompts