ER图设计与实战

ER 图的作用：
│
├── 1⃣ 梳理实体
│   └── 明确系统中有哪些实体（表）
│
├── 2⃣ 确定关系
│   └── 明确实体之间的关联关系
│
├── 3⃣ 发现缺陷
│   └── 在设计阶段发现数据库设计的缺陷
│
├── 4⃣ 优化设计
│   └── 提前优化，避免后期修改成本
│
└── 5⃣ 沟通工具
    └── 团队沟通的可视化工具

文档描述：
用户表和课程表是一对多关系，课程表和章节表是一对多关系...

问题：
 难以直观理解
 关系复杂时容易混淆
 不容易发现设计缺陷

ER 图展示：
┌─────────┐         ┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── N │  课程   │ 1 ──── N │  章节   │
└─────────┘         └─────────┘         └─────────┘

优势：
 一目了然
 关系清晰
 易于发现问题

创建 ER 图步骤：
│
├── 1⃣ 打开网站
│   └── 访问 https://dbdiagram.io/
│
├── 2⃣ 创建新项目
│   └── 点击 "Create" → "ER Diagram"
│
├── 3⃣ 添加实体
│   └── 拖拽表格组件到画布
│
├── 4⃣ 定义字段
│   ├── 字段名称
│   ├── 数据类型
│   └── 约束条件
│
├── 5⃣ 建立关系
│   └── 使用连线工具连接实体
│
└── 6⃣ 导出
    ├── 导出 SQL 语句
    └── 导出图片

┌─────────────────┐
│     users       │  ← 表名
├─────────────────┤
│ id: INT (PK)    │  ← 主键
│ username: VARCHAR│ ← 字段
│ email: VARCHAR  │
└─────────────────┘

┌─────────────────┐
│     users       │
├─────────────────┤
│ id              │  ← 字段名
│ INT             │  ← 数据类型
│ PK              │  ← 主键标识
│ NOT NULL        │  ← 约束条件
│ AUTO_INCREMENT  │  ← 自动增长
└─────────────────┘

四种关联关系：
│
├── 1⃣ 一对一（1:1）
│   ├── 符号：1 ──── 1
│   ├── 示例：用户 ↔ 身份证
│   └── 实现：外键 + UNIQUE
│
├── 2⃣ 一对多（1:N）
│   ├── 符号：1 ──── N
│   ├── 示例：用户 ↔ 订单
│   └── 实现：外键在"多"的一方
│
├── 3⃣ 零对一（0:1）
│   ├── 符号：0 ──── 1
│   ├── 示例：用户 ↔ 个人资料（可选）
│   └── 实现：外键允许 NULL
│
└── 4⃣ 多对多（M:N）
    ├── 符号：M ──── N
    ├── 示例：学生 ↔ 课程
    └── 实现：中间表

一对一（1:1）：
┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── 1 │ 身份证  │
└─────────┘         └─────────┘

一对多（1:N）：
┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── N │  订单   │
└─────────┘         └─────────┘

零对一（0:1）：
┌─────────┐         ┌─────────┐
│  用户   │ 0 ──── 1 │ 个人资料│
└─────────┘         └─────────┘

多对多（M:N）：
┌─────────┐         ┌─────────────┐         ┌─────────┐
│  学生   │ N ──── M │ 中间表      │ M ──── N │  课程   │
└─────────┘         └─────────────┘         └─────────┘

ER 图设计流程：
│
├── 1⃣ 需求分析
│   ├── 理解业务需求
│   ├── 识别核心实体
│   └── 确定实体属性
│
├── 2⃣ 确定实体
│   ├── 列出所有实体
│   ├── 定义实体名称
│   └── 确定主键
│
├── 3⃣ 定义属性
│   ├── 列出字段名称
│   ├── 确定数据类型
│   └── 添加约束条件
│
├── 4⃣ 建立关系
│   ├── 确定关联类型
│   ├── 添加外键
│   └── 绘制连线
│
└── 5⃣ 优化调整
    ├── 检查范式
    ├── 优化布局
    └── 评审确认

ER 图设计原则：
│
├── 1⃣ 清晰易懂
│   ├── 布局合理
│   ├── 命名规范
│   └── 注释清晰
│
├── 2⃣ 符合范式
│   ├── 遵循数据库设计范式
│   ├── 避免数据冗余
│   └── 合理使用反范式
│
├── 3⃣ 易于扩展
│   ├── 预留扩展空间
│   ├── 设计灵活的结构
│   └── 避免过度设计
│
└── 4⃣ 性能考虑
    ├── 合理分表
    ├── 索引设计
    └── 查询优化

课程系统实体：
│
├── 用户相关
│   ├── users（用户表）
│   └── home_resources（首页资源表）
│
├── 课程相关
│   ├── courses（课程表）
│   ├── course_contents（课程章节表）
│   └── course_tags（课程标签表）
│
├── 附件相关
│   ├── attachments（附件表）
│   └── attachment_attributes（附件属性表）
│
├── 评论相关
│   ├── comments（评论表）
│   ├── course_comments（课程评论表）
│   └── content_comments（章节评论表）
│
└── 字典相关
    └── attributes（属性字典表）

┌─────────────┐                    ┌─────────────┐
│ home_       │                    │   users     │
│ resources   │                    ├─────────────┤
├─────────────┤                    │ id (PK)     │
│ id (PK)     │                    │ username    │
│ title       │                    │ email       │
│ url         │                    └──────┬──────┘
└─────────────┘                           │
                                          │ 1
                                          │
                  ┌───────────────────────┼───────────────────────┐
                  │                       │                       │
                  ▼ N                     ▼ N                     ▼ N
         ┌────────────────┐      ┌────────────────┐      ┌────────────────┐
         │    courses     │      │  attachments   │      │    comments    │
         ├────────────────┤      ├────────────────┤      ├────────────────┤
         │ id (PK)        │      │ id (PK)        │      │ id (PK)        │
         │ name           │      │ user_id (FK)   │      │ user_id (FK)   │
         │ description    │      │ file_name      │      │ content        │
         │ price          │      │ file_path      │      │ created_at     │
         └───────┬────────┘      └───────┬────────┘      └────────────────┘
                 │                       │
      ┌──────────┼──────────┐            │
      │          │          │            │ 1
      ▼ N        ▼ N        ▼ N          │
┌──────────┐ ┌──────────┐ ┌──────────┐   ▼ N
│ course_  │ │ course_  │ │ course_  │ ┌────────────────┐
│ contents │ │ tags     │ │ comments │ │ attachment_    │
├──────────┤ ├──────────┤ ├──────────┤ │ attributes     │
│ id (PK)  │ │ id (PK)  │ │ id (PK)  │ ├────────────────┤
│ course_id│ │ course_id│ │ course_id│ │ id (PK)        │
│ title    │ │ tag_name │ │ content  │ │ attachment_id  │
│ content  │ └──────────┘ └──────────┘ │ attribute_id   │
└────┬─────┘                          └───────┬────────┘
     │                                        │
     │ N                                      │ N
     │                                        │
     ▼ 1                                      ▼ 1
┌──────────┐                          ┌──────────────┐
│ content_ │                          │  attributes  │
│ comments │                          ├──────────────┤
├──────────┤                          │ id (PK)      │
│ id (PK)  │                          │ name         │
│ content_ │                          │ unit         │
│ id (FK)  │                          └──────────────┘
│ content  │
└──────────┘

┌─────────────────┐
│     users       │
├─────────────────┤
│ id: INT (PK)    │
│ username: VARCHAR(50) NOT NULL │
│ email: VARCHAR(100) UNIQUE │
│ password: VARCHAR(255) NOT NULL │
│ phone: CHAR(11) │
│ status: TINYINT DEFAULT 1 │
│ created_at: TIMESTAMP │
└─────────────────┘

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  password VARCHAR(255) NOT NULL COMMENT '密码',
  phone CHAR(11) COMMENT '手机号',
  status TINYINT DEFAULT 1 COMMENT '状态',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  INDEX idx_username (username),
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

┌─────────────────┐
│ home_resources  │
├─────────────────┤
│ id: INT (PK)    │
│ title: VARCHAR(200) NOT NULL │
│ url: VARCHAR(500) │
│ sort_order: INT │
│ status: TINYINT DEFAULT 1 │
└─────────────────┘

CREATE TABLE home_resources (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '资源ID',
  title VARCHAR(200) NOT NULL COMMENT '标题',
  url VARCHAR(500) COMMENT '跳转链接',
  sort_order INT DEFAULT 0 COMMENT '排序',
  status TINYINT DEFAULT 1 COMMENT '状态',
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='首页资源表';

┌─────────────────┐
│    courses      │
├─────────────────┤
│ id: INT (PK)    │
│ name: VARCHAR(200) NOT NULL │
│ description: TEXT │
│ price: DECIMAL(10,2) │
│ cover_image: VARCHAR(500) │
│ teacher_id: INT (FK) │
│ status: TINYINT DEFAULT 1 │
│ created_at: TIMESTAMP │
└─────────────────┘

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
  name VARCHAR(200) NOT NULL COMMENT '课程名称',
  description TEXT COMMENT '课程描述',
  price DECIMAL(10, 2) DEFAULT 0 COMMENT '价格',
  cover_image VARCHAR(500) COMMENT '封面图片',
  teacher_id INT COMMENT '讲师ID',
  status TINYINT DEFAULT 1 COMMENT '状态',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  INDEX idx_teacher (teacher_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

┌─────────────────┐
│ course_contents │
├─────────────────┤
│ id: INT (PK)    │
│ course_id: INT (FK) NOT NULL │
│ title: VARCHAR(200) NOT NULL │
│ content: TEXT │
│ video_url: VARCHAR(500) │
│ sort_order: INT │
│ created_at: TIMESTAMP │
└─────────────────┘

CREATE TABLE course_contents (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '章节ID',
  course_id INT NOT NULL COMMENT '课程ID',
  title VARCHAR(200) NOT NULL COMMENT '章节标题',
  content TEXT COMMENT '章节内容',
  video_url VARCHAR(500) COMMENT '视频链接',
  sort_order INT DEFAULT 0 COMMENT '排序',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (course_id) REFERENCES courses(id),
  INDEX idx_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程章节表';

┌─────────────────┐
│  attachments    │
├─────────────────┤
│ id: INT (PK)    │
│ user_id: INT (FK) NOT NULL │
│ file_name: VARCHAR(200) NOT NULL │
│ file_path: VARCHAR(500) NOT NULL │
│ file_size: INT │
│ file_type: VARCHAR(50) │
│ created_at: TIMESTAMP │
└─────────────────┘

CREATE TABLE attachments (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '附件ID',
  user_id INT NOT NULL COMMENT '用户ID',
  file_name VARCHAR(200) NOT NULL COMMENT '文件名',
  file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
  file_size INT COMMENT '文件大小',
  file_type VARCHAR(50) COMMENT '文件类型',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='附件表';

┌─────────────────┐
│ attachment_     │
│ attributes      │
├─────────────────┤
│ id: INT (PK)    │
│ attachment_id: INT (FK) NOT NULL │
│ attribute_id: INT (FK) NOT NULL │
│ attribute_value: VARCHAR(100) │
└─────────────────┘

CREATE TABLE attachment_attributes (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '属性ID',
  attachment_id INT NOT NULL COMMENT '附件ID',
  attribute_id INT NOT NULL COMMENT '属性字典ID',
  attribute_value VARCHAR(100) COMMENT '属性值',
  FOREIGN KEY (attachment_id) REFERENCES attachments(id),
  FOREIGN KEY (attribute_id) REFERENCES attributes(id),
  INDEX idx_attachment (attachment_id),
  INDEX idx_attribute (attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='附件属性表';

┌─────────────────┐
│   attributes    │
├─────────────────┤
│ id: INT (PK)    │
│ name: VARCHAR(50) NOT NULL │
│ unit: VARCHAR(20) │
│ description: VARCHAR(200) │
└─────────────────┘

CREATE TABLE attributes (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '属性ID',
  name VARCHAR(50) NOT NULL COMMENT '属性名称',
  unit VARCHAR(20) COMMENT '单位',
  description VARCHAR(200) COMMENT '描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='属性字典表';

-- 插入常用属性
INSERT INTO attributes (name, unit, description) VALUES
  ('分辨率', 'px', '图片分辨率'),
  ('尺寸', 'KB', '文件大小'),
  ('时长', '秒', '音视频时长'),
  ('比特率', 'kbps', '音视频比特率');

┌─────────────────┐
│    comments     │
├─────────────────┤
│ id: INT (PK)    │
│ user_id: INT (FK) NOT NULL │
│ content: TEXT NOT NULL │
│ parent_id: INT │
│ status: TINYINT DEFAULT 1 │
│ created_at: TIMESTAMP │
└─────────────────┘

CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
  user_id INT NOT NULL COMMENT '用户ID',
  content TEXT NOT NULL COMMENT '评论内容',
  parent_id INT COMMENT '父评论ID（用于回复）',
  status TINYINT DEFAULT 1 COMMENT '状态',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user (user_id),
  INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';

┌─────────────────┐
│ course_comments │
├─────────────────┤
│ course_id: INT (FK) NOT NULL │
│ comment_id: INT (FK) NOT NULL │
│ PRIMARY KEY (course_id, comment_id) │
└─────────────────┘

CREATE TABLE course_comments (
  course_id INT NOT NULL COMMENT '课程ID',
  comment_id INT NOT NULL COMMENT '评论ID',
  PRIMARY KEY (course_id, comment_id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (comment_id) REFERENCES comments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程评论关联表';

┌─────────────────┐
│   course_tags   │
├─────────────────┤
│ id: INT (PK)    │
│ course_id: INT (FK) NOT NULL │
│ tag_name: VARCHAR(50) NOT NULL │
└─────────────────┘

CREATE TABLE course_tags (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
  course_id INT NOT NULL COMMENT '课程ID',
  tag_name VARCHAR(50) NOT NULL COMMENT '标签名称',
  FOREIGN KEY (course_id) REFERENCES courses(id),
  INDEX idx_course (course_id),
  INDEX idx_tag (tag_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程标签表';

实体关系总结：
│
├── users（用户）
│   ├── 1:N → attachments（附件）
│   ├── 1:N → comments（评论）
│   └── 1:N → courses（课程，作为讲师）
│
├── courses（课程）
│   ├── 1:N → course_contents（章节）
│   ├── 1:N → course_tags（标签）
│   └── M:N → comments（评论，通过 course_comments）
│
├── course_contents（章节）
│   └── M:N → comments（如需章节级评论，可扩展 content_comments 关联表）
│
├── attachments（附件）
│   └── 1:N → attachment_attributes（属性）
│
├── attachment_attributes（附件属性）
│   └── N:1 → attributes（属性字典）
│
└── 独立实体
    └── home_resources（首页资源）

布局原则：
│
├── 从左到右、从上到下
│   └── 主表在左，从表在右
│
├── 相关实体靠近
│   └── 有关系的表放在一起
│
├── 避免交叉线
│   └── 连线尽量不交叉
│
└── 留有空间
    └── 方便后续扩展

命名规范：
│
├── 表名
│   ├── 小写 + 下划线
│   ├── 复数形式（users、orders）
│   └── 见名知意
│
├── 字段名
│   ├── 小写 + 下划线
│   ├── 主键：id 或 表名_id
│   ├── 外键：关联表名_id
│   └── 时间：created_at、updated_at
│
└── 索引名
    ├── idx_字段名（普通索引）
    └── uk_字段名（唯一索引）

关系线使用：
│
├── 一对一
│   └── 两端都是 "1"
│
├── 一对多
│   ├── 一端是 "1"
│   └── 多端是 "N"
│
├── 零对一
│   └── 可选关系，允许 NULL
│
└── 多对多
    └── 两端都是 "N"，需要中间表

答案：不一定

原则：
├── 核心字段必须列出
│   └── 如：id、name、status
├── 外键字段必须列出
│   └── 如：user_id、course_id
└── 辅助字段可省略
    └── 如：created_at、updated_at（可统一说明）

解决方案：

┌─────────┐         ┌─────────────┐         ┌─────────┐
│  学生   │ N ──── M │ student_    │ M ──── N │  课程   │
└─────────┘         │ courses     │         └─────────┘
                    ├─────────────┤
                    │ student_id  │
                    │ course_id   │
                    │ score       │  ← 额外字段
                    │ enroll_date │  ← 额外字段
                    └─────────────┘

中间表可以有额外字段：
├── score（成绩）
├── enroll_date（选课时间）
└── status（选课状态）

答案：视情况而定

优点：
├── 保证数据一致性
└── 防止孤立数据

缺点：
├── 影响插入/删除性能
└── 增加维护成本

建议：
├── 核心业务表：建立外键
├── 高频操作表：可不建立外键
└── 在应用层保证一致性

ER 图设计中的性能优化：
│
├── 1⃣ 合理分表
│   ├── 大表拆分
│   └── 冷热数据分离
│
├── 2⃣ 适当冗余
│   ├── 冗余常用字段
│   └── 空间换时间
│
├── 3⃣ 索引设计
│   ├── 主键自动创建索引
│   ├── 外键建议创建索引
│   └── 常用查询字段创建索引
│
└── 4⃣ 避免过度关联
    ├── 单次查询不超过 5 张表
    └── 使用缓存减少查询

转换规则：
│
├── 1⃣ 实体 → 表
│   └── 每个实体转换为一张表
│
├── 2⃣ 属性 → 字段
│   └── 实体的属性转换为表的字段
│
├── 3⃣ 主键 → PRIMARY KEY
│   └── 标识符转换为主键
│
├── 4⃣ 一对一关系
│   ├── 方案一：合并为一张表
│   └── 方案二：外键 + UNIQUE
│
├── 5⃣ 一对多关系
│   └── 外键在"多"的一方
│
└── 6⃣ 多对多关系
    └── 创建中间表

-- 1. 字典表（被引用）
CREATE TABLE attributes (...);

-- 2. 用户表（被引用）
CREATE TABLE users (...);

-- 3. 首页资源表（独立）
CREATE TABLE home_resources (...);

-- 4. 课程表（引用 users）
CREATE TABLE courses (...);

-- 5. 课程章节表（引用 courses）
CREATE TABLE course_contents (...);

-- 6. 附件表（引用 users）
CREATE TABLE attachments (...);

-- 7. 附件属性表（引用 attachments、attributes）
CREATE TABLE attachment_attributes (...);

-- 8. 评论表（引用 users）
CREATE TABLE comments (...);

-- 9. 关联表（引用多个表）
CREATE TABLE course_comments (...);
CREATE TABLE course_tags (...);

ER 图记忆技巧：
│
├── 三大元素
│   └── 实体（矩形）+ 属性（字段）+ 关系（连线）
│
├── 四种关系
│   ├── 1:1 → 外键 + UNIQUE
│   ├── 1:N → 外键在"多"方
│   ├── 0:1 → 外键允许 NULL
│   └── M:N → 中间表
│
└── 设计流程
    └── 分析 → 实体 → 属性 → 关系 → 优化

学习路径规划：
│
├── 第一阶段：理解概念（1 天）
│   ├── 理解 ER 图的作用
│   ├── 理解三种核心元素
│   └── 理解四种关联关系
│
├── 第二阶段：工具实践（2-3 天）
│   ├── 学会使用 dbdiagram.io
│   ├── 练习绘制简单 ER 图
│   └── 练习导出 SQL
│
└── 第三阶段：项目实战（持续）
    ├── 设计完整系统 ER 图
    ├── 评审和优化设计
    └── 转换为数据库实现

ER 图设计知识图谱：
│
├── 基本概念
│   ├── 实体（Entity）
│   ├── 属性（Attribute）
│   └── 关系（Relationship）
│
├── 四种关联关系
│   ├── 一对一（1:1）
│   ├── 一对多（1:N）
│   ├── 零对一（0:1）
│   └── 多对多（M:N）
│
├── 设计工具
│   ├── dbdiagram.io
│   ├── Draw.io
│   ├── Navicat
│   └── MySQL Workbench
│
├── 设计流程
│   ├── 需求分析
│   ├── 确定实体
│   ├── 定义属性
│   ├── 建立关系
│   └── 优化调整
│
├── 实战案例
│   ├── 用户系统
│   ├── 课程系统
│   └── 评论系统
│
└── 最佳实践
    ├── 布局原则
    ├── 命名规范
    └── 性能优化