🗄️ Database Schema Designer

Schema Design: Online Course Platform, PostgreSQL, multi-tenant

A database schema design is a normalized-yet-practical structure for the core entities + queries, calibrated to the expected scale. Based on 100+ production schemas, at your 18-month scale (5M progress records, 100k users), PostgreSQL with standard normalization + indexing will perform well without sharding, caching, or NoSQL. Your multi-tenant + GDPR constraints shape the schema more than scale does — those require specific patterns.

Entity Model

• Organization (tenant) → has many → Users (students + instructors)
• Course belongs to Organization, belongs to instructor User
• Lesson belongs to Course
• Enrollment = (Student User, Course) + progress
• Progress = (Enrollment, Lesson) — can be its own table, or embedded
• Quiz belongs to Lesson; QuizAttempt = (User, Quiz)
• Certificate = (User, Course)

Schema (PostgreSQL DDL)

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  email VARCHAR(255) NOT NULL,
  role VARCHAR(32) NOT NULL CHECK (role IN ('student','instructor','admin')),
  name VARCHAR(255),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ,
  UNIQUE (organization_id, email)
);

CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  instructor_id UUID NOT NULL REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE lessons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID NOT NULL REFERENCES courses(id),
  title VARCHAR(255) NOT NULL,
  content_url TEXT,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ,
  UNIQUE (course_id, order_index)
);

CREATE TABLE enrollments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID NOT NULL REFERENCES users(id),
  course_id UUID NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  -- Denormalized count for fast progress display
  lessons_completed_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (student_id, course_id)
);

CREATE TABLE progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  enrollment_id UUID NOT NULL REFERENCES enrollments(id),
  lesson_id UUID NOT NULL REFERENCES lessons(id),
  completed_at TIMESTAMPTZ,
  watch_seconds INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (enrollment_id, lesson_id)
);

CREATE TABLE quizzes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lesson_id UUID NOT NULL REFERENCES lessons(id),
  title VARCHAR(255) NOT NULL,
  pass_threshold INTEGER NOT NULL CHECK (pass_threshold BETWEEN 0 AND 100),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE quiz_attempts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  quiz_id UUID NOT NULL REFERENCES quizzes(id),
  user_id UUID NOT NULL REFERENCES users(id),
  score INTEGER NOT NULL,
  passed BOOLEAN NOT NULL,
  attempted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE certificates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  course_id UUID NOT NULL REFERENCES courses(id),
  issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, course_id)
);

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_user_id UUID REFERENCES users(id),
  action VARCHAR(64) NOT NULL,
  target_type VARCHAR(64) NOT NULL,
  target_id UUID NOT NULL,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Indexes

-- Foreign key indexes (always)
CREATE INDEX idx_users_org ON users(organization_id);
CREATE INDEX idx_courses_org ON courses(organization_id);
CREATE INDEX idx_courses_instructor ON courses(instructor_id);
CREATE INDEX idx_lessons_course ON lessons(course_id);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_progress_enrollment ON progress(enrollment_id);

-- Query-specific indexes
CREATE INDEX idx_courses_published ON courses(published_at) WHERE published_at IS NOT NULL;
CREATE INDEX idx_enrollments_active ON enrollments(student_id) WHERE completed_at IS NULL;

-- Leaderboard query support
CREATE INDEX idx_quiz_attempts_score ON quiz_attempts(quiz_id, score DESC);

-- Audit log query support
CREATE INDEX idx_audit_logs_target ON audit_logs(target_type, target_id, created_at DESC);

Normalization Decisions

• Normalized: lessons separate from courses; enrollments separate from users; progress in its own table.
• Denormalized: enrollments.lessons_completed_count — cached counter avoids COUNT query on every progress-display page. Updated via trigger or app code on progress INSERT.
• JSONB used sparingly: only on audit_logs.metadata for flexible action-specific data. All core data is relational.

Common Queries (Example)

Q1: Student's enrolled courses + progress:

SELECT c.title, e.lessons_completed_count, e.completed_at
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = $1 AND e.deleted_at IS NULL
ORDER BY e.enrolled_at DESC;

Served by idx_enrollments_student — fast even at 1M enrollments.

Q5: Leaderboard:

SELECT u.name, MAX(qa.score) as best_score
FROM quiz_attempts qa
JOIN users u ON u.id = qa.user_id
WHERE qa.quiz_id = $1
GROUP BY u.id, u.name
ORDER BY best_score DESC
LIMIT 10;

Served by idx_quiz_attempts_score.

The 8 Schema Mistakes (And Which You Avoided)

1. ✅ Integer auto-increment PK — using UUID.

2. ✅ Missing created_at/updated_at — present everywhere.

3. ✅ Missing FK indexes — all FKs indexed.

4. ✅ Hard delete — using soft delete via `deleted_at`.

5. ✅ No constraints — CHECK on role, pass_threshold; NOT NULL where sensible.

6. ✅ JSON when relational would work — JSONB only in audit_logs metadata.

7. ✅ Missing unique constraints — (org_id, email), (student_id, course_id), etc.

8. ✅ No audit table — audit_logs table present.

Migration Strategy

• Use Django migrations + reviewable SQL.
• For schema changes at scale: 'expand-migrate-contract' pattern. Add new columns (backward-compatible), backfill, update app code, remove old columns last.
• Large table additions (progress at 5M rows): use CREATE INDEX CONCURRENTLY.
• Archive old audit_logs (>1 year) to cheap storage quarterly.

When To Revisit

• Enrollments > 1M rows: review query plans for student-dashboard query; may need materialized views.
• Progress > 10M rows: consider partitioning by enrollment_id or month.
• Quiz attempts > 5M: leaderboard query may slow; consider materialized view refresh nightly.
• Multi-region users: reconsider primary/replica strategy.

Key Takeaways

• UUID PKs + created_at/updated_at/deleted_at on every table. Future-proofs nearly every concern.
• Denormalize enrollments.lessons_completed_count to avoid COUNT queries. Maintain via trigger.
• Index every FK column. Missing FK indexes are the #1 query perf issue at your scale.