K12Study/docs/architecture/er-diagram.md

K12Study 核心 ER 设计

1. 建模范围

• 基础域：租户、组织、用户、角色、菜单、文件。
• 课程域：课程、章节、学习节点、知识点、学习进度。
• 习题与批改域：题库、试卷、作业、提交、批改、错题与复习。
• 成就与推荐域：成就定义、用户成就、推荐任务、反馈、学习闭环。
• AI 知识域：知识文件、同步任务、图谱实体/关系。

2. 核心关系图（基础域 + 教学主链路）

erDiagram
    SYS_TENANT ||--o{ SYS_DEPT : contains
    SYS_DEPT ||--o{ SYS_USER : has
    SYS_ROLE ||--o{ SYS_ROLE_MENU : grants
    SYS_MENU ||--o{ SYS_ROLE_MENU : mapped
    SYS_USER ||--o{ SYS_FILE : uploads

    COURSE ||--o{ COURSE_CHAPTER : contains
    COURSE_CHAPTER ||--o{ COURSE_NODE : contains
    KNOWLEDGE_POINT ||--o{ CHAPTER_KP_REL : mapped
    COURSE_CHAPTER ||--o{ CHAPTER_KP_REL : mapped
    COURSE_NODE ||--o{ NODE_KP_REL : mapped
    KNOWLEDGE_POINT ||--o{ NODE_KP_REL : mapped

    QUESTION_BANK ||--o{ BANK_QUESTION_REL : contains
    QUESTION_ITEM ||--o{ BANK_QUESTION_REL : included
    PAPER ||--o{ PAPER_QUESTION : contains
    QUESTION_ITEM ||--o{ PAPER_QUESTION : included
    ASSIGNMENT ||--o{ SUBMISSION : receives
    SUBMISSION ||--o{ SUBMISSION_ANSWER : has
    QUESTION_ITEM ||--o{ SUBMISSION_ANSWER : answered
    GRADING_TASK ||--o{ ANSWER_GRADE : produces
    SUBMISSION_ANSWER ||--o{ ANSWER_GRADE : graded

    USER_ACHIEVEMENT }o--|| ACHIEVEMENT : awarded
    USER_ACHIEVEMENT }o--|| SYS_USER : owner
    RECOMMENDATION_TASK ||--o{ RECOMMENDATION_ITEM : produces
    RECOMMENDATION_ITEM ||--o{ RECOMMENDATION_FEEDBACK : receives

3. AI 知识域关系图

erDiagram
    SYS_FILE ||--o{ AI_KNOWLEDGE_FILE : source
    AI_KNOWLEDGE_FILE ||--o{ AI_KNOWLEDGE_SYNC_TASK : schedules
    AI_KNOWLEDGE_FILE ||--o{ AI_GRAPH_ENTITY : extracts
    AI_GRAPH_ENTITY ||--o{ AI_GRAPH_RELATION : links

4. 实体到现有表映射（节选）

• SYS_TENANT -> upms.tb_sys_tenant
• SYS_DEPT -> upms.tb_sys_dept
• SYS_USER -> upms.tb_sys_user
• SYS_ROLE -> upms.tb_sys_role
• SYS_MENU -> upms.tb_sys_menu
• COURSE -> course.cl_course
• KNOWLEDGE_POINT -> course.cl_knowledge_point
• QUESTION_ITEM -> question.hw_question_item
• ASSIGNMENT -> question.hw_assignment
• SUBMISSION -> question.hw_submission
• GRADING_TASK -> question.gd_grading_task
• ACHIEVEMENT -> achievement.ac_achievement
• USER_ACHIEVEMENT -> achievement.ac_user_achievement
• AI_KNOWLEDGE_FILE -> ai.tb_ai_knowledge_file

5. SQL 基线目录（现有）

• init/pg/auth/10_create_auth_tables.sql
• init/pg/upms/10_create_upms_tables.sql
• init/pg/course/10_create_course_tables.sql
• init/pg/question/10_create_question_tables.sql
• init/pg/achievement/10_create_achievement_tables.sql
• init/pg/ai/10_create_ai_tables.sql

6. 设计建议

• 保持跨域依赖单向：course/question/achievement/recommendation 通过 upms 提供的主体信息关联。
• 涉及 AI 知识检索的数据优先走异步同步表，避免在线写链路阻塞。
• 复杂业务报表优先通过事实表/汇总表落地，避免线上实时多表大 join。