📘 Courses Table Design Tutorial: Complete SQL Schema Guide
🎯 What You'll Learn
By the end of this tutorial, you will:
- Understand every column in a production-ready
coursestable - Know why specific data types and constraints are chosen
- Write INSERT, SELECT, UPDATE, and DELETE commands for course management
- Optimize course queries with strategic indexes
- Apply real-world best practices for course catalog databases
📐 Part 1: Understanding the Courses Table Schema
Why the Courses Table Matters
The courses table is the heart of any e-learning platform. Every enrollment, module, lesson, and review connects back to this table. A poorly designed courses table causes slow searches, broken slugs, and data inconsistency.
Complete Schema Definition
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 REFERENCES categories(category_id), 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);
🔍 Part 2: Column-by-Column Deep Dive
course_id — UUID Primary Key
1course_id UUID PRIMARY KEY DEFAULT gen_random_uuid()
Why UUID instead of INTEGER?
- Security: Prevents attackers from guessing course IDs (
/course/1,/course/2) - Distributed systems: Safe to generate across multiple servers
- Merge friendly: No conflicts when importing/exporting data
Example:
1-- See the generated UUID 2INSERT INTO courses (instructor_id, title, slug, status) 3VALUES ( 4 'a1b2c3d4-e5f6-7890-abcd-ef1234567890', 5 'Python for Data Science', 6 'python-data-science', 7 'published' 8) 9RETURNING course_id; 10-- Returns: f47ac10b-58cc-4372-a567-0e02b2c3d479
instructor_id — Foreign Key to Users
1instructor_id UUID NOT NULL REFERENCES users(user_id)
What it does: Links every course to the instructor who created it.
Why NOT NULL? Every course must have an instructor. No orphan courses allowed.
Example — Get courses with instructor details:
1SELECT 2 c.course_id, 3 c.title, 4 c.slug, 5 u.full_name AS instructor_name, 6 u.email AS instructor_email 7FROM courses c 8JOIN users u ON c.instructor_id = u.user_id 9WHERE c.status = 'published' 10LIMIT 5;
Example — Find all courses by a specific instructor:
1SELECT title, level, price, enrolled_count, rating_avg 2FROM courses 3WHERE instructor_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890' 4AND status = 'published' 5ORDER BY enrolled_count DESC;
title & slug — Course Identity
1title VARCHAR(200) NOT NULL, 2slug VARCHAR(200) UNIQUE NOT NULL
| Field | Purpose | Example |
|---|---|---|
title | Human-readable name | "Complete React Developer Course" |
slug | URL-friendly identifier | complete-react-developer |
Why both? The title is for display; the slug is for URLs (/course/complete-react-developer).
Slug generation rule: Lowercase, no spaces, hyphens only.
Example:
1INSERT INTO courses (instructor_id, title, slug, description, status) 2VALUES ( 3 'instructor-uuid-here', 4 'Machine Learning A-Z™: Hands-On Python & R', 5 'machine-learning-a-z-python-r', 6 'Learn to create machine learning algorithms in Python and R from two data science experts.', 7 'published' 8); 9 10-- Try inserting duplicate slug (will FAIL) 11INSERT INTO courses (instructor_id, title, slug, status) 12VALUES ( 13 'instructor-uuid-here', 14 'Duplicate Title', 15 'machine-learning-a-z-python-r', -- ❌ ERROR: duplicate key value 16 'Description', 17 'draft' 18);
description & short_desc — Content Hierarchy
1description TEXT, 2short_desc VARCHAR(300)
| Field | Length | Use Case |
|---|---|---|
short_desc | 300 chars | Course card, search results, meta description |
description | Unlimited | Full course page, sales copy, curriculum details |
Example:
1UPDATE courses 2SET 3 short_desc = 'Master React 18, Redux, Hooks, and GraphQL. Build 5 real-world projects.', 4 description = 'This comprehensive bootcamp covers everything from JSX basics to advanced state management...' 5WHERE slug = 'complete-react-developer';
thumbnail_url — Visual Asset
1thumbnail_url VARCHAR(500)
Best practice: Store the CDN URL, not the image itself. Databases are bad at storing files.
Example:
1UPDATE courses 2SET thumbnail_url = 'https://cdn.example.com/thumbnails/react-course-1200x675.jpg' 3WHERE course_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
level — Difficulty Classification
1level VARCHAR(20) DEFAULT 'beginner' 2CHECK (level IN ('beginner', 'intermediate', 'advanced'))
Why CHECK constraint? Prevents typos like beginer or intermidiate from entering your database.
Example — Filter by level:
1-- Get all intermediate courses 2SELECT title, slug, price, rating_avg 3FROM courses 4WHERE level = 'intermediate' 5AND status = 'published' 6ORDER BY rating_avg DESC; 7 8-- Count courses by level 9SELECT 10 level, 11 COUNT(*) AS course_count, 12 ROUND(AVG(price), 2) AS avg_price 13FROM courses 14WHERE status = 'published' 15GROUP BY level;
category_id — Course Categorization
1category_id UUID REFERENCES categories(category_id)
Why nullable? A course might be uncategorized during draft phase.
Example — Get courses with category names:
1SELECT 2 c.title, 3 c.slug, 4 cat.name AS category 5FROM courses c 6LEFT JOIN categories cat ON c.category_id = cat.category_id 7WHERE c.status = 'published' 8ORDER BY cat.name, c.title;
price & currency — Monetization
1price DECIMAL(10,2) DEFAULT 0.00, 2currency VARCHAR(3) DEFAULT 'USD'
Why DECIMAL not FLOAT? Money requires exact precision. FLOAT causes rounding errors (e.g., 19.99 might become 19.989999).
Example:
1-- Insert a paid course 2INSERT INTO courses (instructor_id, title, slug, price, currency, status) 3VALUES ( 4 'instructor-uuid', 5 'Advanced Kubernetes', 6 'advanced-kubernetes', 7 89.99, 8 'USD', 9 'published' 10); 11 12-- Find free courses 13SELECT title, slug, enrolled_count 14FROM courses 15WHERE price = 0.00 16AND status = 'published' 17ORDER BY enrolled_count DESC; 18 19-- Revenue report for an instructor 20SELECT 21 c.title, 22 c.price, 23 c.enrolled_count, 24 (c.price * c.enrolled_count) AS estimated_revenue 25FROM courses c 26WHERE c.instructor_id = 'instructor-uuid' 27AND c.status = 'published';
status — Publication Workflow
1status VARCHAR(20) DEFAULT 'draft' 2CHECK (status IN ('draft', 'published', 'archived'))
| Status | Meaning |
|---|---|
draft | Instructor is still building the course |
published | Visible to students, can be enrolled |
archived | Old course, no new enrollments |
Example — Publish a course:
1-- Move from draft to published 2UPDATE courses 3SET 4 status = 'published', 5 published_at = CURRENT_TIMESTAMP 6WHERE course_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479' 7AND status = 'draft'; 8 9-- Verify 10SELECT title, status, published_at 11FROM courses 12WHERE course_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
duration_hours — Course Length
1duration_hours DECIMAL(5,1)
Why DECIMAL(5,1)? Allows up to 999.9 hours with 1 decimal place (e.g., 12.5 hours).
Example:
1-- Update course duration 2UPDATE courses SET duration_hours = 24.5 WHERE course_id = 'uuid'; 3 4-- Find courses by length 5SELECT title, duration_hours 6FROM courses 7WHERE duration_hours BETWEEN 10 AND 20 8AND status = 'published' 9ORDER BY duration_hours;
enrolled_count & rating_avg — Cached Metrics
1enrolled_count INT DEFAULT 0, 2rating_avg DECIMAL(2,1) DEFAULT 0.0
Why store these instead of calculating on the fly?
- Course catalog pages need to display these instantly
- Calculating
COUNT(enrollments)andAVG(reviews)for every page load is slow - Update via triggers or background jobs when data changes
Example — Update after new enrollment:
1UPDATE courses 2SET enrolled_count = enrolled_count + 1 3WHERE course_id = 'course-uuid';
created_at & published_at — Timestamps
1created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 2published_at TIMESTAMP
| Field | Auto-set? | Purpose |
|---|---|---|
created_at | Yes | When the draft was first created |
published_at | Manual | When the course went live |
Example — Find recently published courses:
1SELECT title, slug, published_at 2FROM courses 3WHERE status = 'published' 4AND published_at >= CURRENT_DATE - INTERVAL '30 days' 5ORDER BY published_at DESC;
⚡ Part 3: Performance Indexes
Add these indexes for a production course catalog:
1-- Essential indexes for the courses table 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_level ON courses(level); 6CREATE INDEX idx_courses_slug ON courses(slug); 7CREATE INDEX idx_courses_price ON courses(price); 8 9-- Composite index for filtered browsing 10CREATE INDEX idx_courses_browse ON courses(status, level, category_id); 11 12-- Partial index: only published courses (most queried) 13CREATE INDEX idx_courses_published ON courses(status, category_id, level) 14WHERE status = 'published';
🧪 Part 4: Hands-On Learning Exercises
Exercise 1: Create a Complete Course Catalog
Write SQL to insert 5 courses across different levels and categories.
Solution:
1INSERT INTO courses (instructor_id, title, slug, short_desc, description, level, category_id, price, status, duration_hours) 2VALUES 3('inst-1', 'HTML & CSS for Beginners', 'html-css-beginners', 'Learn web development from scratch', 'Full description...', 'beginner', 'cat-web', 0.00, 'published', 8.5), 4('inst-1', 'JavaScript Fundamentals', 'javascript-fundamentals', 'Master JS ES6+ syntax', 'Full description...', 'beginner', 'cat-web', 29.99, 'published', 12.0), 5('inst-2', 'React Advanced Patterns', 'react-advanced-patterns', 'Hooks, context, and performance', 'Full description...', 'advanced', 'cat-web', 79.99, 'published', 18.5), 6('inst-2', 'Node.js Microservices', 'nodejs-microservices', 'Build scalable backend systems', 'Full description...', 'intermediate', 'cat-backend', 69.99, 'published', 22.0), 7('inst-3', 'UI/UX Design Masterclass', 'ui-ux-masterclass', 'Design beautiful interfaces', 'Full description...', 'intermediate', 'cat-design', 49.99, 'draft', 15.0);
Exercise 2: Build a Course Browse Query
Write a query that shows:
- Only published courses
- With instructor name
- With category name
- Sorted by highest rating
Solution:
1SELECT 2 c.title, 3 c.slug, 4 c.level, 5 c.price, 6 c.rating_avg, 7 c.enrolled_count, 8 u.full_name AS instructor, 9 cat.name AS category 10FROM courses c 11JOIN users u ON c.instructor_id = u.user_id 12LEFT JOIN categories cat ON c.category_id = cat.category_id 13WHERE c.status = 'published' 14ORDER BY c.rating_avg DESC, c.enrolled_count DESC 15LIMIT 20;
Exercise 3: Publish a Draft Course
Update a course from draft to published and set the published timestamp.
Solution:
1UPDATE courses 2SET 3 status = 'published', 4 published_at = CURRENT_TIMESTAMP 5WHERE slug = 'ui-ux-masterclass' 6RETURNING course_id, title, status, published_at;
🏗️ Part 5: Real-World Best Practices
| Practice | Implementation | Benefit |
|---|---|---|
| Never delete courses | Use status = 'archived' | Preserve enrollment history |
| Slug immutability | Once published, never change slug | Prevents broken bookmarks |
| Price in smallest currency unit | Or use DECIMAL(10,2) | Avoids floating-point errors |
| Denormalized counters | enrolled_count, rating_avg | Fast catalog page loads |
| Soft constraints | CHECK on level, status | Data integrity at DB level |
| Separate draft/published | Different queries, different indexes | Optimized performance |
📊 Quick Reference: Most Used Queries
1-- 1. Get single course by slug 2SELECT * FROM courses WHERE slug = 'complete-react-developer'; 3 4-- 2. Get instructor's course dashboard 5SELECT 6 title, 7 status, 8 enrolled_count, 9 rating_avg, 10 (price * enrolled_count) AS revenue 11FROM courses 12WHERE instructor_id = 'uuid' 13ORDER BY created_at DESC; 14 15-- 3. Search courses by title 16SELECT title, slug, level, price 17FROM courses 18WHERE title ILIKE '%python%' 19AND status = 'published'; 20 21-- 4. Get course statistics 22SELECT 23 COUNT(*) AS total_courses, 24 COUNT(*) FILTER (WHERE status = 'published') AS published, 25 COUNT(*) FILTER (WHERE status = 'draft') AS drafts, 26 ROUND(AVG(price), 2) AS avg_price 27FROM courses 28WHERE instructor_id = 'uuid';
This courses table design gives you a scalable, secure, and performant foundation for any e-learning platform. Start with this schema, add the indexes, and scale confidently.