Course Database Design: SQL Schema Tutorial & Examples

📘 Course Database Design Tutorial: Complete SQL Schema Guide

🎯 What You'll Learn

By the end of this tutorial, you will:

Design a production-ready course platform database
Write clean SQL schema definitions for PostgreSQL/MySQL
Run practical CRUD commands for courses, modules, and lessons
Understand relationships between users, enrollments, and progress
Optimize queries with indexes for real-world performance

📐 Part 1: Database Schema Design

1.1 Users Table — Authentication & Profiles

This table stores all platform users: students, instructors, and admins.

sql
1CREATE TABLE users (
2    user_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    email           VARCHAR(255) UNIQUE NOT NULL,
4    password_hash   VARCHAR(255) NOT NULL,
5    full_name       VARCHAR(100) NOT NULL,
6    avatar_url      VARCHAR(500),
7    role            VARCHAR(20) DEFAULT 'student' 
8                    CHECK (role IN ('student', 'instructor', 'admin')),
9    bio             TEXT,
10    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
11    last_login      TIMESTAMP
12);

Key Design Decisions:

UUID for user_id prevents enumeration attacks
CHECK constraint ensures only valid roles
UNIQUE on email prevents duplicate accounts

Insert Example:

sql
1INSERT INTO users (email, password_hash, full_name, role, bio)
2VALUES (
3    'sarah.dev@example.com',
4    '$2b$12$hashedpasswordhere',
5    'Sarah Johnson',
6    'instructor',
7    'Full-stack developer with 10 years experience'
8);
9
10-- Verify
11SELECT user_id, email, full_name, role, created_at 
12FROM users 
13WHERE email = 'sarah.dev@example.com';

1.2 Courses Table — Course Catalog

The central table for all published and draft courses.

sql
1CREATE TABLE courses (
2    course_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    instructor_id   UUID NOT NULL REFERENCES users(user_id),
4    title           VARCHAR(200) NOT NULL,
5    slug            VARCHAR(200) UNIQUE NOT NULL,
6    description     TEXT,
7    short_desc      VARCHAR(300),
8    thumbnail_url   VARCHAR(500),
9    level           VARCHAR(20) DEFAULT 'beginner'
10                    CHECK (level IN ('beginner', 'intermediate', 'advanced')),
11    category_id     UUID,
12    price           DECIMAL(10,2) DEFAULT 0.00,
13    currency        VARCHAR(3) DEFAULT 'USD',
14    status          VARCHAR(20) DEFAULT 'draft'
15                    CHECK (status IN ('draft', 'published', 'archived')),
16    duration_hours  DECIMAL(5,1),
17    enrolled_count  INT DEFAULT 0,
18    rating_avg      DECIMAL(2,1) DEFAULT 0.0,
19    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
20    published_at    TIMESTAMP
21);

Insert Example:

sql
1INSERT INTO courses (instructor_id, title, slug, description, level, price, status)
2VALUES (
3    'a1b2c3d4-e5f6-7890-abcd-ef1234567890', -- Sarah's user_id
4    'Full-Stack Web Development Bootcamp',
5    'fullstack-web-dev-bootcamp',
6    'Master HTML, CSS, JavaScript, React, Node.js and PostgreSQL in one comprehensive course.',
7    'beginner',
8    49.99,
9    'published'
10);
11
12-- List all published courses with instructor names
13SELECT 
14    c.course_id,
15    c.title,
16    c.slug,
17    c.level,
18    c.price,
19    c.enrolled_count,
20    c.rating_avg,
21    u.full_name AS instructor_name
22FROM courses c
23JOIN users u ON c.instructor_id = u.user_id
24WHERE c.status = 'published'
25ORDER BY c.enrolled_count DESC;

1.3 Categories Table — Course Organization

Hierarchical categories for browsing and filtering.

sql
1CREATE TABLE categories (
2    category_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    name            VARCHAR(100) NOT NULL,
4    slug            VARCHAR(100) UNIQUE NOT NULL,
5    parent_id       UUID REFERENCES categories(category_id),
6    icon_url        VARCHAR(500),
7    description     TEXT
8);

Insert Example:

sql
1-- Parent categories
2INSERT INTO categories (name, slug, description) VALUES
3('Programming', 'programming', 'Software development courses'),
4('Design', 'design', 'UI/UX and graphic design courses');
5
6-- Sub-categories
7INSERT INTO categories (name, slug, parent_id, description)
8VALUES (
9    'Web Development',
10    'web-development',
11    (SELECT category_id FROM categories WHERE slug = 'programming'),
12    'Frontend and backend web technologies'
13);
14
15-- Get category tree
16WITH RECURSIVE category_tree AS (
17    SELECT category_id, name, parent_id, 0 AS depth
18    FROM categories
19    WHERE parent_id IS NULL
20    
21    UNION ALL
22    
23    SELECT c.category_id, c.name, c.parent_id, ct.depth + 1
24    FROM categories c
25    JOIN category_tree ct ON c.parent_id = ct.category_id
26)
27SELECT 
28    REPEAT('  ', depth) || name AS category_tree
29FROM category_tree
30ORDER BY depth, name;

1.4 Course Modules Table — Learning Units

Modules divide a course into logical sections.

sql
1CREATE TABLE course_modules (
2    module_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    course_id       UUID NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
4    title           VARCHAR(200) NOT NULL,
5    description     TEXT,
6    module_order    INT NOT NULL,
7    is_published    BOOLEAN DEFAULT FALSE,
8    estimated_mins  INT,
9    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
10    
11    UNIQUE(course_id, module_order)
12);

Insert Example:

sql
1-- Add modules to a course
2INSERT INTO course_modules (course_id, title, description, module_order, estimated_mins, is_published)
3VALUES
4('course-uuid-here', 'Module 1: HTML & CSS Fundamentals', 'Build your first web pages', 1, 180, TRUE),
5('course-uuid-here', 'Module 2: JavaScript Basics', 'Variables, functions, and DOM', 2, 240, TRUE),
6('course-uuid-here', 'Module 3: React Framework', 'Components, hooks, and state', 3, 300, TRUE);
7
8-- Get all modules for a course in order
9SELECT 
10    module_id,
11    module_order,
12    title,
13    description,
14    estimated_mins,
15    is_published
16FROM course_modules
17WHERE course_id = 'course-uuid-here'
18ORDER BY module_order;

1.5 Module Lessons Table — Individual Learning Items

Lessons are the actual content units within a module.

sql
1CREATE TABLE module_lessons (
2    lesson_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    module_id       UUID NOT NULL REFERENCES course_modules(module_id) ON DELETE CASCADE,
4    title           VARCHAR(200) NOT NULL,
5    lesson_order    INT NOT NULL,
6    lesson_type     VARCHAR(20) DEFAULT 'video'
7                    CHECK (lesson_type IN ('video', 'text', 'quiz', 'assignment', 'live_session', 'project')),
8    duration_mins   INT,
9    is_free_preview BOOLEAN DEFAULT FALSE,
10    is_published    BOOLEAN DEFAULT FALSE,
11    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
12    
13    UNIQUE(module_id, lesson_order)
14);

Insert Example:

sql
1-- Add lessons to Module 1
2INSERT INTO module_lessons (module_id, title, lesson_order, lesson_type, duration_mins, is_free_preview, is_published)
3VALUES
4('module-1-uuid', '1.1 What is HTML?', 1, 'video', 15, TRUE, TRUE),
5('module-1-uuid', '1.2 HTML Document Structure', 2, 'video', 20, FALSE, TRUE),
6('module-1-uuid', '1.3 CSS Selectors & Properties', 3, 'video', 25, FALSE, TRUE),
7('module-1-uuid', '1.4 Quiz: HTML & CSS Basics', 4, 'quiz', 15, FALSE, TRUE);
8
9-- Get full course curriculum
10SELECT 
11    c.title AS course_title,
12    cm.module_order,
13    cm.title AS module_title,
14    ml.lesson_order,
15    ml.title AS lesson_title,
16    ml.lesson_type,
17    ml.duration_mins,
18    ml.is_free_preview
19FROM courses c
20JOIN course_modules cm ON c.course_id = cm.course_id
21JOIN module_lessons ml ON cm.module_id = ml.module_id
22WHERE c.slug = 'fullstack-web-dev-bootcamp'
23ORDER BY cm.module_order, ml.lesson_order;

1.6 Enrollments Table — Student Course Access

Tracks which student is enrolled in which course.

sql
1CREATE TABLE enrollments (
2    enrollment_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    user_id         UUID NOT NULL REFERENCES users(user_id),
4    course_id       UUID NOT NULL REFERENCES courses(course_id),
5    enrolled_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
6    completed_at    TIMESTAMP,
7    status          VARCHAR(20) DEFAULT 'active'
8                    CHECK (status IN ('active', 'completed', 'dropped', 'paused')),
9    progress_pct    DECIMAL(5,2) DEFAULT 0.00,
10    last_accessed   TIMESTAMP,
11    
12    UNIQUE(user_id, course_id)
13);

Insert & Query Examples:

sql
1-- Enroll a student
2INSERT INTO enrollments (user_id, course_id, status)
3VALUES ('student-uuid', 'course-uuid', 'active');
4
5-- Get student's enrolled courses
6SELECT 
7    e.enrollment_id,
8    c.title AS course_title,
9    c.thumbnail_url,
10    u.full_name AS instructor,
11    e.progress_pct,
12    e.status,
13    e.enrolled_at,
14    e.last_accessed
15FROM enrollments e
16JOIN courses c ON e.course_id = c.course_id
17JOIN users u ON c.instructor_id = u.user_id
18WHERE e.user_id = 'student-uuid'
19AND e.status = 'active'
20ORDER BY e.last_accessed DESC;
21
22-- Update progress
23UPDATE enrollments 
24SET progress_pct = 45.50, last_accessed = CURRENT_TIMESTAMP
25WHERE enrollment_id = 'enrollment-uuid';

1.7 Lesson Progress Table — Tracking Completion

Granular tracking of which lessons a student has completed.

sql
1CREATE TABLE lesson_progress (
2    progress_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    enrollment_id   UUID NOT NULL REFERENCES enrollments(enrollment_id),
4    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id),
5    status          VARCHAR(20) DEFAULT 'not_started'
6                    CHECK (status IN ('not_started', 'in_progress', 'completed')),
7    started_at      TIMESTAMP,
8    completed_at    TIMESTAMP,
9    watch_time_secs INT DEFAULT 0,
10    notes           TEXT,
11    
12    UNIQUE(enrollment_id, lesson_id)
13);

Insert & Query Examples:

sql
1-- Mark lesson as started
2INSERT INTO lesson_progress (enrollment_id, lesson_id, status, started_at)
3VALUES ('enroll-uuid', 'lesson-uuid', 'in_progress', CURRENT_TIMESTAMP);
4
5-- Mark lesson as completed
6UPDATE lesson_progress 
7SET status = 'completed', completed_at = CURRENT_TIMESTAMP
8WHERE enrollment_id = 'enroll-uuid' AND lesson_id = 'lesson-uuid';
9
10-- Calculate course progress percentage
11WITH course_stats AS (
12    SELECT 
13        e.enrollment_id,
14        COUNT(DISTINCT ml.lesson_id) AS total_lessons,
15        COUNT(DISTINCT CASE WHEN lp.status = 'completed' THEN lp.lesson_id END) AS completed_lessons
16    FROM enrollments e
17    JOIN courses c ON e.course_id = c.course_id
18    JOIN course_modules cm ON c.course_id = cm.course_id
19    JOIN module_lessons ml ON cm.module_id = ml.module_id
20    LEFT JOIN lesson_progress lp ON e.enrollment_id = lp.enrollment_id 
21        AND ml.lesson_id = lp.lesson_id
22    WHERE e.enrollment_id = 'enroll-uuid'
23    GROUP BY e.enrollment_id
24)
25UPDATE enrollments 
26SET progress_pct = ROUND((completed_lessons::DECIMAL / NULLIF(total_lessons, 0)) * 100, 2)
27FROM course_stats
28WHERE enrollments.enrollment_id = course_stats.enrollment_id;

1.8 Quizzes & Questions Tables

sql
1CREATE TABLE quizzes (
2    quiz_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id),
4    title           VARCHAR(200),
5    time_limit_mins INT,
6    pass_score      INT DEFAULT 70,
7    max_attempts    INT DEFAULT 3
8);
9
10CREATE TABLE quiz_questions (
11    question_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
12    quiz_id         UUID NOT NULL REFERENCES quizzes(quiz_id),
13    question_text   TEXT NOT NULL,
14    question_type   VARCHAR(20) DEFAULT 'mcq'
15                    CHECK (question_type IN ('mcq', 'true_false', 'fill_blank', 'matching', 'code')),
16    options         JSONB,
17    correct_answer  JSONB,
18    points          INT DEFAULT 1,
19    order_index     INT
20);

Insert Example:

sql
1-- Create a quiz
2INSERT INTO quizzes (lesson_id, title, time_limit_mins, pass_score)
3VALUES ('lesson-quiz-uuid', 'HTML & CSS Basics Quiz', 15, 70);
4
5-- Add questions
6INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
7VALUES (
8    'quiz-uuid',
9    'What does HTML stand for?',
10    'mcq',
11    '["Hyper Text Markup Language", "High Tech Modern Language", "Hyper Transfer Markup Language", "Home Tool Markup Language"]',
12    '{"answer": 0, "explanation": "HTML = HyperText Markup Language"}',
13    1,
14    1
15);

1.9 Reviews Table — Course Ratings

sql
1CREATE TABLE reviews (
2    review_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    course_id       UUID NOT NULL REFERENCES courses(course_id),
4    user_id         UUID NOT NULL REFERENCES users(user_id),
5    rating          INT CHECK (rating BETWEEN 1 AND 5),
6    comment         TEXT,
7    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
8    
9    UNIQUE(course_id, user_id)
10);

Insert & Aggregate Example:

sql
1-- Add a review
2INSERT INTO reviews (course_id, user_id, rating, comment)
3VALUES ('course-uuid', 'student-uuid', 5, 'Excellent course! Very comprehensive.');
4
5-- Update course average rating (run via trigger or scheduled job)
6UPDATE courses 
7SET rating_avg = (
8    SELECT ROUND(AVG(rating)::NUMERIC, 1) 
9    FROM reviews 
10    WHERE course_id = 'course-uuid'
11)
12WHERE course_id = 'course-uuid';
13
14-- Get top-rated courses
15SELECT 
16    c.title,
17    c.rating_avg,
18    COUNT(r.review_id) AS review_count
19FROM courses c
20LEFT JOIN reviews r ON c.course_id = r.course_id
21WHERE c.status = 'published'
22GROUP BY c.course_id, c.title, c.rating_avg
23HAVING COUNT(r.review_id) >= 5
24ORDER BY c.rating_avg DESC, review_count DESC
25LIMIT 10;

⚡ Part 2: Performance Indexes

Add these indexes for production performance:

sql
1-- Course lookups
2CREATE INDEX idx_courses_instructor ON courses(instructor_id);
3CREATE INDEX idx_courses_category ON courses(category_id);
4CREATE INDEX idx_courses_status ON courses(status);
5CREATE INDEX idx_courses_slug ON courses(slug);
6
7-- Module & lesson ordering
8CREATE INDEX idx_modules_course_order ON course_modules(course_id, module_order);
9CREATE INDEX idx_lessons_module_order ON module_lessons(module_id, lesson_order);
10
11-- Enrollment queries
12CREATE INDEX idx_enrollments_user ON enrollments(user_id);
13CREATE INDEX idx_enrollments_course ON enrollments(course_id);
14CREATE INDEX idx_enrollments_status ON enrollments(status);
15
16-- Progress tracking
17CREATE INDEX idx_progress_enrollment ON lesson_progress(enrollment_id);
18CREATE INDEX idx_progress_lesson ON lesson_progress(lesson_id);
19
20-- Reviews
21CREATE INDEX idx_reviews_course ON reviews(course_id);
22CREATE INDEX idx_reviews_user ON reviews(user_id);

🧪 Part 3: Hands-On Learning Exercises

Exercise 1: Create a Complete Course

Write SQL to:

Create an instructor user
Create a course with 2 modules
Add 3 lessons per module
Enroll 2 students
Mark some lessons complete

Exercise 2: Progress Dashboard Query

Write a query that shows:

Student name
Course title
Total lessons
Completed lessons
Progress percentage
Last activity date

Exercise 3: Instructor Analytics

Write a query that shows an instructor:

Total students across all courses
Average rating per course
Total revenue (price × enrollments)
Most popular course

🏗️ Part 4: Best Practices

Practice	Why It Matters
Use UUIDs for IDs	Prevents ID enumeration, safe for public URLs
ON DELETE CASCADE	Automatically cleans up child records
CHECK constraints	Enforces data integrity at database level
UNIQUE constraints	Prevents duplicate enrollments, slugs, emails
JSONB for flexible data	Quiz options, rubrics, metadata without schema changes
Indexes on foreign keys	Speeds up JOIN operations
Separate metadata from content	SQL for structure, NoSQL for lesson body

📊 Complete ER Diagram Summary

users (1) ────────► (N) courses
   │                    │
   │                    │
   └──► (N) enrollments ◄────┘
            │
            ▼
      lesson_progress
            │
            ▼
      module_lessons (N) ◄─── course_modules (N) ◄─── courses
            │
            ▼
         quizzes
            │
            ▼
      quiz_questions

🎯 Quick Reference: Most Used Queries

sql
1-- 1. Get full course curriculum
2SELECT cm.module_order, cm.title, ml.lesson_order, ml.title, ml.lesson_type
3FROM course_modules cm
4JOIN module_lessons ml ON cm.module_id = ml.module_id
5WHERE cm.course_id = 'uuid'
6ORDER BY cm.module_order, ml.lesson_order;
7
8-- 2. Get student dashboard
9SELECT c.title, e.progress_pct, e.status, e.last_accessed
10FROM enrollments e
11JOIN courses c ON e.course_id = c.course_id
12WHERE e.user_id = 'uuid' AND e.status = 'active';
13
14-- 3. Get course statistics
15SELECT 
16    c.title,
17    COUNT(DISTINCT e.user_id) AS total_students,
18    ROUND(AVG(r.rating), 1) AS avg_rating,
19    COUNT(DISTINCT r.review_id) AS total_reviews
20FROM courses c
21LEFT JOIN enrollments e ON c.course_id = e.course_id
22LEFT JOIN reviews r ON c.course_id = r.course_id
23WHERE c.course_id = 'uuid'
24GROUP BY c.course_id, c.title;

This tutorial gives you a production-ready foundation for any e-learning platform. Start with these tables, add the indexes, and scale with NoSQL for lesson content when needed.