🎓 Course Database Design: Complete Architecture Guide
A well-designed course database balances structured relational data (enrollments, progress, payments) with unstructured content (lesson materials, videos, quizzes). Here's a comprehensive blueprint.
📊 1. Structured Database (SQL) — Core Architecture
Entity Relationship Overview
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ Users │ │ Enrollments │ │ Courses │
├─────────────────┤ ├─────────────────┤ ├─────────────────┤
│ user_id (PK) │◄────┤ enrollment_id │────►│ course_id (PK) │
│ email │ │ user_id (FK) │ │ title │
│ role │ │ course_id (FK) │ │ description │
│ created_at │ │ enrolled_at │ │ instructor_id │
└─────────────────┘ │ status │ │ price │
└─────────────────┘ │ level │
└─────────────────┘
│
▼
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ Course_Modules │ │ Module_Lessons │ │ Lesson_Content │
├─────────────────┤ ├─────────────────┤ ├─────────────────┤
│ module_id (PK) │◄────┤ lesson_id (PK) │────►│ content_id (PK) │
│ course_id (FK) │ │ module_id (FK) │ │ lesson_id (FK) │
│ module_order │ │ lesson_order │ │ content_type │
│ title │ │ title │ │ content_url │
│ description │ │ duration_mins │ │ metadata │
└─────────────────┘ └─────────────────┘ └─────────────────┘
Core Tables (PostgreSQL/MySQL Schema)
1.1 Users Table
1[object Object], users ( 2 user_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 email ,[object Object],(,[object Object],) ,[object Object], ,[object Object],, 4 password_hash ,[object Object],(,[object Object],) ,[object Object],, 5 full_name ,[object Object],(,[object Object],) ,[object Object],, 6 avatar_url ,[object Object],(,[object Object],), 7 role ENUM(,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 8 bio TEXT, 9 created_at ,[object Object], ,[object Object], ,[object Object],, 10 last_login ,[object Object], 11);
1.2 Courses Table
1[object Object], courses ( 2 course_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 instructor_id UUID ,[object Object], ,[object Object], users(user_id), 4 title ,[object Object],(,[object Object],) ,[object Object],, 5 slug ,[object Object],(,[object Object],) ,[object Object], ,[object Object],, 6 description TEXT, 7 short_desc ,[object Object],(,[object Object],), 8 thumbnail_url ,[object Object],(,[object Object],), 9 level ENUM(,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 10 category_id UUID ,[object Object], categories(category_id), 11 price ,[object Object],(,[object Object],,,[object Object],) ,[object Object], ,[object Object],, 12 currency ,[object Object],(,[object Object],) ,[object Object], ,[object Object],, 13 status ENUM(,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 14 duration_hours ,[object Object],(,[object Object],,,[object Object],), 15 enrolled_count ,[object Object], ,[object Object], ,[object Object],, 16 rating_avg ,[object Object],(,[object Object],,,[object Object],) ,[object Object], ,[object Object],, 17 created_at ,[object Object], ,[object Object], ,[object Object],, 18 published_at ,[object Object], 19);
1.3 Categories Table
1[object Object], categories ( 2 category_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 name ,[object Object],(,[object Object],) ,[object Object],, 4 slug ,[object Object],(,[object Object],) ,[object Object], ,[object Object],, 5 parent_id UUID ,[object Object], categories(category_id), 6 icon_url ,[object Object],(,[object Object],), 7 description TEXT 8);
1.4 Course Modules Table
1[object Object], course_modules ( 2 module_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 course_id UUID ,[object Object], ,[object Object], courses(course_id) ,[object Object], ,[object Object], CASCADE, 4 title ,[object Object],(,[object Object],) ,[object Object],, 5 description TEXT, 6 module_order ,[object Object], ,[object Object],, 7 is_published ,[object Object], ,[object Object], ,[object Object],, 8 estimated_mins ,[object Object],, 9 created_at ,[object Object], ,[object Object], ,[object Object],, 10 11 ,[object Object],(course_id, module_order) 12);
1.5 Module Lessons Table
1[object Object], module_lessons ( 2 lesson_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 module_id UUID ,[object Object], ,[object Object], course_modules(module_id) ,[object Object], ,[object Object], CASCADE, 4 title ,[object Object],(,[object Object],) ,[object Object],, 5 lesson_order ,[object Object], ,[object Object],, 6 lesson_type ENUM(,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 7 duration_mins ,[object Object],, 8 is_free_preview ,[object Object], ,[object Object], ,[object Object],, 9 is_published ,[object Object], ,[object Object], ,[object Object],, 10 created_at ,[object Object], ,[object Object], ,[object Object],, 11 12 ,[object Object],(module_id, lesson_order) 13);
1.6 Enrollments Table
1[object Object], enrollments ( 2 enrollment_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 user_id UUID ,[object Object], ,[object Object], users(user_id), 4 course_id UUID ,[object Object], ,[object Object], courses(course_id), 5 enrolled_at ,[object Object], ,[object Object], ,[object Object],, 6 completed_at ,[object Object],, 7 status ENUM(,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 8 progress_pct ,[object Object],(,[object Object],,,[object Object],) ,[object Object], ,[object Object],, 9 last_accessed ,[object Object],, 10 11 ,[object Object],(user_id, course_id) 12);
1.7 Lesson Progress Table
1[object Object], lesson_progress ( 2 progress_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 enrollment_id UUID ,[object Object], ,[object Object], enrollments(enrollment_id), 4 lesson_id UUID ,[object Object], ,[object Object], module_lessons(lesson_id), 5 status ENUM(,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 6 started_at ,[object Object],, 7 completed_at ,[object Object],, 8 watch_time_secs ,[object Object], ,[object Object], ,[object Object],, 9 notes TEXT 10);
1.8 Quizzes & Questions Tables
1[object Object], quizzes ( 2 quiz_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 lesson_id UUID ,[object Object], ,[object Object], module_lessons(lesson_id), 4 title ,[object Object],(,[object Object],), 5 time_limit_mins ,[object Object],, 6 pass_score ,[object Object], ,[object Object], ,[object Object],, 7 max_attempts ,[object Object], ,[object Object], ,[object Object], 8); 9 10,[object Object], quiz_questions ( 11 question_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 12 quiz_id UUID ,[object Object], ,[object Object], quizzes(quiz_id), 13 question_text TEXT ,[object Object],, 14 question_type ENUM(,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],) ,[object Object], ,[object Object],, 15 options JSONB, ,[object Object], 16 correct_answer JSONB, ,[object Object], 17 points ,[object Object], ,[object Object], ,[object Object],, 18 order_index ,[object Object], 19);
1.9 Assignments Table
1[object Object], assignments ( 2 assignment_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 lesson_id UUID ,[object Object], ,[object Object], module_lessons(lesson_id), 4 title ,[object Object],(,[object Object],), 5 instructions TEXT, 6 due_days ,[object Object],, 7 max_score ,[object Object], ,[object Object], ,[object Object],, 8 rubric JSONB 9); 10 11,[object Object], assignment_submissions ( 12 submission_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 13 assignment_id UUID ,[object Object], ,[object Object], assignments(assignment_id), 14 enrollment_id UUID ,[object Object], ,[object Object], enrollments(enrollment_id), 15 submission_text TEXT, 16 file_urls JSONB, 17 submitted_at ,[object Object], ,[object Object], ,[object Object],, 18 score ,[object Object],, 19 feedback TEXT, 20 graded_by UUID ,[object Object], users(user_id), 21 graded_at ,[object Object], 22);
1.10 Reviews Table
1[object Object], reviews ( 2 review_id UUID ,[object Object], ,[object Object], gen_random_uuid(), 3 course_id UUID ,[object Object], ,[object Object], courses(course_id), 4 user_id UUID ,[object Object], ,[object Object], users(user_id), 5 rating ,[object Object], ,[object Object], (rating ,[object Object], ,[object Object], ,[object Object], ,[object Object],), 6 comment TEXT, 7 created_at ,[object Object], ,[object Object], ,[object Object],, 8 9 ,[object Object],(course_id, user_id) 10);
🍃 2. Unstructured Database (NoSQL) — Content & Flexibility
Why NoSQL?
Course content is hierarchical, variable-length, and media-rich. NoSQL (MongoDB/DocumentDB) handles:
- Rich text lessons with embedded media
- Quiz questions with varying structures
- User-generated notes & bookmarks
- Real-time analytics & activity logs
2.1 MongoDB Collections Design
Collection: courses_content
1[object Object], 2 ,[object Object],[object Object], ,[object Object],[object Object], 3 ,[object Object],[object Object], ,[object Object],[object Object], 4 ,[object Object],[object Object], ,[object Object],[object Object], 5 ,[object Object],[object Object], ,[object Object], 6 ,[object Object],[object Object], ,[object Object],[object Object], 7 ,[object Object],[object Object], ,[object Object],[object Object], 8 ,[object Object],[object Object], ,[object Object],[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object],[object Object], 9 ,[object Object],[object Object], ,[object Object],[object Object], 10 ,[object Object],[object Object], ,[object Object], 11 ,[object Object],[object Object], 12 ,[object Object],[object Object], ,[object Object], 13 ,[object Object], 14 ,[object Object],[object Object], ,[object Object],[object Object], 15 ,[object Object],[object Object], ,[object Object],[object Object], 16 ,[object Object],[object Object], ,[object Object],[object Object], 17 ,[object Object],[object Object], ,[object Object],[object Object], 18 ,[object Object],[object Object], ,[object Object],[object Object], 19 ,[object Object],[object Object], ,[object Object], 20 ,[object Object], 21 ,[object Object],[object Object], ,[object Object],[object Object], 22 ,[object Object],[object Object], ,[object Object],[object Object], 23 ,[object Object],[object Object], ,[object Object],[object Object], 24 ,[object Object],[object Object], ,[object Object],[object Object], 25 ,[object Object],[object Object], ,[object Object],[object Object], 26 ,[object Object],[object Object], ,[object Object],[object Object], 27 ,[object Object],[object Object], ,[object Object],[object Object], 28 ,[object Object],[object Object], ,[object Object], 29 ,[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object], ,[object Object], 30 ,[object Object],[object Object], 31 ,[object Object],[object Object], ,[object Object], 32 ,[object Object],[object Object], 33 ,[object Object], 34 ,[object Object],[object Object], ,[object Object],[object Object], 35 ,[object Object],[object Object], ,[object Object],[object Object], 36 ,[object Object],[object Object], ,[object Object],[object Object], 37 ,[object Object],[object Object], ,[object Object],[object Object], 38 ,[object Object],[object Object], ,[object Object], 39 ,[object Object],[object Object], ,[object Object],[object Object], 40 ,[object Object],[object Object], ,[object Object], 41 ,[object Object], 42 ,[object Object],[object Object], ,[object Object],[object Object], 43 ,[object Object],[object Object], ,[object Object],[object Object], 44 ,[object Object],[object Object], ,[object Object], 45 ,[object Object], 46 ,[object Object], 47 ,[object Object], 48 ,[object Object],[object Object], 49 ,[object Object], 50 ,[object Object],[object Object], ,[object Object],[object Object], 51 ,[object Object],[object Object], ,[object Object],[object Object], 52 ,[object Object],[object Object], ,[object Object],[object Object], 53 ,[object Object],[object Object], ,[object Object],[object Object], 54 ,[object Object],[object Object], ,[object Object], 55 ,[object Object],[object Object], ,[object Object],[object Object], 56 ,[object Object],[object Object], ,[object Object],[object Object], 57 ,[object Object],[object Object], ,[object Object], 58 ,[object Object], 59 ,[object Object],[object Object], ,[object Object],[object Object], 60 ,[object Object],[object Object], ,[object Object],[object Object], 61 ,[object Object],[object Object], ,[object Object],[object Object], 62 ,[object Object],[object Object], ,[object Object],[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object],[object Object], 63 ,[object Object],[object Object], ,[object Object],[object Object], 64 ,[object Object],[object Object], ,[object Object], 65 ,[object Object], 66 ,[object Object], 67 ,[object Object], 68 ,[object Object], 69 ,[object Object], 70 ,[object Object],[object Object], 71 ,[object Object], 72 ,[object Object],[object Object], ,[object Object],[object Object], 73 ,[object Object],[object Object], ,[object Object],[object Object], 74 ,[object Object],[object Object], ,[object Object],[object Object], 75 ,[object Object],[object Object], ,[object Object],[object Object], 76 ,[object Object],[object Object], ,[object Object],[object Object], 77 ,[object Object],[object Object], ,[object Object], ... ,[object Object], 78 ,[object Object], 79 ,[object Object],[object Object], 80 ,[object Object],[object Object], ,[object Object],[object Object], 81 ,[object Object],[object Object], ,[object Object], 82,[object Object],
Collection: user_activity_logs
1[object Object], 2 ,[object Object],[object Object], ,[object Object],[object Object], 3 ,[object Object],[object Object], ,[object Object],[object Object], 4 ,[object Object],[object Object], ,[object Object],[object Object], 5 ,[object Object],[object Object], ,[object Object], 6 ,[object Object], 7 ,[object Object],[object Object], ,[object Object],[object Object], 8 ,[object Object],[object Object], ,[object Object],[object Object], 9 ,[object Object],[object Object], ,[object Object],[object Object], 10 ,[object Object],[object Object], ,[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object], ,[object Object], 11 ,[object Object],[object Object], 12 ,[object Object], 13 ,[object Object],[object Object], ,[object Object],[object Object], 14 ,[object Object],[object Object], ,[object Object],[object Object], 15 ,[object Object],[object Object], ,[object Object],[object Object], 16 ,[object Object],[object Object], ,[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object], ,[object Object], 17 ,[object Object],[object Object], 18 ,[object Object], 19 ,[object Object],[object Object], ,[object Object],[object Object], 20 ,[object Object],[object Object], ,[object Object],[object Object], 21 ,[object Object],[object Object], ,[object Object],[object Object], 22 ,[object Object],[object Object], ,[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object],[object Object], ,[object Object], ,[object Object], 23 ,[object Object], 24 ,[object Object], 25,[object Object],
Collection: user_notes
1[object Object], 2 ,[object Object],[object Object], ,[object Object],[object Object], 3 ,[object Object],[object Object], ,[object Object],[object Object], 4 ,[object Object],[object Object], ,[object Object],[object Object], 5 ,[object Object],[object Object], ,[object Object],[object Object], 6 ,[object Object],[object Object], ,[object Object],[object Object], 7 ,[object Object],[object Object], ,[object Object],[object Object], 8 ,[object Object],[object Object], ,[object Object],[object Object], 9 ,[object Object],[object Object], ,[object Object], 10,[object Object],
Collection: course_discussions
1[object Object], 2 ,[object Object],[object Object], ,[object Object],[object Object], 3 ,[object Object],[object Object], ,[object Object],[object Object], 4 ,[object Object],[object Object], ,[object Object],[object Object], 5 ,[object Object],[object Object], ,[object Object],[object Object], 6 ,[object Object],[object Object], ,[object Object],[object Object], 7 ,[object Object],[object Object], ,[object Object],[object Object], 8 ,[object Object],[object Object], ,[object Object], 9 ,[object Object], 10 ,[object Object],[object Object], ,[object Object],[object Object], 11 ,[object Object],[object Object], ,[object Object],[object Object], 12 ,[object Object],[object Object], ,[object Object],[object Object], 13 ,[object Object],[object Object], ,[object Object],[object Object], 14 ,[object Object],[object Object], ,[object Object], 15 ,[object Object], 16 ,[object Object],[object Object], 17 ,[object Object],[object Object], ,[object Object],[object Object],[object Object], ,[object Object],[object Object],[object Object], 18 ,[object Object],[object Object], ,[object Object],[object Object], 19 ,[object Object],[object Object], ,[object Object], 20,[object Object],
🧩 3. Module Division Strategy
Recommended Module Structure
| Module Type | Purpose | Typical Lessons | Duration |
|---|---|---|---|
| Foundation | Core concepts, prerequisites | 4-6 | 2-3 hrs |
| Core Skills | Main learning objectives | 6-10 | 4-6 hrs |
| Hands-On | Projects, labs, coding exercises | 3-5 | 3-5 hrs |
| Assessment | Quizzes, assignments, peer review | 2-3 | 1-2 hrs |
| Advanced | Deep dives, edge cases | 3-5 | 2-4 hrs |
| Capstone | Real-world project | 1-2 | 5-10 hrs |
Module Naming Convention
Module {N}: {Topic} — {Outcome}
Example:
Module 1: JavaScript Fundamentals — Write Clean ES6+ Code
Module 2: React Components — Build Reusable UI Systems
Module 3: State Management — Handle Complex App Data
📖 4. Tutorial Page Structure for Each Module
4.1 Module Overview Page
┌─────────────────────────────────────────────────────────────┐
│ 📚 Module 1: JavaScript Fundamentals │
│ ───────────────────────────────────────────────────────── │
│ ⏱️ 3 hours • 6 lessons • 1 quiz • 1 project │
│ ───────────────────────────────────────────────────────── │
│ │
│ 🎯 Learning Objectives │
│ • Understand ES6+ syntax (let, const, arrow functions) │
│ • Master async programming with Promises & async/await │
│ • Manipulate the DOM efficiently │
│ │
│ 📋 Lessons │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ✅ 1.1 Variables & Data Types 15 min │ │
│ │ ⏸️ 1.2 Functions & Scope 20 min │ │
│ │ 🔒 1.3 Arrays & Objects 25 min │ │
│ │ 🔒 1.4 Async Programming 30 min │ │
│ │ 🔒 1.5 DOM Manipulation 35 min │ │
│ │ 🔒 1.6 Quiz: JavaScript Basics 15 min │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 📎 Resources │
│ • [Download] JavaScript Cheat Sheet (PDF) │
│ • [Link] MDN JavaScript Reference │
│ │
└─────────────────────────────────────────────────────────────┘
4.2 Video Lesson Page
┌─────────────────────────────────────────────────────────────┐
│ ← Back to Module 1 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ [ VIDEO PLAYER ] │ │
│ │ 15:00 / 45:00 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 1.2 Functions & Scope — 20 minutes │
│ ───────────────────────────────────────────────────────── │
│ │
│ 📖 Description │
│ Learn how function scope, closures, and the 'this' keyword │
│ work in JavaScript. We'll cover arrow functions, IIFEs, │
│ and modern function patterns. │
│ │
│ 📝 Transcript (Click to expand) │
│ [00:00] Welcome back! In this lesson... │
│ [02:15] Let's talk about function declarations... │
│ │
│ 📎 Downloads │
│ • Source Code (GitHub) │
│ • Lesson Slides (PDF) │
│ │
│ 💬 Discussion (12 comments) │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Alice: "Great explanation of closures!" │ │
│ │ Bob: "Can you explain 'this' in arrow functions?" │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ ✅ Mark as Complete → Next: 1.3 Arrays & Objects │
│ │
└─────────────────────────────────────────────────────────────┘
4.3 Text Lesson Page
┌─────────────────────────────────────────────────────────────┐
│ 2.4 React Hooks Deep Dive — Text Lesson │
│ ───────────────────────────────────────────────────────── │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ # Understanding useEffect │ │
│ │ │ │
│ │ The `useEffect` hook handles side effects in │ │
│ │ functional components. Think of it as combining │ │
│ │ `componentDidMount`, `componentDidUpdate`, and │ │
│ │ `componentWillUnmount`. │ │
│ │ │ │
│ │ ## Basic Syntax │ │
│ │ ```jsx │ │
│ │ useEffect(() => { │ │
│ │ // Side effect logic │ │
│ │ return () => { │ │
│ │ // Cleanup function │ │
│ │ }; │ │
│ │ }, [dependencies]); │ │
│ │ ``` │ │
│ │ │ │
│ │ ## Dependency Array Rules │ │
│ │ | Scenario | Array | Behavior │ │
│ │ |--------------------|--------------|---------------│ │
│ │ | Run once | `[]` | Mount only │ │
│ │ | Run on change | `[dep]` | Dep changes │ │
│ │ | Run every render | omitted | Every render │ │
│ │ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 🖊️ My Notes │
│ [______________________________________________] [Save] │
│ │
│ 🧪 Try It Yourself (Interactive Code Editor) │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ [Code Editor] [Output Preview] │ │
│ │ function App() { Hello World │ │
│ │ return <h1>Hello</h1>; │ │
│ │ } │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
4.4 Quiz Page
┌─────────────────────────────────────────────────────────────┐
│ 📝 Quiz: JavaScript Basics │
│ ⏱️ Time Remaining: 08:45 | Question 3 of 10 │
│ ───────────────────────────────────────────────────────── │
│ │
│ Question 3 │
│ ───────────────────────────────────────────────────────── │
│ What will be the output of the following code? │
│ │
│ ```javascript │
│ console.log(typeof []); │
│ ``` │
│ │
│ ○ A. "array" │
│ ● B. "object" │
│ ○ C. "undefined" │
│ ○ D. "list" │
│ │
│ [Previous] [Flag for Review] [Next]
│ │
│ 📊 Progress Bar │
│ ████████░░░░░░░░░░ 3/10 │
│ │
└─────────────────────────────────────────────────────────────┘
4.5 Assignment / Project Page
┌─────────────────────────────────────────────────────────────┐
│ 🛠️ Project: Build a Todo App with React & LocalStorage │
│ 📅 Due: Aug 20, 2026 | Max Score: 100 │
│ ───────────────────────────────────────────────────────── │
│ │
│ 📋 Instructions │
│ 1. Create a React app using Vite │
│ 2. Implement CRUD operations for todos │
│ 3. Persist data using localStorage │
│ 4. Add filtering (All / Active / Completed) │
│ 5. Style with Tailwind CSS │
│ │
│ 📊 Rubric │
│ ┌────────────────────────┬────────┬─────────────────────┐ │
│ │ Criteria │ Points │ Description │ │
│ ├────────────────────────┼────────┼─────────────────────┤ │
│ │ Functionality │ 40 │ All features work │ │
│ │ Code Quality │ 30 │ Clean, commented │ │
│ │ UI/UX │ 20 │ Responsive design │ │
│ │ Creativity │ 10 │ Extra features │ │
│ └────────────────────────┴────────┴─────────────────────┘ │
│ │
│ 📤 Submit Your Work │
│ [GitHub Repository URL: _________________________] │
│ [Live Demo URL: _________________________________] │
│ [Additional Notes: ______________________________] │
│ │
│ [Submit Assignment] │
│ │
└─────────────────────────────────────────────────────────────┘
🔗 5. Hybrid Architecture: SQL + NoSQL Integration
Data Flow Diagram
┌─────────────────────────────────────────────────────────────────┐
│ FRONTEND (React/Vue) │
└─────────────────────────────────────────────────────────────────┘
│
┌─────────────────────┼─────────────────────┐
▼ ▼ ▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ Auth API │ │ Course API │ │ Content API │
│ (SQL Users) │ │ (SQL Meta) │ │ (NoSQL Docs) │
└───────────────┘ └───────────────┘ └───────────────┘
│ │ │
▼ ▼ ▼
┌─────────────────────────────────────────────────────────────────┐
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐ │
│ │ PostgreSQL │◄──►│ Redis │◄──►│ MongoDB │ │
│ │ (Users, │ │ (Cache, │ │ (Course Content, │ │
│ │ Enrollments│ │ Sessions, │ │ Logs, Discussions) │ │
│ │ Progress) │ │ Leaderboard│ │ │ │
│ └─────────────┘ └─────────────┘ └─────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Elasticsearch (Search: courses, lessons, discussions) │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
When to Use Which?
| Data Type | Storage | Reason |
|---|---|---|
| User accounts, payments | SQL | ACID compliance, strict schema |
| Course metadata, enrollments | SQL | Relational integrity, reporting |
| Lesson content, videos, quizzes | NoSQL | Flexible schema, rich documents |
| Activity logs, analytics | NoSQL | High write throughput, time-series |
| Search indexes | Elasticsearch | Full-text search, fuzzy matching |
| Session data, leaderboards | Redis | Ultra-fast access, TTL support |
📐 6. Database Indexes for Performance
1[object Object], 2,[object Object], INDEX idx_courses_instructor ,[object Object], courses(instructor_id); 3,[object Object], INDEX idx_courses_category ,[object Object], courses(category_id); 4,[object Object], INDEX idx_courses_status_published ,[object Object], courses(status) ,[object Object], status ,[object Object], ,[object Object],; 5,[object Object], INDEX idx_modules_course_order ,[object Object], course_modules(course_id, module_order); 6,[object Object], INDEX idx_lessons_module_order ,[object Object], module_lessons(module_id, lesson_order); 7,[object Object], INDEX idx_enrollments_user ,[object Object], enrollments(user_id); 8,[object Object], INDEX idx_enrollments_course ,[object Object], enrollments(course_id); 9,[object Object], INDEX idx_progress_enrollment ,[object Object], lesson_progress(enrollment_id); 10,[object Object], INDEX idx_reviews_course ,[object Object], reviews(course_id); 11 12,[object Object], 13db.courses_content.createIndex({ "slug": ,[object Object], }, { ,[object Object],: ,[object Object], }); 14db.courses_content.createIndex({ "metadata.tags": ,[object Object], }); 15db.courses_content.createIndex({ "modules.lessons.lesson_id": ,[object Object], }); 16db.user_activity_logs.createIndex({ "user_id": ,[object Object],, "course_id": ,[object Object], }); 17db.user_activity_logs.createIndex({ "events.timestamp": ,[object Object], }); 18db.course_discussions.createIndex({ "course_id": ,[object Object],, "lesson_id": ,[object Object], });
🚀 7. Quick Start Implementation Tips
- Start with SQL for the core relational model (users, courses, enrollments)
- Add MongoDB when you need rich, variable lesson content
- Use Redis for caching course catalogs and user sessions
- Implement Elasticsearch once you have 50+ courses for search
- Keep content immutable — version your lesson documents in MongoDB
- Denormalize carefully — store
enrolled_countin SQL for fast reads, update via triggers - Use CQRS pattern: SQL for writes, read-optimized views for course browsing
This architecture scales from a single course to millions of users while keeping content flexible and learning progress reliable.