数据库设计 - 道馆系统 (Release 2)#

本文档描述 Release 2 引入的道馆挑战系统的数据库设计。

ER 图#

erDiagram
    Chapter ||--o{ Gym : contains
    Gym ||--o{ GymPrerequisite : has
    Gym ||--o{ Challenge : contains
    Mission ||--o{ GymPrerequisite : required_by
    Challenge ||--o{ ChallengeField : defines
    Challenge ||--o{ Submission : has
    User ||--o{ Submission : submits
    User ||--o{ GradeResult : graded
    User ||--o{ UserSkill : has
    Submission ||--o{ SubmissionFile : contains
    Submission ||--|| GradeResult : graded_with
    GradeResult ||--o{ SkillRating : has

    Gym {
        bigint id PK
        bigint chapter_id FK
        string name
        text description
        string category
        string difficulty
        int order_index
    }

    GymPrerequisite {
        bigint id PK
        bigint gym_id FK
        bigint required_mission_id FK
        boolean require_reward_claimed
    }

    Challenge {
        bigint id PK
        bigint gym_id FK
        string title
        text description
        string type
        int order_index
        int exp_reward
    }

    ChallengeField {
        bigint id PK
        bigint challenge_id FK
        string field_key
        string field_label
        string field_type
        boolean required
        int order_index
    }

    Submission {
        bigint id PK
        bigint challenge_id FK
        bigint user_id FK
        string status
        timestamp submitted_at
        timestamp last_modified_at
    }

    SubmissionFile {
        bigint id PK
        bigint submission_id FK
        string field_key
        string file_name
        string file_url
        bigint file_size
        timestamp uploaded_at
    }

    GradeResult {
        bigint id PK
        bigint submission_id FK
        bigint graded_by_user_id FK
        int score
        text feedback
        boolean passed
        timestamp graded_at
        timestamp last_modified_at
    }

    SkillRating {
        bigint id PK
        bigint grade_result_id FK
        string category
        string level
        text comment
    }

    UserSkill {
        bigint id PK
        bigint user_id FK
        string category
        string level
        timestamp updated_at
    }

表结构#

gyms 表#

外键:

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

说明:

• category: 白段道馆 (WHITE) 适合初学者，黑段道馆 (BLACK) 适合进阶学员
• difficulty: 难度等级，前端显示为星级 (★ 到 ★★★★)

gym_prerequisites 表#

外键:

• FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE CASCADE
• FOREIGN KEY (required_mission_id) REFERENCES missions(id) ON DELETE CASCADE

说明:

• 道馆解锁逻辑：必须完成所有前置任务
• require_reward_claimed = true: 不仅要完成任务，还要领取经验值
• require_reward_claimed = false: 只需观看完成即可

challenges 表#

外键:

• FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE CASCADE

说明:

• INSTANT_CHALLENGE: 速战速决，预估 3 天，可重复提交
• PRACTICAL_CHALLENGE: 实战演练，预估 14 天，只能提交一次

challenge_fields 表#

外键:

• FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE

说明:

• 定义每个挑战需要提交的文件类型
• IMAGE: 图片文件 (.png, .jpg, .jpeg)，最大 5MB
• ZIP: 压缩文件 (.zip)，最大 10MB

submissions 表#

索引:

• UNIQUE INDEX (user_id, challenge_id)

外键:

• FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE
• FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

状态值:

• DRAFT: 草稿
• SUBMITTED: 已提交，等待批改
• GRADING: 批改中
• PASSED: 通过
• FAILED: 未通过
• GRADED: 已批改（包含通过和未通过）

submission_files 表#

外键:

• FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE

说明:

• 文件存储在本地文件系统：uploads/submissions/{userId}/{challengeId}/{timestamp}_{uuid}_{filename}
• 未来可迁移到 MinIO/S3

grade_results 表#

外键:

• FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
• FOREIGN KEY (graded_by_user_id) REFERENCES users(id)

说明:

• 分数 ≥ 60 视为通过，自动授予经验值奖励
• 通过后自动升级用户等级

skill_ratings 表#

外键:

• FOREIGN KEY (grade_result_id) REFERENCES grade_results(id) ON DELETE CASCADE

技能类别 (SkillCategory):

1. DESIGN_PATTERN_FORM - 熟悉设计模式的 Form
2. STRUCTURE_BEHAVIOR_SEPARATION - 区分结构与行为
3. DEVELOPMENT_PROFICIENCY - 游刃有余的开发能力
4. REQUIREMENT_STRUCTURING - 需求结构化分析
5. ABSTRACTION_ABILITY - 抽象/萃取能力
6. WELL_DEFINED_CONTEXT - 建立 Well-Defined Context

技能等级 (SkillLevel) - 34 个等级: F-, F, F+, E-, E, E+, D-, D, D+, C-, C, C+, B-, B, B+, A-, A, A+, AA-, AA, AA+, AAA-, AAA, AAA+, S-, S, S+, SS-, SS, SS+, SSS-, SSS, SSS+, ACE

user_skills 表#

索引:

• UNIQUE INDEX (user_id, category)

外键:

• FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

说明:

• 记录用户在每个技能类别的当前等级
• 每次批改后自动更新对应技能等级
• 每个用户每个类别只有一条记录

枚举类型#

GymCategory (道馆类别)#

public enum GymCategory {
    WHITE,  // 白段道馆 (入门级)
    BLACK   // 黑段道馆 (进阶级)
}

GymDifficulty (道馆难度)#

public enum GymDifficulty {
    BEGINNER,      // ★
    INTERMEDIATE,  // ★★
    ADVANCED,      // ★★★
    EXPERT         // ★★★★
}

ChallengeType (挑战类型)#

public enum ChallengeType {
    INSTANT_CHALLENGE,    // 速战速决 (3 天)
    PRACTICAL_CHALLENGE   // 实战演练 (14 天)
}

FieldType (字段类型)#

public enum FieldType {
    IMAGE,  // 图片 (.png, .jpg, .jpeg, 最大 5MB)
    ZIP     // 压缩文件 (.zip, 最大 10MB)
}

SubmissionStatus (提交状态)#

public enum SubmissionStatus {
    DRAFT,      // 草稿
    SUBMITTED,  // 已提交
    GRADING,    // 批改中
    PASSED,     // 通过
    FAILED,     // 未通过
    GRADED      // 已批改
}

业务逻辑说明#

道馆解锁逻辑#

1. 系统检查 gym_prerequisites 表中该道馆的所有前置条件
2. 对于每个前置任务：
   • 检查 mission_progress 表中用户是否完成该任务 (progress >= 100)
   • 如果 require_reward_claimed = true，检查 reward_claimed 是否为 true
3. 只有所有前置条件都满足时，道馆才解锁

挑战提交流程#

1. 学生上传所需文件（根据 challenge_fields 定义）
2. 系统验证文件类型和大小
3. 创建 Submission 记录，状态为 SUBMITTED
4. 保存文件到 submission_files 表
5. 等待教师批改

批改与评级流程#

1. 教师下载提交文件进行审阅
2. 提交批改结果：
   • 分数 (0-100)
   • 文字反馈
   • 6 个技能类别的评级
3. 创建 GradeResult 记录
4. 创建 6 条 SkillRating 记录
5. 如果分数 ≥ 60：
   • 用户获得 challenge.exp_reward 经验值
   • 自动调用 User.updateLevel() 升级
   • Submission.status 更新为 GRADED
6. 更新 user_skills 表：
   • 每个技能类别的等级更新为最新评级

文件存储策略#

当前实现 (Release 2):

• 本地文件系统存储
• 路径格式：uploads/submissions/{userId}/{challengeId}/{timestamp}_{uuid}_{filename}
• Docker 卷挂载确保数据持久化

未来扩展:

• 可迁移到 MinIO/S3 对象存储
• 支持 CDN 加速访问

数据库优化#

索引策略#

1. 唯一索引:

   • (user_id, challenge_id) - 防止重复提交
   • (user_id, category) - 用户技能唯一性
   • submission_id (grade_results) - 一对一关系

2. 外键索引: 自动创建

3. 查询优化索引:

   • gyms(chapter_id, order_index) - 按章节查询道馆
   • challenges(gym_id, order_index) - 按道馆查询挑战

常见查询示例#

-- 检查道馆是否解锁
SELECT gp.id, gp.require_reward_claimed, mp.progress, mp.reward_claimed
FROM gym_prerequisites gp
LEFT JOIN mission_progress mp ON mp.mission_id = gp.required_mission_id AND mp.user_id = ?
WHERE gp.gym_id = ?;

-- 查询用户的提交历史
SELECT s.*, c.title AS challenge_title, gr.score, gr.passed
FROM submissions s
JOIN challenges c ON c.id = s.challenge_id
LEFT JOIN grade_results gr ON gr.submission_id = s.id
WHERE s.user_id = ?
ORDER BY s.submitted_at DESC;

-- 查询用户当前技能等级
SELECT category, level, updated_at
FROM user_skills
WHERE user_id = ?
ORDER BY category;

-- 查询待批改的提交
SELECT s.*, u.name AS student_name, c.title AS challenge_title
FROM submissions s
JOIN users u ON u.id = s.user_id
JOIN challenges c ON c.id = s.challenge_id
WHERE s.status IN ('SUBMITTED', 'GRADING')
ORDER BY s.submitted_at ASC;

数据完整性#

级联删除规则#

• 删除 Chapter → 级联删除 Gym → 级联删除 Challenge → 级联删除 Submission
• 删除 Submission → 级联删除 SubmissionFile 和 GradeResult
• 删除 GradeResult → 级联删除 SkillRating
• 删除 User → 级联删除 Submission 和 UserSkill

唯一性约束#

• (user_id, challenge_id): 每个用户每个挑战只能有一次提交
• (user_id, category): 每个用户每个技能类别只有一条记录
• submission_id (grade_results): 每个提交只能有一个批改结果