elearning/docs/api-docs/ER

Database Schema (ERD) Documentation

📊 Entity-Relationship Diagram

Online Visualization

View the interactive ERD diagram:

• dbdiagram.io: dbdiagram.io

📋 Database Overview

Total Tables: 17

Category	Tables	Count
Core	users, categories, courses, chapters, lessons	5
Content	quizzes, questions, choices, lesson_attachments	4
Progress	enrollments, lesson_progress, quiz_attempts	3
Communication	announcements, announcement_attachments	2
Commerce (อนาคต)	orders, payments, withdrawals	3

---

🎯 Key Features

1. Multi-language Support

• Uses jsonb fields for Thai/English content
• Fields: title, content, description

2. Video Progress Tracking

• Track playback position per student
• Auto-complete at 90% watched
• Resume from last position

3. Lesson Prerequisites

• Sequential learning control
• Quiz pass requirements
• Flexible ordering

4. File Attachments

• Multiple files per lesson
• Support for PDF, DOCX, ZIP, images
• Sortable with descriptions

5. Quiz System

• Multiple attempts with cooldown
• Score policies (HIGHEST/LATEST/FIRST/AVERAGE)
• Detailed attempt history

---

📊 Main Entities

Users

• Roles: STUDENT, INSTRUCTOR, ADMIN
• Profile information
• Authentication data

Courses

• Multi-language titles and descriptions
• Categories and pricing
• Instructor ownership
• Approval workflow

Chapters & Lessons

• Hierarchical structure
• Sort ordering
• Multiple lesson types (video, pdf, text, quiz)
• Prerequisites support

Progress Tracking

• Enrollment status
• Lesson completion
• Video playback position
• Quiz attempts and scores

---

🔗 Relationships

users (1) ──── (many) courses [instructor]
users (1) ──── (many) enrollments
users (1) ──── (many) lesson_progress
users (1) ──── (many) quiz_attempts

courses (1) ──── (many) chapters
courses (1) ──── (many) enrollments
courses (1) ──── (many) announcements

chapters (1) ──── (many) lessons

lessons (1) ──── (many) lesson_attachments
lessons (1) ──── (many) lesson_progress
lessons (1) ──── (many) quizzes

quizzes (1) ──── (many) questions
quizzes (1) ──── (many) quiz_attempts

questions (1) ──── (many) choices

---

🎨 How to Visualize

Using dbdiagram.io

1. Open dbdiagram.io

   • Go to https://dbdiagram.io/d

2. Import Schema

   • Click "Import" button (top right)
   • Select "From DBML"
   • Copy contents from ERD_v2_improved.txt
   • Paste into the editor
   • Click "Import"

3. View Diagram

   • Zoom in/out with mouse wheel
   • Drag to pan
   • Click tables to highlight relationships
   • Export as PNG/PDF/SQL

Alternative Tools

• DBeaver: Import SQL and generate ER diagram
• MySQL Workbench: Reverse engineer from database
• pgAdmin: PostgreSQL schema visualization
• draw.io: Manual diagram creation

---

📝 Table Descriptions

Core Tables

users

• User accounts and authentication
• Roles: STUDENT, INSTRUCTOR, ADMIN
• Profile information

categories

• Course categorization
• Multi-language names

courses

• Course information and metadata
• Pricing and approval status
• Instructor ownership

chapters

• Course structure organization
• Sortable within courses

lessons

• Learning content units
• Types: video, pdf, text, quiz
• Prerequisites and sequencing

Content Tables

quizzes

• Assessment configuration
• Attempt limits and cooldown
• Passing score requirements

questions

• Quiz questions
• Types: multiple_choice, true_false
• Point values

choices

• Answer options for questions
• Correct answer marking

lesson_attachments

• Supplementary files
• PDFs, documents, code samples
• Multi-language descriptions

Progress Tables

enrollments

• Student course registrations
• Progress tracking
• Certificate issuance

lesson_progress

• Lesson completion status
• Video playback position
• Watch history

quiz_attempts

• Quiz submission records
• Scores and answers
• Attempt history

Communication Tables

announcements

• Course announcements
• Pinning and scheduling
• Multi-language content

announcement_attachments

• Announcement file attachments

---

🔧 Database Constraints

Primary Keys

• All tables use auto-incrementing integer PKs

Foreign Keys

• Maintain referential integrity
• Cascade deletes where appropriate

Unique Constraints

• (user_id, course_id) in enrollments
• (user_id, lesson_id) in lesson_progress
• Email in users

Check Constraints (via notes)

• Progress percentages: 0-100
• Scores: 0-100
• Sort orders: >= 0
• Prices: >= 0

Indexes

• Single column indexes on FKs
• Composite indexes for common queries
• JSONB GIN indexes for multi-language fields

---

🚀 Migration Notes

PostgreSQL Extensions Required

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- for text search

JSONB Usage

-- Multi-language fields
title JSONB  -- {"th": "...", "en": "..."}

-- Query examples
WHERE title->>'th' LIKE '%Python%'
WHERE title @> '{"en": "Introduction"}'

Soft Delete Pattern

-- Recommended for important tables
ALTER TABLE courses ADD COLUMN deleted_at TIMESTAMP;
CREATE INDEX idx_courses_deleted ON courses(deleted_at) WHERE deleted_at IS NULL;

---

📈 Performance Considerations

Indexes Created

• All foreign keys
• Composite indexes for (course_id, sort_order)
• User email for login
• Course status for filtering
• Last watched timestamp for recent videos

Query Optimization

• Use SELECT specific columns
• Implement pagination
• Use database views for complex queries

---

🔗 Related Documentation

• API Endpoints - REST API reference
• Edge Cases - Special scenarios
• Development Setup - Environment configuration

---

📊 Statistics

• Total Tables: 17
• Total Relationships: 20+
• Multi-language Fields: 15+
• Indexed Columns: 40+
• JSONB Fields: 15+

---

🎯 Next Steps

1. ✅ Review ERD schema
2. ⏳ Create PostgreSQL database
3. ⏳ Run migrations
4. ⏳ Seed initial data
5. ⏳ Implement API endpoints
6. ⏳ Test with sample data

---

Last Updated: 2024-12-24
Version: 2.0 (Improved)