Course Module Division Strategy: Database Design Guide

📐 Course Module Division Strategy: Database Design Tutorial

🎯 What You'll Learn

By the end of this tutorial, you will:

Apply the 6-module framework to any course curriculum
Design a database schema that supports flexible module types
Write SQL commands to create, order, and manage course modules
Implement naming conventions that improve student comprehension
Build queries for module analytics and curriculum optimization

🤔 Part 1: Why Module Division Matters

The Problem

A 40-hour course without clear modules overwhelms students. They can't track progress, find topics, or feel a sense of accomplishment.

The Solution

Divide courses into logical, digestible modules with specific purposes. Each module should feel like a mini-course with a clear outcome.

Module vs Lesson

Concept	Definition	Database Table
Module	A container of related lessons (2-10 hrs)	`course_modules`
Lesson	A single learning unit (10-45 min)	`module_lessons`

📊 Part 2: The 6-Module Type Framework

Framework Overview

Module Type	Purpose	Typical Lessons	Duration	Student Outcome
Foundation	Core concepts, prerequisites	4-6	2-3 hrs	"I understand the basics"
Core Skills	Main learning objectives	6-10	4-6 hrs	"I can do the main task"
Hands-On	Projects, labs, coding exercises	3-5	3-5 hrs	"I built something real"
Assessment	Quizzes, assignments, peer review	2-3	1-2 hrs	"I know what I mastered"
Advanced	Deep dives, edge cases	3-5	2-4 hrs	"I understand edge cases"
Capstone	Real-world project	1-2	5-10 hrs	"I have a portfolio piece"

Type 1: Foundation Module

Purpose: Ensure all students start with the same baseline knowledge.

Example:

Module 1: HTML & CSS Basics — Build Your First Web Page
├── 1.1 What is HTML? (Video, 15 min)
├── 1.2 HTML Document Structure (Video, 20 min)
├── 1.3 CSS Selectors & Properties (Video, 25 min)
├── 1.4 The Box Model (Video, 30 min)
├── 1.5 Practice: Style a Card (Text, 20 min)
└── 1.6 Quiz: HTML & CSS Basics (Quiz, 15 min)
Total: 2.5 hours | 6 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 1: HTML & CSS Basics — Build Your First Web Page',
5    'Master the building blocks of every website. No prior experience needed.',
6    1,
7    'foundation',
8    150,
9    TRUE
10);

Type 2: Core Skills Module

Purpose: Teach the primary skills the course promises.

Example:

Module 2: JavaScript Fundamentals — Write Clean ES6+ Code
├── 2.1 Variables & Data Types (Video, 15 min)
├── 2.2 Functions & Scope (Video, 20 min)
├── 2.3 Arrays & Objects (Video, 25 min)
├── 2.4 DOM Manipulation (Video, 30 min)
├── 2.5 Events & Event Listeners (Video, 25 min)
├── 2.6 Async Programming (Video, 35 min)
├── 2.7 Fetch API & Promises (Video, 30 min)
├── 2.8 Error Handling (Video, 20 min)
└── 2.9 Quiz: JavaScript Basics (Quiz, 20 min)
Total: 4 hours | 9 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 2: JavaScript Fundamentals — Write Clean ES6+ Code',
5    'Master modern JavaScript syntax and browser APIs used in production.',
6    2,
7    'core_skills',
8    240,
9    TRUE
10);

Type 3: Hands-On Module

Purpose: Apply knowledge through guided projects before independent work.

Example:

Module 3: Build a Weather Dashboard — Apply API Integration
├── 3.1 Project Setup & Planning (Video, 20 min)
├── 3.2 Fetching Weather Data (Video, 30 min)
├── 3.3 Displaying Dynamic Data (Video, 35 min)
├── 3.4 Adding Search & Filters (Video, 40 min)
└── 3.5 Code Review & Best Practices (Video, 25 min)
Total: 2.5 hours | 5 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 3: Build a Weather Dashboard — Apply API Integration',
5    'Build a real weather app using live APIs and modern JavaScript patterns.',
6    3,
7    'hands_on',
8    150,
9    TRUE
10);

Type 4: Assessment Module

Purpose: Validate learning and identify knowledge gaps.

Example:

Module 4: Mid-Course Assessment — Test Your Full-Stack Skills
├── 4.1 Coding Challenge: Build a Todo App (Assignment, 45 min)
├── 4.2 Peer Review: UI/UX Evaluation (Assignment, 30 min)
└── 4.3 Comprehensive Quiz (Quiz, 20 min)
Total: 1.5 hours | 3 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 4: Mid-Course Assessment — Test Your Full-Stack Skills',
5    'Validate your progress with coding challenges, peer reviews, and quizzes.',
6    4,
7    'assessment',
8    95,
9    TRUE
10);

Type 5: Advanced Module

Purpose: Cover edge cases, performance, and production concerns.

Example:

Module 5: Performance & Security — Optimize Production Apps
├── 5.1 Web Performance Metrics (Video, 25 min)
├── 5.2 Lazy Loading & Code Splitting (Video, 30 min)
├── 5.3 Authentication Patterns (Video, 35 min)
└── 5.4 Deployment Strategies (Video, 30 min)
Total: 2 hours | 4 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 5: Performance & Security — Optimize Production Apps',
5    'Learn production-grade optimization and security best practices.',
6    5,
7    'advanced',
8    120,
9    TRUE
10);

Type 6: Capstone Module

Purpose: Synthesize all skills into a portfolio-worthy project.

Example:

Module 6: Capstone Project — Build a Full E-Commerce App
├── 6.1 Project Requirements & Architecture (Video, 30 min)
├── 6.2 Database Design & API Development (Video, 45 min)
├── 6.3 Frontend Implementation (Video, 60 min)
├── 6.4 Testing & Deployment (Video, 40 min)
├── 6.5 Portfolio Presentation (Video, 20 min)
└── 6.6 Final Submission & Review (Assignment, 60 min)
Total: 6 hours | 6 lessons

Database Insert:

sql
1INSERT INTO course_modules (course_id, title, description, module_order, module_type, estimated_mins, is_published)
2VALUES (
3    'course-uuid-fullstack',
4    'Module 6: Capstone Project — Build a Full E-Commerce App',
5    'Combine everything into a production-ready e-commerce application for your portfolio.',
6    6,
7    'capstone',
8    360,
9    TRUE
10);

🏗️ Part 3: Database Schema for Module Types

Enhanced Schema with Module Types

sql
1-- Add module_type to course_modules table
2ALTER TABLE course_modules 
3ADD COLUMN module_type VARCHAR(20) DEFAULT 'core_skills'
4CHECK (module_type IN ('foundation', 'core_skills', 'hands_on', 'assessment', 'advanced', 'capstone'));
5
6-- Add module_type_stats for analytics
7CREATE TABLE module_type_stats (
8    stat_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
9    course_id       UUID NOT NULL REFERENCES courses(course_id),
10    module_type     VARCHAR(20) NOT NULL,
11    avg_completion_pct DECIMAL(5,2) DEFAULT 0.00,
12    avg_rating      DECIMAL(2,1) DEFAULT 0.0,
13    drop_off_count  INT DEFAULT 0,
14    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
15    
16    UNIQUE(course_id, module_type)
17);

Full Course Module Table

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    module_type     VARCHAR(20) DEFAULT 'core_skills'
8                    CHECK (module_type IN ('foundation', 'core_skills', 'hands_on', 'assessment', 'advanced', 'capstone')),
9    is_published    BOOLEAN DEFAULT FALSE,
10    estimated_mins  INT,
11    learning_outcome VARCHAR(200),
12    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
13    
14    UNIQUE(course_id, module_order)
15);

📝 Part 4: Naming Convention Implementation

The Formula

Module {N}: {Topic} — {Outcome}

Component	Purpose	Example
`Module {N}`	Clear sequence	Module 1, Module 2...
`{Topic}`	What students learn	JavaScript Fundamentals
`{Outcome}`	What students can do	Write Clean ES6+ Code

Why This Works

Numbering shows progress (Module 3 of 6)
Topic helps with search and categorization
Outcome motivates enrollment ("I will be able to...")

Database Validation

sql
1-- Insert with proper naming convention
2INSERT INTO course_modules (course_id, title, module_order, module_type, estimated_mins, learning_outcome)
3VALUES (
4    'course-uuid',
5    'Module 1: JavaScript Fundamentals — Write Clean ES6+ Code',
6    1,
7    'foundation',
8    180,
9    'Write modern JavaScript using ES6+ syntax and best practices'
10);
11
12-- Find modules missing outcome (bad naming)
13SELECT module_id, title 
14FROM course_modules 
15WHERE title NOT LIKE '% — %';

⚡ Part 5: Module Management SQL Commands

Reorder Modules

sql
1-- Swap Module 2 and Module 3
2BEGIN;
3
4UPDATE course_modules SET module_order = 999 WHERE module_order = 2 AND course_id = 'course-uuid';
5UPDATE course_modules SET module_order = 2 WHERE module_order = 3 AND course_id = 'course-uuid';
6UPDATE course_modules SET module_order = 3 WHERE module_order = 999 AND course_id = 'course-uuid';
7
8COMMIT;
9
10-- Verify order
11SELECT module_order, title, module_type 
12FROM course_modules 
13WHERE course_id = 'course-uuid'
14ORDER BY module_order;

Get Full Course Curriculum with Types

sql
1SELECT 
2    cm.module_order,
3    cm.module_type,
4    cm.title,
5    cm.estimated_mins,
6    cm.learning_outcome,
7    COUNT(ml.lesson_id) AS lesson_count,
8    COALESCE(SUM(ml.duration_mins), 0) AS actual_duration_mins
9FROM course_modules cm
10LEFT JOIN module_lessons ml ON cm.module_id = ml.module_id
11WHERE cm.course_id = 'course-uuid'
12  AND cm.is_published = TRUE
13GROUP BY cm.module_id, cm.module_order, cm.module_type, cm.title, cm.estimated_mins, cm.learning_outcome
14ORDER BY cm.module_order;

Calculate Course Duration by Module Type

sql
1SELECT 
2    cm.module_type,
3    COUNT(*) AS module_count,
4    SUM(cm.estimated_mins) AS total_estimated_mins,
5    ROUND(SUM(cm.estimated_mins) / 60.0, 1) AS total_hours
6FROM course_modules cm
7WHERE cm.course_id = 'course-uuid'
8GROUP BY cm.module_type
9ORDER BY 
10    CASE cm.module_type
11        WHEN 'foundation' THEN 1
12        WHEN 'core_skills' THEN 2
13        WHEN 'hands_on' THEN 3
14        WHEN 'assessment' THEN 4
15        WHEN 'advanced' THEN 5
16        WHEN 'capstone' THEN 6
17    END;

Find Courses Missing Assessment Modules

sql
1SELECT 
2    c.course_id,
3    c.title,
4    c.slug
5FROM courses c
6WHERE c.status = 'published'
7AND NOT EXISTS (
8    SELECT 1 FROM course_modules cm 
9    WHERE cm.course_id = c.course_id 
10    AND cm.module_type = 'assessment'
11);

Get Student Progress by Module Type

sql
1SELECT 
2    cm.module_type,
3    COUNT(DISTINCT cm.module_id) AS total_modules,
4    COUNT(DISTINCT CASE WHEN lp.status = 'completed' THEN cm.module_id END) AS completed_modules,
5    ROUND(
6        COUNT(DISTINCT CASE WHEN lp.status = 'completed' THEN cm.module_id END) * 100.0 / 
7        COUNT(DISTINCT cm.module_id), 
8        2
9    ) AS completion_pct
10FROM enrollments e
11JOIN course_modules cm ON e.course_id = cm.course_id
12LEFT JOIN module_lessons ml ON cm.module_id = ml.module_id
13LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
14    AND lp.enrollment_id = e.enrollment_id
15WHERE e.enrollment_id = 'enroll-uuid'
16GROUP BY cm.module_type
17ORDER BY 
18    CASE cm.module_type
19        WHEN 'foundation' THEN 1
20        WHEN 'core_skills' THEN 2
21        WHEN 'hands_on' THEN 3
22        WHEN 'assessment' THEN 4
23        WHEN 'advanced' THEN 5
24        WHEN 'capstone' THEN 6
25    END;

📈 Part 6: Module Analytics

Drop-off Analysis by Module

sql
1WITH module_completion AS (
2    SELECT 
3        cm.module_id,
4        cm.module_order,
5        cm.title,
6        cm.module_type,
7        COUNT(DISTINCT e.enrollment_id) AS total_students,
8        COUNT(DISTINCT CASE WHEN lp.status = 'completed' THEN e.enrollment_id END) AS completed_students
9    FROM course_modules cm
10    JOIN enrollments e ON cm.course_id = e.course_id
11    LEFT JOIN module_lessons ml ON cm.module_id = ml.module_id
12    LEFT JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id 
13        AND lp.enrollment_id = e.enrollment_id
14    WHERE cm.course_id = 'course-uuid'
15    GROUP BY cm.module_id, cm.module_order, cm.title, cm.module_type
16)
17SELECT 
18    module_order,
19    title,
20    module_type,
21    total_students,
22    completed_students,
23    ROUND(completed_students * 100.0 / NULLIF(total_students, 0), 2) AS completion_rate,
24    total_students - completed_students AS drop_off_count
25FROM module_completion
26ORDER BY module_order;

Average Time Spent Per Module Type

sql
1SELECT 
2    cm.module_type,
3    AVG(EXTRACT(EPOCH FROM (lp.completed_at - lp.started_at)) / 60) AS avg_time_mins
4FROM course_modules cm
5JOIN module_lessons ml ON cm.module_id = ml.module_id
6JOIN lesson_progress lp ON ml.lesson_id = lp.lesson_id
7WHERE lp.status = 'completed'
8GROUP BY cm.module_type;

🧪 Part 7: Hands-On Learning Exercises

Exercise 1: Design a Python Course

Create 6 modules for a "Python for Data Science" course following the framework:

Foundation: Python syntax
Core Skills: Pandas & NumPy
Hands-On: Clean a real dataset
Assessment: Data analysis challenge
Advanced: Machine learning intro
Capstone: End-to-end ML project

Exercise 2: Fix Module Order

Write SQL to insert a new "Assessment" module between Module 3 and Module 4 without breaking existing order.

Solution:

sql
1-- Shift all modules after position 3
2UPDATE course_modules 
3SET module_order = module_order + 1
4WHERE course_id = 'course-uuid' AND module_order > 3;
5
6-- Insert new assessment module at position 4
7INSERT INTO course_modules (course_id, title, module_order, module_type, estimated_mins)
8VALUES (
9    'course-uuid',
10    'Module 4: Data Analysis Assessment — Prove Your Skills',
11    4,
12    'assessment',
13    90
14);

Exercise 3: Generate Course Syllabus

Write a query that outputs a formatted syllabus showing module type, title, duration, and lesson count.

Solution:

sql
1SELECT 
2    'Module ' || cm.module_order || ': ' || cm.title AS syllabus_line,
3    cm.module_type,
4    cm.estimated_mins || ' mins' AS duration,
5    COUNT(ml.lesson_id) || ' lessons' AS content_size
6FROM course_modules cm
7LEFT JOIN module_lessons ml ON cm.module_id = ml.module_id
8WHERE cm.course_id = 'course-uuid'
9GROUP BY cm.module_id, cm.module_order, cm.title, cm.module_type, cm.estimated_mins
10ORDER BY cm.module_order;

🎯 Quick Reference: Most Used Commands

sql
1-- Get course structure overview
2SELECT 
3    module_order,
4    module_type,
5    title,
6    estimated_mins
7FROM course_modules
8WHERE course_id = 'uuid'
9ORDER BY module_order;
10
11-- Count modules by type across all courses
12SELECT 
13    module_type,
14    COUNT(*) AS count,
15    ROUND(AVG(estimated_mins), 0) AS avg_duration
16FROM course_modules
17WHERE is_published = TRUE
18GROUP BY module_type;
19
20-- Find oversized modules (should be < 6 hours)
21SELECT 
22    course_id,
23    module_order,
24    title,
25    estimated_mins
26FROM course_modules
27WHERE estimated_mins > 360
28ORDER BY estimated_mins DESC;

This module division strategy gives students clear milestones, instructors structured content planning, and databases clean, queryable curriculum data.