E-Learning Module Pages

📖 Tutorial Page Database Design: Module Lesson Schema & SQL

🎯 What You'll Learn

By the end of this tutorial, you will:

• Design database schemas for 5 essential tutorial page types
• Write SQL queries that power module overviews, video players, text lessons, quizzes, and assignments
• Track lesson progress with status icons (✅ completed, ⏸️ in-progress, 🔒 locked)
• Manage transcripts, notes, rubrics, and submissions in relational tables
• Build page-specific analytics for student engagement

📐 Part 1: The 5 Tutorial Page Types

🗄️ Part 2: Database Schema for Tutorial Pages

Core Tables

sqlCopy
1-- Learning objectives for module overview
2CREATE TABLE module_objectives (
3    objective_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4    module_id       UUID NOT NULL REFERENCES course_modules(module_id) ON DELETE CASCADE,
5    objective_text  TEXT NOT NULL,
6    order_index     INT DEFAULT 1
7);
8
9-- Lesson resources (downloads, links)
10CREATE TABLE lesson_resources (
11    resource_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
12    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id) ON DELETE CASCADE,
13    resource_name   VARCHAR(200) NOT NULL,
14    resource_type   VARCHAR(20) CHECK (resource_type IN ('pdf', 'github', 'link', 'zip')),
15    resource_url    VARCHAR(500) NOT NULL,
16    order_index     INT DEFAULT 1
17);
18
19-- Video metadata for video lessons
20CREATE TABLE video_metadata (
21    video_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
22    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id) ON DELETE CASCADE,
23    video_url       VARCHAR(500) NOT NULL,
24    duration_secs   INT NOT NULL,
25    transcript      TEXT,
26    thumbnail_url   VARCHAR(500)
27);
28
29-- Student notes for text lessons
30CREATE TABLE student_notes (
31    note_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
32    user_id         UUID NOT NULL REFERENCES users(user_id),
33    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id),
34    note_text       TEXT,
35    highlighted_text TEXT,
36    color           VARCHAR(20) DEFAULT 'yellow',
37    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
38);
39
40-- Quiz session state for quiz pages
41CREATE TABLE quiz_sessions (
42    session_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
43    enrollment_id   UUID NOT NULL REFERENCES enrollments(enrollment_id),
44    quiz_id         UUID NOT NULL REFERENCES quizzes(quiz_id),
45    started_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
46    expires_at      TIMESTAMP,
47    current_question INT DEFAULT 1,
48    status          VARCHAR(20) DEFAULT 'in_progress' CHECK (status IN ('in_progress', 'completed', 'expired')),
49    flagged_questions UUID[] DEFAULT '{}'
50);
51
52-- Assignment submissions
53CREATE TABLE assignment_submissions (
54    submission_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
55    assignment_id   UUID NOT NULL REFERENCES assignments(assignment_id),
56    enrollment_id   UUID NOT NULL REFERENCES enrollments(enrollment_id),
57    repo_url        VARCHAR(500),
58    demo_url        VARCHAR(500),
59    notes           TEXT,
60    submitted_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
61    score           INT,
62    feedback        TEXT
63);
64
65-- Rubric criteria for assignments
66CREATE TABLE rubric_criteria (
67    criteria_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
68    assignment_id   UUID NOT NULL REFERENCES assignments(assignment_id) ON DELETE CASCADE,
69    criteria_name   VARCHAR(100) NOT NULL,
70    max_points      INT NOT NULL,
71    description     TEXT,
72    order_index     INT
73);

📋 Part 3: Module Overview Page

What It Shows

• Module title, duration, lesson count, quiz count, project count
• Learning objectives list
• Lesson list with completion status icons
• Downloadable resources

SQL Query: Module Overview Data

sqlCopy
1-- Get module header stats
2SELECT 
3    cm.module_id,
4    cm.title,
5    cm.description,
6    cm.estimated_mins,
7    COUNT(DISTINCT ml.lesson_id) FILTER (WHERE ml.is_published = TRUE) AS total_lessons,
8    COUNT(DISTINCT ml.lesson_id) FILTER (WHERE ml.lesson_type = 'quiz') AS quiz_count,
9    COUNT(DISTINCT ml.lesson_id) FILTER (WHERE ml.lesson_type = 'project') AS project_count
10FROM course_modules cm
11LEFT JOIN module_lessons ml ON cm.module_id = ml.module_id
12WHERE cm.module_id = 'mod-001'
13GROUP BY cm.module_id, cm.title, cm.description, cm.estimated_mins;
14
15-- Get learning objectives
16SELECT objective_text, order_index
17FROM module_objectives
18WHERE module_id = 'mod-001'
19ORDER BY order_index;
20
21-- Get lesson list with student progress status
22SELECT 
23    ml.lesson_id,
24    ml.lesson_order,
25    ml.title,
26    ml.lesson_type,
27    ml.duration_mins,
28    COALESCE(lp.status, 'not_started') AS progress_status,
29    CASE 
30        WHEN lp.status = 'completed' THEN '✅'
31        WHEN lp.status = 'in_progress' THEN '⏸️'
32        ELSE '🔒'
33    END AS status_icon
34FROM module_lessons ml
35LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
36    AND lp.enrollment_id = 'enroll-student-123'
37WHERE ml.module_id = 'mod-001'
38  AND ml.is_published = TRUE
39ORDER BY ml.lesson_order;
40
41-- Get module resources
42SELECT resource_name, resource_type, resource_url
43FROM lesson_resources lr
44JOIN module_lessons ml ON lr.lesson_id = ml.lesson_id
45WHERE ml.module_id = 'mod-001'
46ORDER BY lr.order_index;

Query Result Example

🎬 Part 4: Video Lesson Page

What It Shows

• Video player with current position
• Lesson description
• Timestamped transcript
• Downloadable resources
• Discussion comments
• Mark complete + next lesson navigation

SQL Query: Video Lesson Page Data

sqlCopy
1-- Get video lesson details
2SELECT 
3    ml.lesson_id,
4    ml.title,
5    ml.description,
6    ml.duration_mins,
7    vm.video_url,
8    vm.transcript,
9    vm.thumbnail_url,
10    vm.duration_secs,
11    COALESCE(lp.watch_time_secs, 0) AS current_position_secs,
12    lp.status AS completion_status
13FROM module_lessons ml
14LEFT JOIN video_metadata vm ON ml.lesson_id = vm.lesson_id
15LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
16    AND lp.enrollment_id = 'enroll-student-123'
17WHERE ml.lesson_id = 'les-001';
18
19-- Get timestamped transcript sections
20WITH transcript_lines AS (
21    SELECT 
22        regexp_split_to_table(vm.transcript, '\n') AS line
23    FROM video_metadata vm
24    WHERE vm.lesson_id = 'les-001'
25)
26SELECT 
27    substring(line from '\[(\d{2}:\d{2})\]') AS timestamp,
28    substring(line from '\]\s*(.+)$') AS text
29FROM transcript_lines
30WHERE line ~ '^\[\d{2}:\d{2}\]';
31
32-- Get lesson resources
33SELECT resource_name, resource_type, resource_url
34FROM lesson_resources
35WHERE lesson_id = 'les-001'
36ORDER BY order_index;
37
38-- Get discussion comments
39SELECT 
40    u.full_name,
41    u.avatar_url,
42    cd.body,
43    cd.created_at,
44    cd.is_instructor
45FROM course_discussions cd
46JOIN users u ON cd.user_id = u.user_id
47WHERE cd.lesson_id = 'les-001'
48ORDER BY cd.created_at DESC
49LIMIT 10;
50
51-- Get next lesson for navigation
52SELECT title, lesson_id
53FROM module_lessons
54WHERE module_id = (SELECT module_id FROM module_lessons WHERE lesson_id = 'les-001')
55  AND lesson_order = (
56      SELECT lesson_order + 1 
57      FROM module_lessons 
58      WHERE lesson_id = 'les-001'
59  );

Update Video Progress

sqlCopy
1-- Update watch time (called every 10 seconds while playing)
2INSERT INTO lesson_progress (enrollment_id, lesson_id, status, started_at, watch_time_secs)
3VALUES ('enroll-student-123', 'les-001', 'in_progress', CURRENT_TIMESTAMP, 300)
4ON CONFLICT (enrollment_id, lesson_id) 
5DO UPDATE SET 
6    watch_time_secs = EXCLUDED.watch_time_secs,
7    last_accessed = CURRENT_TIMESTAMP;
8
9-- Mark video as complete (when watch_time >= 90% of duration)
10UPDATE lesson_progress 
11SET 
12    status = 'completed',
13    completed_at = CURRENT_TIMESTAMP
14WHERE enrollment_id = 'enroll-student-123' 
15  AND lesson_id = 'les-001'
16  AND watch_time_secs >= (
17      SELECT duration_secs * 0.9 FROM video_metadata WHERE lesson_id = 'les-001'
18  );

📄 Part 5: Text Lesson Page

What It Shows

• Rich text content (HTML/Markdown)
• Code snippets with syntax highlighting
• Dependency array tables
• Personal notes input
• Interactive code editor

SQL Query: Text Lesson Page Data

sqlCopy
1-- Get text lesson content (stored in NoSQL, referenced here)
2SELECT 
3    ml.lesson_id,
4    ml.title,
5    ml.lesson_type,
6    ml.duration_mins,
7    cc.modules.lessons.content AS lesson_content  -- From MongoDB courses_content
8FROM module_lessons ml
9WHERE ml.lesson_id = 'les-text-001';
10
11-- Get student's saved notes for this lesson
12SELECT note_text, highlighted_text, color, created_at
13FROM student_notes
14WHERE user_id = 'user-123' AND lesson_id = 'les-text-001'
15ORDER BY created_at DESC;
16
17-- Save a new note
18INSERT INTO student_notes (user_id, lesson_id, note_text, highlighted_text, color)
19VALUES (
20    'user-123',
21    'les-text-001',
22    'Remember: empty dependency array means run once on mount',
23    'useEffect(() => {}, [])',
24    'yellow'
25);

Text Content Schema (MongoDB)

javascriptCopy
1// Stored in courses_content collection
2{
3  lesson_id: "les-text-001",
4  type: "text",
5  content: {
6    body: "<h2>Understanding useEffect</h2><p>...</p>",
7    code_snippets: [
8      {
9        language: "jsx",
10        code: "useEffect(() => { return () => {} }, []);",
11        output: null
12      }
13    ],
14    tables: [
15      {
16        headers: ["Scenario", "Array", "Behavior"],
17        rows: [
18          ["Run once", "[]", "Mount only"],
19          ["Run on change", "[dep]", "Dep changes"]
20        ]
21      }
22    ]
23  }
24}

📝 Part 6: Quiz Page

What It Shows

• Quiz title and timer
• Current question with options
• Progress bar (question X of Y)
• Flag for review checkbox
• Previous/Next navigation

SQL Query: Quiz Page Data

sqlCopy
1-- Get quiz session state
2SELECT 
3    qs.session_id,
4    qs.current_question,
5    qs.started_at,
6    qs.expires_at,
7    EXTRACT(EPOCH FROM (qs.expires_at - CURRENT_TIMESTAMP))/60 AS minutes_remaining,
8    qs.flagged_questions,
9    qz.title,
10    qz.pass_score
11FROM quiz_sessions qs
12JOIN quizzes qz ON qs.quiz_id = qz.quiz_id
13WHERE qs.session_id = 'session-uuid-456';
14
15-- Get current question with options
16SELECT 
17    qq.question_id,
18    qq.order_index,
19    qq.question_text,
20    qq.question_type,
21    qq.options,
22    qq.points
23FROM quiz_questions qq
24WHERE qq.quiz_id = 'quiz-uuid-123'
25  AND qq.order_index = (
26      SELECT current_question FROM quiz_sessions WHERE session_id = 'session-uuid-456'
27  );
28
29-- Get total question count for progress bar
30SELECT COUNT(*) AS total_questions, SUM(points) AS total_points
31FROM quiz_questions
32WHERE quiz_id = 'quiz-uuid-123';
33
34-- Get answered questions so far
35SELECT question_id, student_answer, is_correct, points_earned
36FROM quiz_answers
37WHERE attempt_id = (
38    SELECT attempt_id FROM quiz_attempts 
39    WHERE enrollment_id = 'enroll-student-123' AND quiz_id = 'quiz-uuid-123'
40    ORDER BY attempt_number DESC LIMIT 1
41);

Update Quiz State

sqlCopy
1-- Save answer and move to next question
2BEGIN;
3
4-- Insert answer
5INSERT INTO quiz_answers (attempt_id, question_id, student_answer, is_correct, points_earned)
6VALUES ('attempt-uuid', 'question-uuid', '{"selected": 1}', true, 1);
7
8-- Update session progress
9UPDATE quiz_sessions 
10SET current_question = current_question + 1
11WHERE session_id = 'session-uuid-456';
12
13-- Flag question for review
14UPDATE quiz_sessions 
15SET flagged_questions = array_append(flagged_questions, 'question-uuid')
16WHERE session_id = 'session-uuid-456';
17
18COMMIT;

🛠️ Part 7: Assignment / Project Page

What It Shows

• Project title and due date
• Step-by-step instructions
• Rubric with point breakdown
• Submission form (GitHub repo, live demo, notes)
• Score and feedback after grading

SQL Query: Assignment Page Data

sqlCopy
1-- Get assignment details
2SELECT 
3    a.assignment_id,
4    ml.title,
5    a.instructions,
6    a.due_days,
7    a.max_score,
8    e.enrolled_at + INTERVAL '1 day' * a.due_days AS due_date,
9    s.score,
10    s.feedback,
11    s.submitted_at
12FROM assignments a
13JOIN module_lessons ml ON a.lesson_id = ml.lesson_id
14JOIN enrollments e ON e.enrollment_id = 'enroll-student-123'
15LEFT JOIN assignment_submissions s ON s.assignment_id = a.assignment_id 
16    AND s.enrollment_id = 'enroll-student-123'
17WHERE a.lesson_id = 'les-project-001';
18
19-- Get rubric criteria
20SELECT criteria_name, max_points, description, order_index
21FROM rubric_criteria
22WHERE assignment_id = 'assignment-uuid-789'
23ORDER BY order_index;
24
25-- Submit assignment
26INSERT INTO assignment_submissions (assignment_id, enrollment_id, repo_url, demo_url, notes)
27VALUES (
28    'assignment-uuid-789',
29    'enroll-student-123',
30    'https://github.com/student/todo-app',
31    'https://todo-app-demo.vercel.app',
32    'Used Context API for state management'
33)
34ON CONFLICT (assignment_id, enrollment_id) 
35DO UPDATE SET 
36    repo_url = EXCLUDED.repo_url,
37    demo_url = EXCLUDED.demo_url,
38    notes = EXCLUDED.notes,
39    submitted_at = CURRENT_TIMESTAMP;

⚡ Part 8: Indexes for Tutorial Page Performance

sqlCopy
1-- Module overview
2CREATE INDEX idx_lessons_module_published ON module_lessons(module_id, is_published, lesson_order);
3CREATE INDEX idx_objectives_module ON module_objectives(module_id, order_index);
4
5-- Video lesson
6CREATE INDEX idx_video_lesson ON video_metadata(lesson_id);
7CREATE INDEX idx_resources_lesson ON lesson_resources(lesson_id, order_index);
8CREATE INDEX idx_progress_enrollment_lesson ON lesson_progress(enrollment_id, lesson_id);
9
10-- Text lesson notes
11CREATE INDEX idx_notes_user_lesson ON student_notes(user_id, lesson_id);
12
13-- Quiz
14CREATE INDEX idx_quiz_session_enrollment ON quiz_sessions(enrollment_id, quiz_id);
15CREATE INDEX idx_quiz_questions_quiz_order ON quiz_questions(quiz_id, order_index);
16
17-- Assignment
18CREATE INDEX idx_submission_assignment_enrollment ON assignment_submissions(assignment_id, enrollment_id);
19CREATE INDEX idx_rubric_assignment ON rubric_criteria(assignment_id, order_index);

🧪 Part 9: Hands-On Exercises

Exercise 1: Build Module Overview Query

Write SQL that returns a module overview showing:

• Module title and total duration
• Count of lessons by type (video, text, quiz, project)
• Student completion percentage

Solution:

sqlCopy
1SELECT 
2    cm.title,
3    cm.estimated_mins,
4    COUNT(*) FILTER (WHERE ml.lesson_type = 'video') AS videos,
5    COUNT(*) FILTER (WHERE ml.lesson_type = 'text') AS texts,
6    COUNT(*) FILTER (WHERE ml.lesson_type = 'quiz') AS quizzes,
7    COUNT(*) FILTER (WHERE ml.lesson_type = 'project') AS projects,
8    ROUND(
9        COUNT(*) FILTER (WHERE lp.status = 'completed') * 100.0 / COUNT(*), 
10        2
11    ) AS completion_pct
12FROM course_modules cm
13JOIN module_lessons ml ON cm.module_id = ml.module_id
14LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
15    AND lp.enrollment_id = 'enroll-uuid'
16WHERE cm.module_id = 'mod-001'
17GROUP BY cm.module_id, cm.title, cm.estimated_mins;

Exercise 2: Video Resume Position

Write SQL to get the exact second where a student left off watching.

Solution:

sqlCopy
1SELECT 
2    ml.title,
3    vm.duration_secs,
4    COALESCE(lp.watch_time_secs, 0) AS resume_at_sec,
5    CONCAT(
6        LPAD((COALESCE(lp.watch_time_secs, 0) / 60)::TEXT, 2, '0'), 
7        ':', 
8        LPAD((COALESCE(lp.watch_time_secs, 0) % 60)::TEXT, 2, '0')
9    ) AS resume_timestamp
10FROM module_lessons ml
11JOIN video_metadata vm ON ml.lesson_id = vm.lesson_id
12LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
13    AND lp.enrollment_id = 'enroll-uuid'
14WHERE ml.lesson_id = 'les-001';

Exercise 3: Quiz Timer Check

Write SQL that returns whether a quiz session has expired.

Solution:

sqlCopy
1SELECT 
2    session_id,
3    CASE 
4        WHEN expires_at < CURRENT_TIMESTAMP THEN 'EXPIRED'
5        ELSE 'ACTIVE'
6    END AS timer_status,
7    ROUND(EXTRACT(EPOCH FROM (expires_at - CURRENT_TIMESTAMP))/60, 0) AS minutes_left
8FROM quiz_sessions
9WHERE session_id = 'session-uuid-456';

🎯 Quick Reference: Page-to-Query Mapping

This tutorial page database design gives you complete page-level data architecture for any e-learning platform, from curriculum browsing to project submission.