💾 数据库设计 ER 图

读书笔记App - 实体关系图与表结构

🔗 实体关系图 (Entity-Relationship Diagram)
erDiagram USER ||--o{ LEARNHIS_LEARNING_TASK : creates USER ||--o{ LEARNHIS_LEARNING_LOG : records USER ||--o{ LEARNHIS_NOTE : writes USER ||--o{ POINT_LOG : earns USER ||--o{ VIP_ORDER : purchases USER ||--o{ MALL_ORDER : places USER ||--o{ GROUP_MEMBER : joins USER ||--o{ SYNC_DEVICE : uses USER ||--o{ SYNC_LOG : syncs USER ||--o{ LEARNHIS_USER_MILESTONE : achieves USER ||--o{ LEARNHIS_LEARNING_TOPIC : creates USER ||--o{ LEARNHIS_LEARNING_PATH : plans LEARNHIS_BOOK }o--|| LEARNHIS_BOOK_AUTHOR : "written by" LEARNHIS_BOOK }o--|| LEARNHIS_BOOK_PUBLISHER : "published by" LEARNHIS_BOOK ||--o{ LEARNHIS_LEARNING_TASK : referenced_by LEARNHIS_LEARNING_TASK }o--o| LEARNHIS_BOOK : references LEARNHIS_LEARNING_TASK }o--o| LEARNHIS_CUSTOM_SOURCE : from LEARNHIS_LEARNING_TASK ||--o{ LEARNHIS_LEARNING_LOG : generates LEARNHIS_LEARNING_TASK ||--o{ LEARNHIS_NOTE : contains LEARNHIS_LEARNING_LOG ||--o{ LEARNHIS_NOTE : generates LEARNHIS_BOOK }o--o{ LEARNHIS_LEARNING_PATH : "included in" LEARNHIS_BOOK }o--o{ LEARNHIS_KNOWLEDGE_RELATION : "related to" LEARNHIS_NOTE }o--o{ LEARNHIS_NOTE_TAG : tagged LEARNHIS_NOTE }o--o{ LEARNHIS_NOTE_TAG_RELATION : through LEARNHIS_NOTE }o--o{ LEARNHIS_REVIEW_PLAN : "scheduled for" LEARNHIS_NOTE }o--o{ LEARNHIS_KNOWLEDGE_RELATION : "related to" LEARNHIS_NOTE_TAG ||--o{ LEARNHIS_NOTE_TAG_RELATION : links USER ||--o{ USER_TAG : manages TAG }o--|| USER_TAG : owned POINT_RULE ||--o{ POINT_LOG : defines MALL_PRODUCT ||--o{ MALL_ORDER : ordered GROUP ||--o{ GROUP_MEMBER : includes USER ||--o{ GROUP : creates LEARNHIS_MILESTONE_CONFIG ||--o{ LEARNHIS_USER_MILESTONE : defines LEARNHIS_USER_MILESTONE ||--o{ LEARNHIS_MILESTONE_REWARD_LOG : earns LEARNHIS_LEARNING_TOPIC ||--o{ LEARNHIS_LEARNING_PATH : organizes LEARNHIS_LEARNING_TOPIC ||--o{ LEARNHIS_LEARNING_STATS : tracks LEARNHIS_LEARNING_PATH ||--o{ LEARNHIS_LEARNING_STATS : measures LEARNHIS_SYNC_ENTITY_VERSION ||--o{ LEARNHIS_SYNC_LOG : versions USER { int id PK string openid UK string nickname string avatar_url boolean is_vip datetime vip_expire_time int total_points datetime created_at } LEARNHIS_BOOK { int id PK string title string cover_url int author_id FK int publisher_id FK date publish_date string isbn UK int total_pages text description text catalog json extra_info datetime created_at } LEARNHIS_LEARNING_TASK { int id PK int user_id FK string task_type string title text description string cover_url int status decimal progress int book_id FK int source_id FK int group_id FK int current_page int total_pages json extra_info datetime created_at } LEARNHIS_LEARNING_LOG { int id PK int user_id FK int task_id FK datetime start_time datetime end_time int duration_seconds int start_page int end_page date log_date datetime created_at } LEARNHIS_NOTE { int id PK int user_id FK int task_id FK int reading_log_id FK text content int page_no json images datetime created_at datetime updated_at } LEARNHIS_NOTE_TAG { int id PK string name UK string color datetime created_at } LEARNHIS_NOTE_TAG_RELATION { int note_id FK int tag_id FK } LEARNHIS_BOOK_AUTHOR { int id PK string name string bio } LEARNHIS_BOOK_PUBLISHER { int id PK string name string description } LEARNHIS_BOOK_BOOKSHELF { int id PK int user_id FK string name int sort_order } SERIES { int id PK int user_id FK string name string description } USER_TAG { int id PK int user_id FK int tag_id FK datetime created_at } POINT_LOG { int id PK int user_id FK int rule_id FK int points string description datetime created_at } POINT_RULE { int id PK string name string rule_key int points int daily_limit } VIP_PACKAGE { int id PK string name int duration_days decimal price boolean is_active } VIP_ORDER { int id PK int user_id FK int package_id FK decimal amount string order_no int status datetime paid_at datetime created_at } MALL_PRODUCT { int id PK string name int points_required int stock string image_url boolean is_active } MALL_ORDER { int id PK int user_id FK int product_id FK int points_cost string order_no int status datetime created_at } GROUP { int id PK int creator_id FK string name string description string cover_url datetime created_at } GROUP_MEMBER { int id PK int group_id FK int user_id FK int role datetime joined_at }
📌 关系说明
核心实体关系：
• 用户 (USER) 是中心实体，创建学习任务、学习记录、笔记等所有内容
• 书籍 (BOOK) 为基础信息表，通过ISBN查询获取，可被多个学习任务引用
• 学习任务 (LEARNING_TASK) 支持多种类型（书籍/视频/音频/文档/博客/小红书等）
• 学习记录 (READING_LOG) 关联到学习任务，记录每次学习活动
• 笔记 (NOTE) 关联到学习任务和学习记录，通过标签 (TAG) 实现多对多关系
• 积分系统通过 POINT_LOG 和 POINT_RULE 实现规则化管理
• 会员系统和商城系统通过订单表实现交易记录
📊 核心数据表结构
👤 users - 用户表
  • id INT PK
  • openid VARCHAR UK
  • nickname VARCHAR
  • avatar_url VARCHAR
  • is_vip BOOLEAN
  • vip_expire_time DATETIME
  • total_points INT
  • created_at DATETIME
📚 learnhis_book - 书籍基础信息表 【2026年1月5日更新】
  • id INT PK
  • title VARCHAR(255)
  • author_id INT FK
  • publisher_id INT FK
  • isbn VARCHAR(20) UK
  • description TEXT
  • cover_url VARCHAR(500)
  • catalog TEXT
  • publish_date DATE
  • total_pages INT
  • extra_info JSON
说明：书籍基础信息表，通过ISBN查询获取，可被多个学习任务引用
📋 learnhis_learning_tasks - 学习任务表 【2026年1月5日更新】
  • id INT PK
  • user_id INT FK
  • task_type VARCHAR(20)
  • title VARCHAR(255)
  • description TEXT
  • cover_url VARCHAR(500)
  • status INT
  • progress DECIMAL(5,2)
  • book_id INT FK
  • source_id INT FK
  • group_id INT FK
  • current_page INT
  • total_pages INT
  • extra_info JSON
说明：学习任务表，支持书籍/视频/音频/文档/博客/小红书等多种类型
⏱️ learnhis_learning_logs - 学习记录表 【2026年1月5日更新】
  • id INT PK
  • user_id INT FK
  • task_id INT FK
  • start_time DATETIME
  • end_time DATETIME
  • duration_seconds INT
  • start_page INT
  • end_page INT
  • log_date DATE
📝 learnhis_note - 笔记/书摘表
  • id INT PK
  • user_id INT FK
  • task_id INT FK
  • reading_log_id INT FK
  • content TEXT
  • page_no INT
  • images JSON
  • created_at DATETIME
  • updated_at DATETIME
🏷️ tags - 标签表
  • id INT PK
  • name VARCHAR UK
  • color VARCHAR
  • created_at DATETIME
🔗 note_tags - 笔记标签关联表
  • note_id INT FK
  • tag_id INT FK
✍️ learnhis_book_author - 作者表
  • id INT PK
  • name VARCHAR
  • bio TEXT
🏢 learnhis_book_publisher - 出版社表
  • id INT PK
  • name VARCHAR
  • description TEXT
📖 learnhis_book_bookshelf - 书架表
  • id INT PK
  • user_id INT FK
  • name VARCHAR
  • sort_order INT
📚 series - 套装表
  • id INT PK
  • user_id INT FK
  • name VARCHAR
  • description TEXT
💰 point_logs - 积分日志表
  • id INT PK
  • user_id INT FK
  • rule_id INT FK
  • points INT
  • description VARCHAR
  • created_at DATETIME
📋 point_rules - 积分规则表
  • id INT PK
  • name VARCHAR
  • rule_key VARCHAR UK
  • points INT
  • daily_limit INT
💎 vip_packages - 会员套餐表
  • id INT PK
  • name VARCHAR
  • duration_days INT
  • price DECIMAL
  • is_active BOOLEAN
🧾 vip_orders - 会员订单表
  • id INT PK
  • user_id INT FK
  • package_id INT FK
  • amount DECIMAL
  • order_no VARCHAR UK
  • status INT
🛒 mall_products - 商城商品表
  • id INT PK
  • name VARCHAR
  • points_required INT
  • stock INT
  • image_url VARCHAR
  • is_active BOOLEAN
🧾 mall_orders - 商城订单表
  • id INT PK
  • user_id INT FK
  • product_id INT FK
  • points_cost INT
  • order_no VARCHAR UK
  • status INT
👥 groups - 读书小组表
  • id INT PK
  • creator_id INT FK
  • name VARCHAR
  • description TEXT
📱 sync_device_info - 设备信息表
  • id INT PK
  • user_id INT FK
  • device_id VARCHAR(100) UK
  • device_type INT
  • last_sync_time DATETIME
  • sync_version INT
🔄 sync_log - 同步记录表
  • id INT PK
  • user_id INT FK
  • entity_type VARCHAR(50)
  • entity_id INT
  • device_id VARCHAR(100)
  • action INT
  • local_version INT
  • server_version INT
  • sync_status INT
🔢 sync_entity_version - 数据版本表
  • id INT PK
  • user_id INT FK
  • entity_type VARCHAR(50)
  • entity_id INT
  • version INT
  • last_modified_by VARCHAR(100)
🏆 learnhis_milestone_config - 里程碑配置表
  • id INT PK
  • name VARCHAR(100)
  • type INT
  • condition JSON
  • reward JSON
  • level INT
  • status INT
⭐ user_milestone - 用户里程碑记录表
  • id INT PK
  • user_id INT FK
  • milestone_id INT FK
  • progress INT
  • target INT
  • status INT
  • reward_received BOOLEAN
🎁 milestone_reward_log - 里程碑奖励记录表
  • id INT PK
  • user_id INT FK
  • milestone_id INT FK
  • reward_type VARCHAR(20)
  • reward_value JSON
📚 learnhis_learning_topic - 学习主题表
  • id INT PK
  • user_id INT FK
  • name VARCHAR(100)
  • parent_id INT FK
  • color VARCHAR(20)
🗺️ learning_path - 学习路径表
  • id INT PK
  • user_id INT FK
  • topic_id INT FK
  • name VARCHAR(100)
  • books JSON
  • current_book_index INT
  • status INT
🔗 knowledge_relation - 知识关联表
  • id INT PK
  • user_id INT FK
  • source_type VARCHAR(20)
  • source_id INT
  • target_type VARCHAR(20)
  • target_id INT
  • relation_type INT
  • weight DECIMAL(3,2)
📅 review_plan - 复习计划表
  • id INT PK
  • user_id INT FK
  • note_id INT FK
  • review_times INT
  • next_review_time DATETIME
  • ease_factor DECIMAL(3,2)
  • interval INT
📊 learning_stats - 学习统计表
  • id INT PK
  • user_id INT FK
  • topic_id INT FK
  • total_books INT
  • total_hours INT
  • mastery_level INT
  • cover_url VARCHAR
    • 👤 group_members - 小组成员表
    • id INT PK
    • group_id INT FK
    • user_id INT FK
    • role INT
    • joined_at DATETIME
    📚 6. 多载体学习内容模块 【2026年1月5日更新】
    erDiagram User ||--o{ LearnhisLearningContent : owns LearnhisLearningContent ||--o{ LearnhisLearningContentItem : has LearnhisLearningContent }o--o| LearnhisCustomSource : from LearnhisLearningContent }o--o| LearnhisLearningGroup : belongs_to User ||--o{ LearnhisCustomSource : creates User ||--o{ LearnhisLearningGroup : creates LearnhisLearningContent { int id PK int user_id FK varchar type varchar title text description varchar cover_url int status decimal progress json extra_info int source_id FK int group_id FK datetime created_at } LearnhisLearningContentItem { int id PK int content_id FK varchar item_type varchar title int duration decimal progress json extra_data int sort_order } LearnhisCustomSource { int id PK int user_id FK varchar name varchar icon_url text description boolean is_system } LearnhisLearningGroup { int id PK int user_id FK varchar name text description int parent_id FK int sort_order }
    📚 learnhis_learning_contents - 学习内容表
    • id INT PK
    • user_id INT FK
    • type VARCHAR(20)
    • title VARCHAR(255)
    • description TEXT
    • cover_url VARCHAR(500)
    • status INT
    • progress DECIMAL(5,2)
    • extra_info JSON
    • source_id INT FK
    • group_id INT FK
    📄 learnhis_learning_content_items - 学习内容条目表
    • id INT PK
    • content_id INT FK
    • item_type VARCHAR(20)
    • title VARCHAR(255)
    • duration INT
    • progress DECIMAL(5,2)
    • extra_data JSON
    • sort_order INT
    🏷️ learnhis_custom_sources - 自定义来源表
    • id INT PK
    • user_id INT FK
    • name VARCHAR(100)
    • icon_url VARCHAR(500)
    • description TEXT
    • is_system BOOLEAN
    📁 learnhis_learning_groups - 学习内容分组表
    • id INT PK
    • user_id INT FK
    • name VARCHAR(100)
    • description TEXT
    • icon VARCHAR(200)
    • parent_id INT FK
    • sort_order INT
    🧠 7. 思维导图模块 【2026年1月5日更新】
    erDiagram User ||--o{ LearnhisMindMap : creates LearnhisLearningContent ||--o| LearnhisMindMap : generates LearnhisMindMap { int id PK int user_id FK varchar title int content_id FK json data varchar thumbnail_url boolean is_auto_generated datetime created_at }
    🧠 learnhis_mind_maps - 思维导图表
    • id INT PK
    • user_id INT FK
    • title VARCHAR(255)
    • content_id INT FK
    • data JSON
    • thumbnail_url VARCHAR(500)
    • is_auto_generated BOOLEAN
    👥 8. 学习分享与社交模块 【2026年1月5日更新】
    erDiagram User ||--o{ LearnhisLearningShare : shares User ||--o{ LearnhisLearningShare : source_of LearnhisLearningShare { int id PK int user_id FK varchar content_type int content_id varchar share_type varchar title text content json images int source_user_id FK int like_count int comment_count int view_count datetime created_at }
    📤 learnhis_learning_shares - 学习分享表
    • id INT PK
    • user_id INT FK
    • content_type VARCHAR(20)
    • content_id INT
    • share_type VARCHAR(20)
    • title VARCHAR(255)
    • content TEXT
    • images JSON
    • source_user_id INT FK
    • like_count INT
    • comment_count INT
    • view_count INT
    💬 9. 讨论功能模块 【2026年1月5日更新】
    erDiagram User ||--o{ LearnhisDiscussionTopic : creates User ||--o{ LearnhisDiscussionReply : replies LearnhisDiscussionTopic ||--o{ LearnhisDiscussionReply : has LearnhisDiscussionReply ||--o| LearnhisDiscussionReply : replies_to LearnhisLearningContent ||--o{ LearnhisDiscussionTopic : discussed_in LearnhisDiscussionTopic { int id PK int user_id FK varchar content_type int content_id varchar title text content int status int reply_count int view_count int like_count datetime created_at } LearnhisDiscussionReply { int id PK int topic_id FK int user_id FK int parent_id FK text content int like_count boolean is_expert datetime created_at }
    💬 learnhis_discussion_topics - 讨论话题表
    • id INT PK
    • user_id INT FK
    • content_type VARCHAR(20)
    • content_id INT
    • title VARCHAR(255)
    • content TEXT
    • status INT
    • reply_count INT
    • view_count INT
    • like_count INT
    💭 learnhis_discussion_replies - 讨论回复表
    • id INT PK
    • topic_id INT FK
    • user_id INT FK
    • parent_id INT FK
    • content TEXT
    • like_count INT
    • is_expert BOOLEAN
    🤖 10. AI学习推荐模块 【2026年1月5日更新】
    erDiagram User ||--o{ LearnhisAiRecommendation : receives User ||--o{ LearnhisLearningPath : creates LearnhisLearningContent ||--o{ LearnhisAiRecommendation : recommended_by LearnhisLearningContent }o--o{ LearnhisLearningPath : included_in LearnhisAiRecommendation { int id PK int user_id FK int content_id FK varchar recommend_type text reason decimal score int status datetime created_at } LearnhisLearningPath { int id PK int user_id FK varchar name text description json items int status decimal progress datetime created_at }
    🤖 learnhis_ai_recommendations - AI推荐记录表
    • id INT PK
    • user_id INT FK
    • content_id INT FK
    • recommend_type VARCHAR(20)
    • reason TEXT
    • score DECIMAL(5,2)
    • status INT
    🛤️ learnhis_learning_paths - 学习路径表
    • id INT PK
    • user_id INT FK
    • name VARCHAR(100)
    • description TEXT
    • items JSON
    • status INT
    • progress DECIMAL(5,2)
    📖 数据字典

    书籍状态 (book.status)

    订单状态 (order.status)

    小组角色 (group_member.role)

    ⚡ 索引建议

    主要索引

    • books: idx_user_id, idx_status, idx_bookshelf_id
    • books: uk_isbn, idx_title, idx_author_id, idx_publisher_id
    • learning_tasks: idx_user_id, idx_task_type, idx_status, idx_book_id, idx_source_id, idx_group_id
    • reading_logs: idx_user_id, idx_task_id, idx_log_date
    • notes: idx_user_id, idx_task_id, idx_reading_log_id, idx_created_at
    • note_tags: idx_note_id, idx_tag_id (联合索引)
    • point_logs: idx_user_id, idx_created_at
    • vip_orders: idx_user_id, idx_order_no
    • mall_orders: idx_user_id, idx_order_no
    • group_members: idx_group_id, idx_user_id
    • sync_device_info: idx_user_id, uk_device_id
    • sync_log: idx_user_id, idx_entity_type_id, idx_device_id
    • sync_entity_version: idx_user_entity, idx_version
    • user_milestone: idx_user_id, idx_milestone_id
    • learning_topic: idx_user_id, idx_parent_id
    • learning_path: idx_user_id, idx_topic_id
    • knowledge_relation: idx_user_source, idx_target
    • review_plan: idx_user_id, idx_next_review_time
    • learning_contents: idx_user_id, idx_type, idx_status, idx_source_id, idx_group_id
    • learning_content_items: idx_content_id, idx_sort_order
    • custom_sources: idx_user_id, idx_is_system
    • learning_groups: idx_user_id, idx_parent_id
    • mind_maps: idx_user_id, idx_content_id
    • learning_shares: idx_user_id, idx_content_type_id, idx_source_user_id
    • discussion_topics: idx_user_id, idx_content_type_id, idx_status
    • discussion_replies: idx_topic_id, idx_user_id, idx_parent_id
    • ai_recommendations: idx_user_id, idx_content_id, idx_recommend_type
    • learning_paths: idx_user_id, idx_status
    📊 数据库设计总结

    数据表统计： 共 27 张核心数据表（新增9张）

    主要模块：

    设计原则：满足第三范式、合理使用外键、预留扩展字段、注重查询性能

    ← 返回文档总览

    📝 更新日志

    2025-01-XX (v2.0)

    ✅ 已更新的表（30+张）:
    learnhis_book
    learnhis_learning_tasks
    learnhis_learning_logs
    learnhis_note
    learnhis_learning_contents
    learnhis_learning_content_items
    learnhis_learning_groups
    learnhis_custom_sources
    learnhis_book_author
    learnhis_book_publisher
    learnhis_book_bookshelf
    learnhis_book_series
    learnhis_note_tag
    learnhis_note_tag_relation
    learnhis_milestone_config
    learnhis_user_milestone
    learnhis_milestone_reward_log
    learnhis_learning_topic
    learnhis_learning_path
    learnhis_knowledge_relation
    learnhis_review_plan
    learnhis_learning_stats
    learnhis_mind_maps
    learnhis_learning_shares
    learnhis_discussion_topics
    learnhis_discussion_replies
    learnhis_ai_recommendations
    learnhis_learning_paths
    learnhis_sync_device_info
    learnhis_sync_log
    learnhis_sync_entity_version