跳到主要内容

ER 模型

问题​

什么是 ER 模型？如何画 ER 图进行数据库设计？

答案​

ER 模型（Entity-Relationship Model，实体-关系模型） 是数据库设计的经典工具，用图形化的方式描述现实世界中的数据和数据之间的关系。ER 图是需求分析到表结构设计之间的桥梁。

简单来说：先画 ER 图理清实体和关系，再把 ER 图"翻译"成数据库表。

ER 模型的三个核心要素​

1. 实体（Entity）​

实体是现实世界中可以被区分的"事物"。在数据库中，一个实体通常对应一张表。

实体分为两种：

  • 强实体：可以独立存在，有自己的主键（如用户、商品）
  • 弱实体：依赖于其他实体才能存在（如订单项依赖于订单）

2. 属性（Attribute）​

属性描述实体的特征。在数据库中，属性对应表中的列。

属性类型：

类型说明示例
简单属性不可再分的原子值年龄、性别
复合属性可以拆分为更小的部分地址 → 省+市+区+详细地址
多值属性一个实体可以有多个值一个人有多个手机号
派生属性可以从其他属性计算得出年龄可以从生日计算
键属性用来唯一标识实体的属性学号、身份证号

3. 关系（Relationship）​

关系描述实体之间的关联方式。在数据库中，关系通过外键或中间表来实现。

关系有三种基数（Cardinality）：

关系类型符号表示说明示例
一对一（1:1）||--||一个实体对应另一个实体的唯一记录用户 ↔ 身份证
一对多（1:N）||--o{一个实体对应多个另一个实体的记录用户 → 订单
多对多（M:N）}o--o{两个实体之间互相对应多条记录学生 ↔ 课程

ER 图绘制实战​

场景：在线商城​

需求：用户可以浏览商品、下单购买、对商品进行评价。

第一步：识别实体

  • 用户（User）
  • 商品（Product）
  • 订单（Order）
  • 订单项（OrderItem）
  • 评价（Review）
  • 分类（Category）

第二步：识别属性和关系

第三步：转化为数据库表

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分类表（自引用：支持多级分类）
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    description TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 订单项表（订单和商品的多对多关系）
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,  -- 下单时的价格快照
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 评价表
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
设计要点
  • order_items.unit_price 记录下单时的价格快照，商品调价后不影响历史订单
  • categories.parent_id 自引用实现多级分类（电子产品 → 手机 → 智能手机）
  • reviews 表同时关联用户和商品，形成"谁对什么商品评了什么"的关系

ER 图转化为表的规则​

ER 模型元素转化规则
强实体直接创建表，属性变为列，键属性变为主键
弱实体创建表，主键 = 自身部分键 + 所依赖实体的主键
1:1 关系外键放任一方，或合并为一张表
1:N 关系外键放在"多"的一方
M:N 关系创建中间表，两个外键分别指向两张主表
多值属性创建独立的表来存储
派生属性通常不存储，通过计算得出

常见面试问题​

Q1: 给你一个业务场景，怎么设计数据库表？​

答案：

数据库设计的标准流程：

  1. 需求分析：梳理业务需求，搞清楚"系统要管理什么数据"
  2. 识别实体：找出核心业务对象（用户、订单、商品等）
  3. 识别属性：确定每个实体有哪些字段
  4. 识别关系：确定实体之间的关联关系（1:1、1:N、M:N）
  5. 画 ER 图：可视化表达以上信息
  6. 转化为表：按规则将 ER 图转化为 SQL DDL
  7. 范式检查：检查是否满足 3NF，是否需要反范式优化
  8. 索引设计：为高频查询的字段添加索引

Q2: 什么是弱实体？举个例子​

答案：

弱实体是不能独立存在、必须依赖于其他实体的实体。弱实体没有自己的完整主键，需要借助依赖的实体的主键来唯一标识。

例子：订单项（OrderItem）是弱实体，它依赖于订单（Order）。没有订单，订单项就没有意义。

-- 订单项的主键需要包含 order_id
CREATE TABLE order_items (
    order_id INT,        -- 来自订单表
    item_seq INT,        -- 在订单内的序号
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, item_seq),  -- 复合主键
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

再比如：房间（Room）依赖于建筑物（Building）。"301 室"必须说清楚是"A 栋 301 室"才有意义。

Q3: 自引用关系是什么？怎么设计？​

答案：

自引用关系是实体引用自身的情况，最常见的场景是树形结构。

-- 员工表：经理也是员工
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,                              -- 自引用：指向上级经理
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 分类表：支持多级分类
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,                               -- 自引用：指向父分类
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);
自引用的查询挑战

自引用表的递归查询需要使用 CTE（Common Table Expression）：

-- 查询某个分类的所有子分类（MySQL 8.0+ / PostgreSQL）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Q4: 什么时候用 1:1 关系？为什么不合并成一张表？​

答案：

1:1 关系在以下场景下值得拆分：

  1. 访问频率不同：用户基本信息（频繁查询） vs 用户详细资料（偶尔查询）
  2. 权限控制：敏感数据单独存放（如手机号、身份证号单独一张表）
  3. 表字段过多：将一张超宽表拆分为多张窄表，减少 I/O
  4. 扩展性：子系统各自维护自己的扩展表

如果数据量小且总是一起查询，合并为一张表更好（减少 JOIN 开销）。

Q5: 如何处理多对多关系？​

答案：

多对多关系必须通过中间表来实现。中间表记录两个实体之间的关联关系，通常还可以存储关系本身的属性。

-- 学生和课程的多对多关系
-- 学生表
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50));
-- 课程表
CREATE TABLE courses (id INT PRIMARY KEY, name VARCHAR(50));
-- 中间表：选课记录（关系本身带有属性）
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade DECIMAL(3,1),           -- 关系属性：成绩
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);
中间表的主键选择
  • 联合主键 (student_id, course_id)：如果一个学生同一门课只能选一次
  • 自增主键 + 唯一约束：如果需要记录多次选课（重修）
CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    UNIQUE (student_id, course_id, semester)  -- 一个学期选一次
);

Q6: ER 模型中如何处理"继承"关系？​

答案：

当多个实体有共同属性时（如"学生"和"教师"都是"人"），有三种设计方案：

-- 方案 1：单表继承（STI）
-- 一张表存所有类型，用 type 列区分
CREATE TABLE persons (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    type ENUM('student', 'teacher'),
    -- 学生专有
    grade INT,
    -- 教师专有
    department VARCHAR(50),
    title VARCHAR(20)
);
-- 优点：简单，无需 JOIN
-- 缺点：很多 NULL，不适合差异大的情况

-- 方案 2：类表继承（CTI）
-- 共有属性放父表，特有属性放子表
CREATE TABLE persons (id INT PRIMARY KEY, name VARCHAR(50), type VARCHAR(20));
CREATE TABLE students (person_id INT PRIMARY KEY, grade INT, FOREIGN KEY (person_id) REFERENCES persons(id));
CREATE TABLE teachers (person_id INT PRIMARY KEY, department VARCHAR(50), FOREIGN KEY (person_id) REFERENCES persons(id));
-- 优点：结构清晰，无冗余
-- 缺点：查询需要 JOIN

-- 方案 3：具体表继承
-- 每个实体一张独立的表，不共享父表
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), grade INT);
CREATE TABLE teachers (id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50));
-- 优点：各自独立
-- 缺点：共有属性重复，无法统一查询

Q7: 常用的 ER 图工具有哪些？​

答案：

工具类型特点
MySQL Workbench免费MySQL 官方，可直接生成 SQL
DBeaver免费通用数据库工具，支持 ER 图生成
dbdiagram.io在线用代码画 ER 图，分享方便
draw.io在线/桌面通用画图工具，ER 图模板丰富
DataGrip付费JetBrains 出品，专业 IDE
PowerDesigner付费企业级建模工具
Mermaid代码Markdown 嵌入 ER 图（本文使用）

设计进阶：ER 模型到实际开发的注意事项​

1. 主键选择策略​

策略优点缺点适用场景
自增整数简单、性能好分库分表不友好单库小项目
UUID全局唯一占空间大、不连续分布式系统
雪花算法有序+全局唯一依赖时钟分布式系统（推荐）
自然主键语义清晰可能变更不推荐

2. 审计字段​

几乎每张表都应该包含的字段：

CREATE TABLE any_table (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    -- ... 业务字段 ...
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT,     -- 创建人
    updated_by BIGINT,     -- 最后修改人
    is_deleted TINYINT DEFAULT 0  -- 软删除标志
);

3. 字段命名规范​

规范示例说明
使用下划线命名user_name不用 userName（SQL 对大小写不敏感）
外键 = 关联表名_iduser_id, order_id一眼看出关联关系
布尔字段用 is_ 前缀is_active, is_deleted语义清晰
时间字段用 _at 后缀created_at, expired_at统一风格
避免使用保留字不用 order, key, index 作为列名防止 SQL 冲突

相关链接​