数据库设计 - 核心系统 (Release 1)#

本文档描述平台核心功能的数据库设计，包括用户、课程、章节、任务、进度追踪和课程报名。

Release 2 道馆系统: 参见 数据库设计 - 道馆系统

ER 图#

erDiagram
    User ||--o{ MissionProgress : has
    User ||--o{ CourseEnrollment : has
    User ||--o{ UserSkill : has
    Course ||--o{ Chapter : contains
    Course ||--o{ CourseEnrollment : enrolled_by
    Chapter ||--o{ Mission : contains
    Chapter ||--o{ Gym : contains
    Mission ||--o{ MissionProgress : tracked_by

    User {
        bigint id PK
        string email UK
        string password
        string name
        string auth_provider "LOCAL/GOOGLE"
        string google_id
        string role "STUDENT/TEACHER"
        int level
        int exp
        string title
        string avatar_url
        string nickname
        string gender
        string profession
        date birthday
        string region
        string github_url
        timestamp created_at
        timestamp updated_at
    }

    Course {
        bigint id PK
        string name
        text description
        string image_url
        decimal price
        string level
        string instructor
        int total_duration
        int student_count
        string category
        boolean has_free_preview
    }

    Chapter {
        bigint id PK
        bigint course_id FK
        string title
        int order_index
    }

    Mission {
        bigint id PK
        bigint chapter_id FK
        string title
        string type "VIDEO/ARTICLE/SURVEY"
        text content
        string video_url
        int exp_reward
        int order_index
        boolean is_free_preview
    }

    MissionProgress {
        bigint id PK
        bigint user_id FK
        bigint mission_id FK
        int progress
        boolean completed
        boolean reward_claimed
        int last_watched_position
        int video_duration
        int watched_time
        timestamp completed_at
        timestamp updated_at
    }

    CourseEnrollment {
        bigint id PK
        bigint user_id FK
        bigint course_id FK
        string type "FREE/PURCHASED/GIFTED"
        string status "PENDING/COMPLETED/CANCELLED"
        timestamp enrolled_at
        timestamp completed_at
    }

    Gym {
        bigint id PK
        bigint chapter_id FK
        string name
        string category
        string difficulty
    }

注意: Gym 及其相关实体的完整定义参见 数据库设计 - 道馆系统

表结构#

users 表#

索引:

• PRIMARY KEY (id)
• UNIQUE INDEX (email)
• INDEX (google_id)

courses 表#

关联表:

• course_features (ElementCollection): 存储课程启用的功能列表

chapters 表#

外键:

• FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE

说明:

• 每个章节可包含多个任务 (missions) 和多个道馆 (gyms)
• 道馆系统详见 数据库设计 - 道馆系统

missions 表#

外键:

• FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE

mission_progress 表#

索引:

• UNIQUE INDEX (user_id, mission_id)
• FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
• FOREIGN KEY (mission_id) REFERENCES missions(id) ON DELETE CASCADE

course_enrollments 表#

索引:

• UNIQUE INDEX (user_id, course_id)
• FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
• FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE

数据库优化#

索引策略#

1. 主键索引: 所有表都有自增主键
2. 唯一索引:
   • users.email
   • (user_id, mission_id) 组合
   • (user_id, course_id) 组合
3. 外键索引: 自动创建

查询优化#

-- 高效查询用户排行榜（已建立索引）
SELECT id, name, level, exp, title, avatar_url
FROM users
ORDER BY exp DESC
LIMIT 100;

-- 高效查询用户进度（组合索引）
SELECT mp.*
FROM mission_progress mp
WHERE mp.user_id = ? AND mp.mission_id = ?;

-- 课程详情（使用 JOIN 优化）
SELECT c.*, ch.*, m.*
FROM courses c
LEFT JOIN chapters ch ON ch.course_id = c.id
LEFT JOIN missions m ON m.chapter_id = ch.id
WHERE c.id = ?
ORDER BY ch.order_index, m.id;

数据初始化#

系统启动时自动初始化种子数据（参见 DataInitializer.java）。