E-Learning Database

🚀 E-Learning Database Implementation: Step-by-Step Build Guide

🎯 What You'll Learn

By the end of this tutorial, you will:

• Build a course platform database incrementally from zero to production
• Know exactly when to add MongoDB, Redis, and Elasticsearch
• Implement immutable lesson versioning for safe content updates
• Apply denormalization with database triggers
• Use the CQRS pattern to separate writes from reads

🏗️ Part 1: Phase 1 — Start with SQL Core

Why Start Here?

PostgreSQL handles users, payments, enrollments, and progress with ACID guarantees. Build this first — everything else hangs off it.

Step 1: Create Core Tables

sqlCopy
1-- Users: students, instructors, admins
2CREATE TABLE users (
3    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4    email VARCHAR(255) UNIQUE NOT NULL,
5    password_hash VARCHAR(255) NOT NULL,
6    full_name VARCHAR(100) NOT NULL,
7    role VARCHAR(20) DEFAULT 'student' CHECK (role IN ('student', 'instructor', 'admin')),
8    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
9);
10
11-- Courses: catalog metadata
12CREATE TABLE courses (
13    course_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
14    instructor_id UUID NOT NULL REFERENCES users(user_id),
15    title VARCHAR(200) NOT NULL,
16    slug VARCHAR(200) UNIQUE NOT NULL,
17    description TEXT,
18    price DECIMAL(10,2) DEFAULT 0.00,
19    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
20    enrolled_count INT DEFAULT 0,
21    rating_avg DECIMAL(2,1) DEFAULT 0.0,
22    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
23);
24
25-- Enrollments: who bought what
26CREATE TABLE enrollments (
27    enrollment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
28    user_id UUID NOT NULL REFERENCES users(user_id),
29    course_id UUID NOT NULL REFERENCES courses(course_id),
30    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
31    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'dropped')),
32    progress_pct DECIMAL(5,2) DEFAULT 0.00,
33    UNIQUE(user_id, course_id)
34);
35
36-- Essential indexes
37CREATE INDEX idx_courses_status ON courses(status);
38CREATE INDEX idx_courses_slug ON courses(slug);
39CREATE INDEX idx_enrollments_user ON enrollments(user_id);
40CREATE INDEX idx_enrollments_course ON enrollments(course_id);

Step 2: Seed Test Data

sqlCopy
1-- Insert instructor
2INSERT INTO users (email, password_hash, full_name, role)
3VALUES ('sarah@example.com', 'hash123', 'Sarah Johnson', 'instructor')
4RETURNING user_id;
5-- Returns: user-uuid-1
6
7-- Insert course
8INSERT INTO courses (instructor_id, title, slug, price, status)
9VALUES ('user-uuid-1', 'JavaScript Fundamentals', 'js-fundamentals', 29.99, 'published')
10RETURNING course_id;
11-- Returns: course-uuid-1
12
13-- Enroll a student
14INSERT INTO users (email, password_hash, full_name, role)
15VALUES ('student@example.com', 'hash456', 'Alex Student', 'student')
16RETURNING user_id;
17-- Returns: user-uuid-2
18
19INSERT INTO enrollments (user_id, course_id, status)
20VALUES ('user-uuid-2', 'course-uuid-1', 'active');

Step 3: Verify Core Works

sqlCopy
1-- Student dashboard query
2SELECT 
3    c.title,
4    c.slug,
5    c.price,
6    e.progress_pct,
7    e.enrolled_at
8FROM enrollments e
9JOIN courses c ON e.course_id = c.course_id
10WHERE e.user_id = 'user-uuid-2'
11AND e.status = 'active';

🍃 Part 2: Phase 2 — Add MongoDB for Rich Content

When to Add

Add MongoDB when you need to store variable lesson structures — videos, text, code snippets, quizzes — that don't fit rigid SQL columns.

Step 4: Install & Connect MongoDB

javascriptCopy
1// Connection string
2const { MongoClient } = require('mongodb');
3const client = new MongoClient('mongodb://localhost:27017/course_platform');
4const db = client.db('course_platform');

Step 5: Create Content Collection

javascriptCopy
1// Store full curriculum as nested documents
2db.courses_content.insertOne({
3  _id: "course-uuid-1",  // Matches PostgreSQL course_id
4  title: "JavaScript Fundamentals",
5  slug: "js-fundamentals",
6  version: 1,
7  is_latest: true,
8  modules: [
9    {
10      module_id: "mod-001",
11      order: 1,
12      title: "Variables & Data Types",
13      lessons: [
14        {
15          lesson_id: "les-001",
16          order: 1,
17          title: "let vs const vs var",
18          type: "video",
19          duration_mins: 15,
20          video_url: "https://cdn.example.com/videos/les-001.mp4",
21          transcript: "Welcome! In this lesson we compare variable declarations...",
22          resources: [
23            { name: "Cheat Sheet", url: "https://cdn.example.com/sheets/variables.pdf" }
24          ]
25        },
26        {
27          lesson_id: "les-002",
28          order: 2,
29          title: "Data Types in JavaScript",
30          type: "text",
31          content: {
32            body: "<h2>Primitive Types</h2><p>JavaScript has 7 primitive types...</p>",
33            code_snippets: [
34              {
35                language: "javascript",
36                code: "const name = 'Sarah'; // string\nconst age = 30; // number",
37                output: null
38              }
39            ]
40          }
41        }
42      ]
43    }
44  ],
45  created_at: new Date(),
46  updated_at: new Date()
47});

Step 6: Query Content from Your API

javascriptCopy
1// Get full course curriculum
2const course = await db.collection('courses_content').findOne(
3  { slug: "js-fundamentals", is_latest: true },
4  { projection: { modules: 1, title: 1 } }
5);
6
7// Get single lesson
8const lesson = await db.collection('courses_content').findOne(
9  { _id: "course-uuid-1", "modules.lessons.lesson_id": "les-001" },
10  { projection: { "modules.$": 1 } }
11);

⚡ Part 3: Phase 3 — Add Redis for Caching

When to Add

Add Redis when your course catalog page or student dashboard gets slow under load. Cache hot data for sub-millisecond access.

Step 7: Cache Course Catalog

bashCopy
1# After querying PostgreSQL for published courses, cache the result
2SET "catalog:published:page:1" '{"courses":[{"title":"JavaScript Fundamentals","slug":"js-fundamentals","price":29.99}]}' EX 300
3
4# Check cache before hitting database
5GET "catalog:published:page:1"
6# If exists: return immediately
7# If null: query PostgreSQL, then SET cache

Step 8: Cache User Sessions

bashCopy
1# Store session after login
2SET "session:user-uuid-2" '{"role":"student","enrolled":["course-uuid-1"]}' EX 86400
3
4# Verify on every API request
5GET "session:user-uuid-2"
6# Returns: {"role":"student","enrolled":["course-uuid-1"]}
7
8# Invalidate on logout
9DEL "session:user-uuid-2"

Step 9: Cache Course Detail Pages

javascriptCopy
1// Node.js cache-aside pattern
2async function getCourseDetail(slug) {
3  const cacheKey = `course:detail:${slug}`;
4  
5  // 1. Check Redis
6  const cached = await redis.get(cacheKey);
7  if (cached) return JSON.parse(cached);
8  
9  // 2. Query PostgreSQL (metadata)
10  const meta = await pg.query('SELECT * FROM courses WHERE slug = $1', [slug]);
11  
12  // 3. Query MongoDB (content)
13  const content = await mongo.collection('courses_content').findOne({ slug });
14  
15  // 4. Combine and cache for 5 minutes
16  const result = { meta: meta.rows[0], content };
17  await redis.setex(cacheKey, 300, JSON.stringify(result));
18  
19  return result;
20}

🔍 Part 4: Phase 4 — Add Elasticsearch for Search

When to Add

Add Elasticsearch once you have 50+ courses and students need to search by title, description, or tags. PostgreSQL ILIKE becomes too slow.

Step 10: Index Courses

jsonCopy
1PUT /courses/_doc/course-uuid-1
2{
3  "title": "JavaScript Fundamentals",
4  "description": "Master variables, functions, and async programming...",
5  "slug": "js-fundamentals",
6  "level": "beginner",
7  "tags": ["javascript", "es6", "web development"],
8  "instructor": "Sarah Johnson",
9  "price": 29.99,
10  "rating_avg": 4.8,
11  "enrolled_count": 15420,
12  "status": "published"
13}

Step 11: Search with Fuzzy Matching

jsonCopy
1GET /courses/_search
2{
3  "query": {
4    "multi_match": {
5      "query": "javscript begginer",
6      "fields": ["title^3", "description", "tags"],
7      "fuzziness": "AUTO"
8    }
9  },
10  "highlight": {
11    "fields": {
12      "title": {},
13      "description": {}
14    }
15  }
16}

Step 12: Filtered Browse

jsonCopy
1GET /courses/_search
2{
3  "query": {
4    "bool": {
5      "must": [
6        { "match": { "title": "javascript" } }
7      ],
8      "filter": [
9        { "term": { "status": "published" } },
10        { "range": { "price": { "lte": 50 } } }
11      ]
12    }
13  },
14  "sort": [
15    { "rating_avg": "desc" }
16  ]
17}

🔄 Part 5: Immutable Content Versioning

Why Immutability Matters

When you update Lesson 3, students currently watching it shouldn't see broken content. Versioning keeps old versions accessible while publishing new ones.

Step 13: Version Your MongoDB Documents

javascriptCopy
1// Publish version 1
2db.courses_content.insertOne({
3  _id: "course-uuid-1-v1",
4  course_id: "course-uuid-1",
5  version: 1,
6  is_latest: true,
7  modules: [ /* ... */ ],
8  created_at: new Date()
9});
10
11// Create version 2 (update lesson content)
12db.courses_content.insertOne({
13  _id: "course-uuid-1-v2",
14  course_id: "course-uuid-1",
15  version: 2,
16  is_latest: true,
17  modules: [ /* updated content ... */ ],
18  created_at: new Date()
19});
20
21// Mark old version as not latest
22db.courses_content.updateOne(
23  { _id: "course-uuid-1-v1" },
24  { $set: { is_latest: false } }
25);

Step 14: Track Which Version Students See

sqlCopy
1-- Add version to enrollment
2ALTER TABLE enrollments ADD COLUMN content_version INT DEFAULT 1;
3
4-- Student enrolled during version 1, continues seeing version 1
5-- New students see version 2

Step 15: Query Correct Version

javascriptCopy
1// Get content for a specific student
2const enrollment = await pg.query(
3  'SELECT content_version FROM enrollments WHERE enrollment_id = $1',
4  ['enroll-uuid-123']
5);
6
7const version = enrollment.rows[0]?.content_version || 1;
8
9const content = await mongo.collection('courses_content').findOne({
10  course_id: "course-uuid-1",
11  version: version
12});

📊 Part 6: Denormalization with Triggers

Why Denormalize?

enrolled_count on the courses table is a cached counter. Calculating COUNT(*) from enrollments for every catalog page is too slow.

Step 16: Auto-Update Counter with Trigger

sqlCopy
1-- Function to increment counter
2CREATE OR REPLACE FUNCTION increment_enrolled_count()
3RETURNS TRIGGER AS $$
4BEGIN
5    UPDATE courses 
6    SET enrolled_count = enrolled_count + 1 
7    WHERE course_id = NEW.course_id;
8    RETURN NEW;
9END;
10$$ LANGUAGE plpgsql;
11
12-- Trigger runs on every new enrollment
13CREATE TRIGGER trg_enrollment_insert
14AFTER INSERT ON enrollments
15FOR EACH ROW
16EXECUTE FUNCTION increment_enrolled_count();
17
18-- Test it
19INSERT INTO enrollments (user_id, course_id, status)
20VALUES ('user-uuid-3', 'course-uuid-1', 'active');
21
22-- Check counter updated automatically
23SELECT title, enrolled_count FROM courses WHERE course_id = 'course-uuid-1';

Step 17: Decrement on Drop

sqlCopy
1CREATE OR REPLACE FUNCTION decrement_enrolled_count()
2RETURNS TRIGGER AS $$
3BEGIN
4    UPDATE courses 
5    SET enrolled_count = enrolled_count - 1 
6    WHERE course_id = OLD.course_id;
7    RETURN OLD;
8END;
9$$ LANGUAGE plpgsql;
10
11CREATE TRIGGER trg_enrollment_delete
12AFTER DELETE ON enrollments
13FOR EACH ROW
14EXECUTE FUNCTION decrement_enrolled_count();

Step 18: Rating Average Trigger

sqlCopy
1CREATE OR REPLACE FUNCTION update_course_rating()
2RETURNS TRIGGER AS $$
3BEGIN
4    UPDATE courses 
5    SET rating_avg = (
6        SELECT ROUND(AVG(rating)::NUMERIC, 1) 
7        FROM reviews 
8        WHERE course_id = NEW.course_id
9    )
10    WHERE course_id = NEW.course_id;
11    RETURN NEW;
12END;
13$$ LANGUAGE plpgsql;
14
15CREATE TRIGGER trg_review_insert
16AFTER INSERT OR UPDATE ON reviews
17FOR EACH ROW
18EXECUTE FUNCTION update_course_rating();

🏛️ Part 7: CQRS Pattern — Separate Reads from Writes

What is CQRS?

Command Query Responsibility Segregation: Use PostgreSQL for writes (enrollments, payments, progress updates), but create read-optimized views for browsing.

Step 19: Create Read-Optimized Materialized View

sqlCopy
1-- Materialized view for course catalog (fast reads)
2CREATE MATERIALIZED VIEW course_catalog AS
3SELECT 
4    c.course_id,
5    c.title,
6    c.slug,
7    c.price,
8    c.rating_avg,
9    c.enrolled_count,
10    c.status,
11    u.full_name AS instructor_name,
12    cat.name AS category_name
13FROM courses c
14JOIN users u ON c.instructor_id = u.user_id
15LEFT JOIN categories cat ON c.category_id = cat.category_id
16WHERE c.status = 'published';
17
18-- Index the materialized view
19CREATE INDEX idx_catalog_category ON course_catalog(category_name);
20CREATE INDEX idx_catalog_rating ON course_catalog(rating_avg DESC);
21
22-- Refresh every 5 minutes (or use triggers for real-time)
23REFRESH MATERIALIZED VIEW CONCURRENTLY course_catalog;

Step 20: Write Path vs Read Path

javascriptCopy
1// WRITE PATH: Enroll student (hits PostgreSQL directly)
2async function enrollStudent(userId, courseId) {
3  await pg.query('BEGIN');
4  await pg.query(
5    'INSERT INTO enrollments (user_id, course_id) VALUES ($1, $2)',
6    [userId, courseId]
7  );
8  // Trigger auto-updates enrolled_count
9  await pg.query('COMMIT');
10  
11  // Invalidate cache
12  await redis.del(`catalog:published:*`);
13  await redis.del(`course:detail:*`);
14}
15
16// READ PATH: Browse catalog (hits materialized view + cache)
17async function browseCatalog(category, page) {
18  const cacheKey = `catalog:${category}:page:${page}`;
19  
20  const cached = await redis.get(cacheKey);
21  if (cached) return JSON.parse(cached);
22  
23  const result = await pg.query(
24    'SELECT * FROM course_catalog WHERE category_name = $1 LIMIT 20 OFFSET $2',
25    [category, page * 20]
26  );
27  
28  await redis.setex(cacheKey, 300, JSON.stringify(result.rows));
29  return result.rows;
30}

🧪 Part 8: Hands-On Implementation Exercise

Build a Complete Course Detail API

Requirements:

1. Check Redis cache
2. If miss: query materialized view for metadata
3. Query MongoDB for lesson content
4. Query Elasticsearch for "related courses"
5. Cache result for 5 minutes
6. Return combined JSON

Solution:

javascriptCopy
1async function getCourseDetail(slug, userId) {
2  const cacheKey = `course:detail:v2:${slug}:${userId}`;
3  
4  // 1. Redis
5  const cached = await redis.get(cacheKey);
6  if (cached) return JSON.parse(cached);
7  
8  // 2. PostgreSQL: enrollment + metadata
9  const metaResult = await pg.query(`
10    SELECT c.*, e.content_version, e.progress_pct, e.status as enroll_status
11    FROM course_catalog c
12    LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.user_id = $2
13    WHERE c.slug = $1
14  `, [slug, userId]);
15  
16  if (metaResult.rows.length === 0) throw new Error('Course not found');
17  const meta = metaResult.rows[0];
18  
19  // 3. MongoDB: content with correct version
20  const version = meta.content_version || meta.is_latest ? 'latest' : 1;
21  const contentQuery = version === 'latest' 
22    ? { course_id: meta.course_id, is_latest: true }
23    : { course_id: meta.course_id, version: version };
24  
25  const content = await mongo.collection('courses_content').findOne(contentQuery);
26  
27  // 4. Elasticsearch: related courses
28  const related = await es.search({
29    index: 'courses',
30    query: {
31      more_like_this: {
32        fields: ['title', 'tags'],
33        like: [{ _index: 'courses', _id: meta.course_id }],
34        min_term_freq: 1
35      }
36    },
37    size: 4
38  });
39  
40  // 5. Combine
41  const result = {
42    meta,
43    content: content?.modules || [],
44    related: related.hits.hits.map(h => h._source)
45  };
46  
47  // 6. Cache
48  await redis.setex(cacheKey, 300, JSON.stringify(result));
49  return result;
50}

📋 Part 9: Implementation Checklist

🎯 Quick Reference: Essential Commands

sqlCopy
1-- PostgreSQL: Core setup
2CREATE TABLE users (...);
3CREATE TABLE courses (...);
4CREATE TABLE enrollments (...);
5CREATE INDEX idx_courses_status ON courses(status);
6CREATE INDEX idx_enrollments_user ON enrollments(user_id);
7
8-- Trigger for denormalized counter
9CREATE TRIGGER trg_enrollment_insert AFTER INSERT ON enrollments
10FOR EACH ROW EXECUTE FUNCTION increment_enrolled_count();
11
12-- Materialized view for reads
13CREATE MATERIALIZED VIEW course_catalog AS SELECT ...;
14REFRESH MATERIALIZED VIEW CONCURRENTLY course_catalog;

javascriptCopy
1// MongoDB: Content store
2db.courses_content.insertOne({ _id: "course-1", modules: [...], version: 1, is_latest: true });
3db.courses_content.createIndex({ slug: 1 }, { unique: true });
4db.courses_content.createIndex({ course_id: 1, is_latest: 1 });
5
6// Activity logs
7db.user_activity_logs.insertOne({ user_id: "u1", events: [...] });
8db.user_activity_logs.createIndex({ "events.timestamp": -1 });

bashCopy
1# Redis: Cache layer
2SET "session:user-1" '{"role":"student"}' EX 86400
3SETEX "course:detail:js-fundamentals" 300 '{"title":"JS Fundamentals"}'
4DEL "course:detail:js-fundamentals"

jsonCopy
1// Elasticsearch: Search
2PUT /courses/_doc/course-1 { "title": "JS Fundamentals", "tags": ["javascript"] }
3GET /courses/_search { "query": { "match": { "title": "javascript" } } }