012-kaopeilian/backend/database_schema_unified.md

考培练系统统一数据库架构设计

数据库基本信息

• 数据库名称：kaopeilian
• 字符集：utf8mb4
• 排序规则：utf8mb4_unicode_ci
• 存储引擎：InnoDB

一、用户管理模块

1.1 用户表 (users)

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `email` VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    `phone` VARCHAR(20) UNIQUE COMMENT '手机号',
    `password_hash` VARCHAR(200) NOT NULL COMMENT '密码哈希',
    `full_name` VARCHAR(100) COMMENT '姓名',
    `avatar_url` VARCHAR(500) COMMENT '头像URL',
    `bio` TEXT COMMENT '个人简介',
    `role` VARCHAR(20) DEFAULT 'trainee' COMMENT '系统角色: admin, manager, trainee',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否激活',
    `is_verified` BOOLEAN DEFAULT FALSE COMMENT '是否验证',
    `last_login_at` DATETIME COMMENT '最后登录时间',
    `password_changed_at` DATETIME COMMENT '密码修改时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

1.2 团队表 (teams)

CREATE TABLE `teams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE COMMENT '团队名称',
    `code` VARCHAR(50) NOT NULL UNIQUE COMMENT '团队代码',
    `description` TEXT COMMENT '团队描述',
    `team_type` VARCHAR(50) DEFAULT 'department' COMMENT '团队类型: department, project, study_group',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否激活',
    `leader_id` INT COMMENT '负责人ID',
    `parent_id` INT COMMENT '父团队ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (leader_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES teams(id) ON DELETE CASCADE,
    INDEX idx_team_type (team_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队表';

1.3 用户团队关联表 (user_teams)

CREATE TABLE `user_teams` (
    `user_id` INT NOT NULL,
    `team_id` INT NOT NULL,
    `role` VARCHAR(50) DEFAULT 'member' COMMENT '团队角色: member, leader',
    `joined_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
    PRIMARY KEY (user_id, team_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户团队关联表';

二、课程管理模块

2.1 课程表 (courses)

CREATE TABLE `courses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL COMMENT '课程名称',
    `description` TEXT COMMENT '课程描述',
    `category` ENUM('technology', 'management', 'business', 'general') DEFAULT 'general' COMMENT '课程分类',
    `status` ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '课程状态',
    `cover_image` VARCHAR(500) COMMENT '封面图片URL',
    `duration_hours` FLOAT COMMENT '课程时长(小时)',
    `difficulty_level` INT COMMENT '难度等级(1-5)',
    `tags` JSON COMMENT '标签列表',
    `published_at` DATETIME COMMENT '发布时间',
    `publisher_id` INT COMMENT '发布人ID',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_featured` BOOLEAN DEFAULT FALSE COMMENT '是否推荐',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_by` INT COMMENT '创建人ID',
    `updated_by` INT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_is_featured (is_featured),
    INDEX idx_is_deleted (is_deleted),
    INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程表';

2.2 课程资料表 (course_materials)

CREATE TABLE `course_materials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `name` VARCHAR(200) NOT NULL COMMENT '资料名称',
    `description` TEXT COMMENT '资料描述',
    `file_url` VARCHAR(500) NOT NULL COMMENT '文件URL',
    `file_type` VARCHAR(50) NOT NULL COMMENT '文件类型',
    `file_size` INT NOT NULL COMMENT '文件大小(字节)',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程资料表';

2.3 知识点表 (knowledge_points)

CREATE TABLE `knowledge_points` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `name` VARCHAR(200) NOT NULL COMMENT '知识点名称',
    `description` TEXT COMMENT '知识点描述',
    `parent_id` INT COMMENT '父知识点ID',
    `level` INT DEFAULT 1 COMMENT '层级深度',
    `path` VARCHAR(500) COMMENT '路径(如: 1.2.3)',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `weight` FLOAT DEFAULT 1.0 COMMENT '权重',
    `is_required` BOOLEAN DEFAULT TRUE COMMENT '是否必修',
    `estimated_hours` FLOAT COMMENT '预计学习时间(小时)',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES knowledge_points(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_is_deleted (is_deleted),
    INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识点表';

2.4 成长路径表 (growth_paths)

CREATE TABLE `growth_paths` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL COMMENT '路径名称',
    `description` TEXT COMMENT '路径描述',
    `target_role` VARCHAR(100) COMMENT '目标角色',
    `courses` JSON COMMENT '课程列表[{course_id, order, is_required}]',
    `estimated_duration_days` INT COMMENT '预计完成天数',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active),
    INDEX idx_is_deleted (is_deleted),
    INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成长路径表';

三、考试模块

3.1 题目表 (questions)

CREATE TABLE `questions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `question_type` VARCHAR(20) NOT NULL COMMENT '题目类型: single_choice, multiple_choice, true_false, fill_blank, essay',
    `title` TEXT NOT NULL COMMENT '题目标题',
    `content` TEXT COMMENT '题目内容',
    `options` JSON COMMENT '选项（JSON格式）',
    `correct_answer` TEXT NOT NULL COMMENT '正确答案',
    `explanation` TEXT COMMENT '答案解释',
    `score` FLOAT DEFAULT 10.0 COMMENT '分值',
    `difficulty` VARCHAR(10) DEFAULT 'medium' COMMENT '难度等级: easy, medium, hard',
    `tags` JSON COMMENT '标签（JSON格式）',
    `usage_count` INT DEFAULT 0 COMMENT '使用次数',
    `correct_count` INT DEFAULT 0 COMMENT '答对次数',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id),
    INDEX idx_question_type (question_type),
    INDEX idx_difficulty (difficulty),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目表';

3.2 考试记录表 (exams)

CREATE TABLE `exams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT '用户ID',
    `course_id` INT NOT NULL COMMENT '课程ID',
    `exam_name` VARCHAR(255) NOT NULL COMMENT '考试名称',
    `question_count` INT DEFAULT 10 COMMENT '题目数量',
    `total_score` FLOAT DEFAULT 100.0 COMMENT '总分',
    `pass_score` FLOAT DEFAULT 60.0 COMMENT '及格分',
    `start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    `end_time` DATETIME COMMENT '结束时间',
    `duration_minutes` INT DEFAULT 60 COMMENT '考试时长（分钟）',
    `score` FLOAT COMMENT '得分',
    `is_passed` BOOLEAN COMMENT '是否通过',
    `status` VARCHAR(20) DEFAULT 'started' COMMENT '状态: started, submitted, timeout',
    `questions` JSON COMMENT '题目数据（JSON格式）',
    `answers` JSON COMMENT '答案数据（JSON格式）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_course_id (course_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试记录表';

3.3 考试结果详情表 (exam_results)

CREATE TABLE `exam_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `exam_id` INT NOT NULL COMMENT '考试ID',
    `question_id` INT NOT NULL COMMENT '题目ID',
    `user_answer` TEXT COMMENT '用户答案',
    `is_correct` BOOLEAN DEFAULT FALSE COMMENT '是否正确',
    `score` FLOAT DEFAULT 0.0 COMMENT '得分',
    `answer_time` INT COMMENT '答题时长（秒）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    INDEX idx_exam_id (exam_id),
    INDEX idx_question_id (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试结果详情表';

四、陪练模块

4.1 陪练场景表 (training_scenes)

CREATE TABLE `training_scenes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '场景名称',
    `description` TEXT COMMENT '场景描述',
    `category` VARCHAR(50) NOT NULL COMMENT '场景分类',
    `ai_config` JSON COMMENT 'AI配置（如Coze Bot ID等）',
    `prompt_template` TEXT COMMENT '提示词模板',
    `evaluation_criteria` JSON COMMENT '评估标准',
    `status` ENUM('DRAFT', 'ACTIVE', 'INACTIVE') DEFAULT 'DRAFT' COMMENT '场景状态',
    `is_public` BOOLEAN DEFAULT TRUE COMMENT '是否公开',
    `required_level` INT COMMENT '所需用户等级',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_is_public (is_public),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练场景表';

4.2 陪练会话表 (training_sessions)

CREATE TABLE `training_sessions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT '用户ID',
    `scene_id` INT NOT NULL COMMENT '场景ID',
    `coze_conversation_id` VARCHAR(100) COMMENT 'Coze会话ID',
    `start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    `end_time` DATETIME COMMENT '结束时间',
    `duration_seconds` INT COMMENT '持续时长（秒）',
    `status` ENUM('CREATED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'ERROR') DEFAULT 'CREATED' COMMENT '会话状态',
    `session_config` JSON COMMENT '会话配置',
    `total_score` FLOAT COMMENT '总分',
    `evaluation_result` JSON COMMENT '评估结果详情',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (scene_id) REFERENCES training_scenes(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_scene_id (scene_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练会话表';

4.3 陪练消息表 (training_messages)

CREATE TABLE `training_messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT NOT NULL COMMENT '会话ID',
    `role` ENUM('USER', 'ASSISTANT', 'SYSTEM') NOT NULL COMMENT '消息角色',
    `type` ENUM('TEXT', 'VOICE', 'SYSTEM') NOT NULL COMMENT '消息类型',
    `content` TEXT NOT NULL COMMENT '消息内容',
    `voice_url` VARCHAR(500) COMMENT '语音文件URL',
    `voice_duration` FLOAT COMMENT '语音时长（秒）',
    `message_metadata` JSON COMMENT '消息元数据',
    `coze_message_id` VARCHAR(100) COMMENT 'Coze消息ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES training_sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练消息表';

4.4 陪练报告表 (training_reports)

CREATE TABLE `training_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT NOT NULL UNIQUE COMMENT '会话ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `overall_score` FLOAT NOT NULL COMMENT '总体得分',
    `dimension_scores` JSON NOT NULL COMMENT '各维度得分',
    `strengths` JSON NOT NULL COMMENT '优势点',
    `weaknesses` JSON NOT NULL COMMENT '待改进点',
    `suggestions` JSON NOT NULL COMMENT '改进建议',
    `detailed_analysis` TEXT COMMENT '详细分析',
    `transcript` TEXT COMMENT '对话文本记录',
    `statistics` JSON COMMENT '统计数据',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES training_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练报告表';

五、数据库设计原则

5.1 主键规范

• 所有表使用INT作为主键，满足当前规模与 ORM 定义
• 所有主键均设置为AUTO_INCREMENT

5.2 外键约束

• 所有外键关系都明确定义
• 删除策略：
  • CASCADE：级联删除（用于强关联关系）
  • SET NULL：置空（用于弱关联关系）

5.3 索引策略

• 所有外键字段自动创建索引
• 常用查询字段创建索引（如status, type等）
• 唯一约束字段自动创建唯一索引

5.4 字段命名规范

• 使用下划线命名法（snake_case）
• 布尔字段使用is_前缀
• 时间字段使用_at后缀
• JSON字段明确标注数据结构

5.5 软删除设计

• 使用is_deleted和deleted_at字段实现软删除
• 保留数据完整性，便于数据恢复

5.6 审计字段

• created_at：创建时间
• updated_at：更新时间
• created_by：创建人ID
• updated_by：更新人ID

六、性能优化建议

1. 分表策略

   • training_messages表可能增长较快，考虑按月分表
   • exam_results表可考虑按年分表

2. 缓存策略

   • 用户信息使用Redis缓存
   • 课程列表使用Redis缓存
   • 热门题目使用Redis缓存

3. 查询优化

   • 使用分页查询避免大量数据加载
   • 合理使用JOIN，避免N+1查询
   • 统计类查询考虑使用物化视图