数据库设计三大范式

一、什么是数据库范式？

1.1 范式的定义

定义：数据库设计的一系列规范和原则，用于指导如何设计合理的关系型数据库表结构

核心目的：

减少数据冗余
避免数据异常
保证数据一致性
提高存储效率

1.2 三大范式概览

code

数据库三大范式：
│
├── 第一范式（1NF）
│   └── 原子性：字段不可再分
│
├── 第二范式（2NF）
│   └── 唯一性：消除部分依赖
│
└── 第三范式（3NF）
    └── 独立性：消除传递依赖

1.3 范式的作用

作用	说明
减少冗余	数据只存储一次，节省存储空间
避免异常	防止插入、更新、删除异常
保证一致性	数据修改时不会出现矛盾
提高可维护性	表结构清晰，易于理解和修改

二、第一范式（1NF）：原子性

2.1 定义

第一范式（1NF）：数据库表中的所有字段都是不可再分的原子值

核心要求：

不能有嵌套的数据结构
不能有重复的列
每个字段只存储一个值

2.2 反范式示例

问题表：院系职称人数表

code

┌──────────┬─────────────────────┐
│   院系   │     高级职称        │
├──────────┼─────────────────────┤
│ 计算机系 │ 教授：5，副教授：10 │
│ 数学系   │ 教授：3，副教授：8  │
│ 物理系   │ 教授：4，副教授：6  │
└──────────┴─────────────────────┘

问题：
 "高级职称"字段包含了教授和副教授，不是原子值
 字段内部还有嵌套结构
 无法直接查询教授人数

2.3 解决方案

方案一：扁平化设计

sql

--  方案一：拆分为多个字段
CREATE TABLE department_titles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  department VARCHAR(50),
  professor_count INT,     -- 教授人数
  associate_professor_count INT  -- 副教授人数
);

-- 数据示例
+----+-------------+-----------------+---------------------------+
| id | department  | professor_count | associate_professor_count |
+----+-------------+-----------------+---------------------------+
|  1 | 计算机系    |        5        |            10             |
|  2 | 数学系      |        3        |             8             |
|  3 | 物理系      |        4        |             6             |
+----+-------------+-----------------+---------------------------+

方案二：外键关联

sql

--  方案二：使用外键关联
-- 职称类型表
CREATE TABLE title_types (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title_name VARCHAR(50)  -- 教授、副教授、讲师等
);

-- 院系职称人数表
CREATE TABLE department_titles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  department VARCHAR(50),
  title_type_id INT,      -- 外键
  count INT,              -- 人数
  FOREIGN KEY (title_type_id) REFERENCES title_types(id)
);

-- 数据示例
-- title_types 表
+----+-------------+
| id | title_name  |
+----+-------------+
|  1 | 教授        |
|  2 | 副教授      |
|  3 | 讲师        |
+----+-------------+

-- department_titles 表
+----+-------------+--------------+-------+
| id | department  | title_type_id| count |
+----+-------------+--------------+-------+
|  1 | 计算机系    |       1      |   5   |
|  2 | 计算机系    |       2      |  10   |
|  3 | 数学系      |       1      |   3   |
|  4 | 数学系      |       2      |   8   |
+----+-------------+--------------+-------+

-- 查询：计算机系的教授人数
SELECT d.department, t.title_name, d.count
FROM department_titles d
INNER JOIN title_types t ON d.title_type_id = t.id
WHERE d.department = '计算机系' AND t.title_name = '教授';

2.4 第一范式检查要点

code

第一范式（1NF）检查清单：
│
├──  每个字段是否只存储一个值？
├──  是否存在嵌套的数据结构？
├──  是否存在重复的列？
├──  字段是否可以继续拆分？
│
└── 如果答案都是"否"，则符合第一范式

2.5 常见反范式情况

1. 逗号分隔存储

sql

--  错误示例：逗号分隔
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  tags VARCHAR(255)  -- "前端,Vue,React"
);

--  正确做法：使用关联表
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE tags (
  id INT PRIMARY KEY,
  tag_name VARCHAR(50)
);

CREATE TABLE user_tags (
  user_id INT,
  tag_id INT,
  PRIMARY KEY (user_id, tag_id)
);

2. JSON 字段存储

sql

--  虽然现代数据库支持 JSON，但不符合第一范式
CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_info JSON  -- {"items": [...], "total": 100}
);

--  正确做法：拆分为关联表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  total_amount DECIMAL(10, 2)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_name VARCHAR(100),
  quantity INT,
  price DECIMAL(10, 2)
);

三、第二范式（2NF）：消除部分依赖

3.1 定义

第二范式（2NF）：在满足第一范式的基础上，非主键字段必须完全依赖于主键，不能只依赖主键的一部分

适用场景：主要用于联合主键的表

核心要求：

不能存在部分依赖
非主键字段必须完全依赖于整个主键

3.2 反范式示例

问题表：职工项目信息表

sql

--  反范式示例
CREATE TABLE employee_projects (
  employee_id INT,        -- 职工号
  employee_name VARCHAR(50),  -- 姓名
  title_id INT,           -- 职称ID
  project_id INT,         -- 项目号
  project_name VARCHAR(100),  -- 项目名称
  PRIMARY KEY (employee_id, project_id)  -- 联合主键
);

-- 数据示例
+-------------+---------------+----------+------------+--------------+
| employee_id | employee_name | title_id | project_id | project_name |
+-------------+---------------+----------+------------+--------------+
|      1      |     张三      |     1    |     101    |   项目A      |
|      1      |     张三      |     1    |     102    |   项目B      |
|      2      |     李四      |     2    |     101    |   项目A      |
+-------------+---------------+----------+------------+--------------+

问题分析：
├── 联合主键：(employee_id, project_id)
├── employee_name 只依赖于 employee_id（部分依赖）
├── title_id 只依赖于 employee_id（部分依赖）
└── project_name 只依赖于 project_id（部分依赖）

依赖关系图：

code

联合主键：(employee_id, project_id)
          │
          ├───── employee_id ─────┬── employee_name（部分依赖）
          │                       └── title_id（部分依赖）
          │
          └───── project_id ─────── project_name（部分依赖）

问题：
 employee_name 不依赖于 project_id
 project_name 不依赖于 employee_id
 存在数据冗余（张三的信息重复）

3.3 解决方案

拆分为多个表：

sql

--  正确做法：拆分为三个表

-- 1. 职工信息表
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50),
  title_id INT,
  FOREIGN KEY (title_id) REFERENCES titles(id)
);

-- 2. 职称表
CREATE TABLE titles (
  id INT PRIMARY KEY,
  title_name VARCHAR(50),
  title_level VARCHAR(20)
);

-- 3. 项目信息表
CREATE TABLE projects (
  project_id INT PRIMARY KEY,
  project_name VARCHAR(100)
);

-- 4. 职工项目关联表（多对多关系）
CREATE TABLE employee_projects (
  employee_id INT,
  project_id INT,
  PRIMARY KEY (employee_id, project_id),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

-- 数据示例

-- employees 表
+-------------+---------------+----------+
| employee_id | employee_name | title_id |
+-------------+---------------+----------+
|      1      |     张三      |     1    |
|      2      |     李四      |     2    |
+-------------+---------------+----------+

-- titles 表
+----+------------+-------------+
| id | title_name | title_level |
+----+------------+-------------+
|  1 |   教授     |     高级    |
|  2 |   副教授   |     副高    |
+----+------------+-------------+

-- projects 表
+------------+--------------+
| project_id | project_name |
+------------+--------------+
|    101     |    项目A     |
|    102     |    项目B     |
+------------+--------------+

-- employee_projects 表
+-------------+------------+
| employee_id | project_id |
+-------------+------------+
|      1      |    101     |
|      1      |    102     |
|      2      |    101     |
+-------------+------------+

3.4 拆分的好处与代价

好处：

code

拆分的好处：
│
├── 1⃣ 消除数据冗余
│   └── 张三的信息只存储一次
│
├── 2⃣ 逻辑更加清晰
│   ├── 职工信息独立管理
│   ├── 项目信息独立管理
│   └── 关联关系清晰
│
├── 3⃣ 易于维护
│   ├── 修改职工信息只需改一处
│   └── 修改项目信息只需改一处
│
└── 4⃣ 符合单一职责原则
    └── 每个表只负责一类信息

代价：

code

拆分的代价：
│
├── 1⃣ 表数量增加
│   └── 从 1 张表变成 4 张表
│
├── 2⃣ 查询复杂度增加
│   └── 需要使用 JOIN 关联查询
│
├── 3⃣ 性能影响
│   └── 关联查询可能影响性能
│
└── 4⃣ 理解难度增加
    └── 需要理解表之间的关联关系

查询示例：

sql

-- 查询张三参与的所有项目
SELECT 
  e.employee_name,
  t.title_name,
  p.project_name
FROM employees e
INNER JOIN titles t ON e.title_id = t.id
INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE e.employee_name = '张三';

-- 结果
+---------------+------------+--------------+
| employee_name | title_name | project_name |
+---------------+------------+--------------+
|     张三      |    教授    |    项目A     |
|     张三      |    教授    |    项目B     |
+---------------+------------+--------------+

3.5 第二范式检查要点

code

第二范式（2NF）检查清单：
│
├── 1⃣ 是否满足第一范式？
├── 2⃣ 是否使用联合主键？
│   ├── 如果是单列主键 → 自动满足第二范式
│   └── 如果是联合主键 → 检查部分依赖
├── 3⃣ 非主键字段是否完全依赖于整个主键？
│   ├── 完全依赖 → 符合第二范式
│   └── 部分依赖 → 拆分表
│
└── 如果所有非主键字段都完全依赖于主键，则符合第二范式

3.6 部分依赖识别方法

识别步骤：

code

识别部分依赖的步骤：
│
├── 1⃣ 找出主键（单列或联合主键）
│
├── 2⃣ 对于每个非主键字段，问：
│   └── 这个字段依赖于主键的全部还是部分？
│
├── 3⃣ 如果只依赖部分主键
│   └── 存在部分依赖 → 需要拆分表
│
└── 4⃣ 如果依赖整个主键
    └── 符合第二范式

四、第三范式（3NF）：消除传递依赖

4.1 定义

第三范式（3NF）：在满足第二范式的基础上，非主键字段之间不能存在传递依赖关系

传递依赖：A → B → C（A 决定 B，B 决定 C，因此 A 间接决定 C）

核心要求：

不能存在传递依赖
非主键字段只依赖于主键

4.2 反范式示例

问题表：学生信息表

sql

--  反范式示例
CREATE TABLE students (
  student_id INT PRIMARY KEY,     -- 学号
  student_name VARCHAR(50),      -- 姓名
  college_id INT,                -- 学院ID
  college_name VARCHAR(100),     -- 学院名称
  college_phone VARCHAR(20)      -- 学院电话
);

-- 数据示例
+------------+--------------+------------+--------------+---------------+
| student_id | student_name | college_id | college_name | college_phone |
+------------+--------------+------------+--------------+---------------+
|     1      |     张三     |     1      |   计算机学院  |  010-12345678 |
|     2      |     李四     |     1      |   计算机学院  |  010-12345678 |
|     3      |     王五     |     2      |   数学学院   |  010-87654321 |
|     4      |     赵六     |     2      |   数学学院   |  010-87654321 |
+------------+--------------+------------+--------------+---------------+

问题分析：
├── student_id → student_name（直接依赖）
├── student_id → college_id（直接依赖）
├── college_id → college_name（直接依赖）
├── college_id → college_phone（直接依赖）
└── 因此：student_id → college_id → college_name/college_phone（传递依赖）

传递依赖关系图：

code

student_id（主键）
     │
     ├──→ student_name（直接依赖 ）
     │
     └──→ college_id ──┬──→ college_name（传递依赖 ）
                       │
                       └──→ college_phone（传递依赖 ）

问题：
 college_name 依赖于 college_id，而不是直接依赖于 student_id
 college_phone 依赖于 college_id，而不是直接依赖于 student_id
 数据冗余：计算机学院的信息重复存储了 2 次
 更新异常：修改学院电话需要修改多条记录

4.3 解决方案

拆分为两个表：

sql

--  正确做法：拆分为两个表

-- 1. 学院信息表
CREATE TABLE colleges (
  college_id INT PRIMARY KEY,
  college_name VARCHAR(100),
  college_phone VARCHAR(20)
);

-- 2. 学生信息表
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50),
  college_id INT,
  FOREIGN KEY (college_id) REFERENCES colleges(college_id)
);

-- 数据示例

-- colleges 表
+------------+--------------+---------------+
| college_id | college_name | college_phone |
+------------+--------------+---------------+
|     1      |   计算机学院  |  010-12345678 |
|     2      |   数学学院   |  010-87654321 |
+------------+--------------+---------------+

-- students 表
+------------+--------------+------------+
| student_id | student_name | college_id |
+------------+--------------+------------+
|     1      |     张三     |     1      |
|     2      |     李四     |     1      |
|     3      |     王五     |     2      |
|     4      |     赵六     |     2      |
+------------+--------------+------------+

4.4 拆分的好处

code

消除传递依赖的好处：
│
├── 1⃣ 消除数据冗余
│   └── 学院信息只存储一次
│
├── 2⃣ 避免更新异常
│   └── 修改学院电话只需改一处
│
├── 3⃣ 避免插入异常
│   └── 可以先创建学院，再添加学生
│
├── 4⃣ 避免删除异常
│   └── 删除学生不会删除学院信息
│
└── 5⃣ 数据更加规范
    └── 符合第三范式

更新异常示例：

sql

--  反范式情况下的更新异常
-- 修改计算机学院的电话，需要修改多条记录
UPDATE students 
SET college_phone = '010-99999999' 
WHERE college_id = 1;  -- 影响多条记录

--  正确做法：只需修改一条记录
UPDATE colleges 
SET college_phone = '010-99999999' 
WHERE college_id = 1;  -- 只修改一条

4.5 第三范式检查要点

code

第三范式（3NF）检查清单：
│
├── 1⃣ 是否满足第二范式？
│
├── 2⃣ 非主键字段之间是否存在依赖关系？
│   ├── 存在依赖 → 可能存在传递依赖
│   └── 不存在依赖 → 符合第三范式
│
├── 3⃣ 是否所有非主键字段都直接依赖于主键？
│   ├── 直接依赖 → 符合第三范式
│   └── 间接依赖 → 拆分表
│
└── 如果不存在传递依赖，则符合第三范式

4.6 传递依赖识别方法

识别步骤：

code

识别传递依赖的步骤：
│
├── 1⃣ 找出主键
│
├── 2⃣ 对于每个非主键字段，问：
│   └── 这个字段是否直接依赖于主键？
│
├── 3⃣ 如果字段 A 依赖于主键，字段 B 依赖于字段 A
│   └── 存在传递依赖：主键 → A → B
│
└── 4⃣ 解决方法：
    └── 将 A 和 B 拆分到新表

识别技巧：

code

快速识别传递依赖：
│
├── 看字段名称
│   └── 如 college_name、college_phone 都以 college_ 开头
│
├── 看数据重复
│   └── 如多个学生的学院信息相同
│
└── 问自己
    └── "这个字段是否可以独立存在？"
    ├── 可以独立存在 → 可能需要拆分
    └── 不能独立存在 → 符合第三范式

五、三大范式对比总结

5.1 对比表

范式	定义	核心要求	解决的问题	示例
第一范式	原子性	字段不可再分	嵌套结构	高级职称字段包含多个职称
第二范式	消除部分依赖	完全依赖主键	部分依赖	职工信息部分依赖联合主键
第三范式	消除传递依赖	直接依赖主键	传递依赖	学院信息通过学院ID传递依赖

5.2 范式递进关系

code

三大范式递进关系：
│
├── 第一范式（1NF）
│   └── 要求：字段不可再分
│       ↓ 满足后进入下一范式
│
├── 第二范式（2NF）
│   └── 要求：消除部分依赖
│       ↓ 满足后进入下一范式
│
└── 第三范式（3NF）
    └── 要求：消除传递依赖

5.3 范式判断流程

code

判断表是否符合范式的流程：
│
├── 1⃣ 检查第一范式
│   ├── 所有字段是否都是原子值？
│   ├── YES → 继续
│   └── NO → 扁平化字段
│
├── 2⃣ 检查第二范式
│   ├── 是否使用联合主键？
│   │   ├── YES → 检查是否存在部分依赖
│   │   │   ├── YES → 拆分表
│   │   │   └── NO → 继续
│   │   └── NO → 自动满足第二范式
│
└── 3⃣ 检查第三范式
    ├── 非主键字段之间是否存在依赖？
    │   ├── YES → 可能存在传递依赖，拆分表
    │   └── NO → 符合第三范式 
    │
    └── 完成！符合三大范式

六、反范式设计

6.1 什么是反范式？

定义：为了提高查询性能，故意违反范式规则，适当增加数据冗余

核心思想：空间换时间

6.2 反范式的应用场景

适合反范式的场景：

code

反范式适用场景：
│
├── 1⃣ 频繁查询的字段
│   └── 如订单表中冗余用户名称，避免 JOIN
│
├── 2⃣ 统计类字段
│   └── 如文章表中冗余评论数、点赞数
│
├── 3⃣ 历史数据快照
│   └── 如订单中冗余商品价格（下单时的价格）
│
└── 4⃣ 查询性能优先
    └── 高并发场景，减少 JOIN 操作

6.3 反范式示例

示例一：订单表冗余用户名称

sql

--  符合第三范式：需要 JOIN 查询
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  order_no VARCHAR(50),
  total_amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查询订单需要 JOIN
SELECT o.order_no, u.username, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

--  反范式：冗余用户名称
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  username VARCHAR(50),  -- 冗余字段
  order_no VARCHAR(50),
  total_amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查询订单不需要 JOIN
SELECT order_no, username, total_amount
FROM orders;

示例二：文章表冗余统计字段

sql

--  符合第三范式：每次查询评论数需要 COUNT
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT
);

CREATE TABLE comments (
  id INT PRIMARY KEY,
  article_id INT,
  content TEXT,
  FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 查询文章和评论数
SELECT a.title, COUNT(c.id) as comment_count
FROM articles a
LEFT JOIN comments c ON a.id = c.article_id
GROUP BY a.id;

--  反范式：冗余评论数
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  comment_count INT DEFAULT 0,  -- 冗余字段
  like_count INT DEFAULT 0      -- 冗余字段
);

-- 查询文章和评论数（不需要 JOIN）
SELECT title, comment_count, like_count
FROM articles;

-- 注意：需要在添加/删除评论时更新 comment_count

6.4 反范式的优缺点

优点：

code

反范式优点：
│
├──  减少 JOIN 操作
│   └── 提高查询性能
│
├──  简化查询语句
│   └── SQL 更简单易懂
│
└──  提高响应速度
    └── 适合高并发场景

缺点：

code

反范式缺点：
│
├──  数据冗余
│   └── 占用更多存储空间
│
├──  更新异常风险
│   └── 需要同步更新冗余字段
│
├──  数据一致性风险
│   └── 冗余字段可能不一致
│
└──  维护成本高
    └── 需要额外的逻辑保证一致性

6.5 反范式使用原则

code

反范式使用原则：
│
├── 1⃣ 优先符合范式
│   └── 只有性能瓶颈时才考虑反范式
│
├── 2⃣ 控制冗余字段数量
│   └── 只冗余最常用的字段
│
├── 3⃣ 保证数据一致性
│   └── 使用触发器或应用逻辑同步更新
│
├── 4⃣ 文档化
│   └── 注释说明冗余字段的作用和更新逻辑
│
└── 5⃣ 定期校验
    └── 定期检查冗余字段的一致性

七、实战案例：电商系统数据库设计

7.1 需求分析

功能需求：

用户管理（用户信息、角色权限）
商品管理（商品信息、分类、库存）
订单管理（订单、订单项、支付）
评论管理（评论、点赞）

7.2 数据库设计

ER 图：

code

┌──────────┐         ┌──────────┐         ┌──────────┐
│   用户   │         │   订单   │         │   商品   │
└────┬─────┘         └────┬─────┘         └────┬─────┘
     │ 1                   │ N                   │ 1
     │                     │                     │
     └─────────────────────┼─────────────────────┘
                           │ N
                           │
                    ┌──────┴──────┐
                    │  订单商品   │
                    └─────────────┘

7.3 完整建表语句

sql

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

-- 2. 商品分类表
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
  name VARCHAR(50) NOT NULL COMMENT '分类名称',
  parent_id INT DEFAULT NULL COMMENT '父分类ID',
  level TINYINT DEFAULT 1 COMMENT '层级',
  FOREIGN KEY (parent_id) REFERENCES categories(id),
  INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- 3. 商品表
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
  name VARCHAR(200) NOT NULL COMMENT '商品名称',
  category_id INT COMMENT '分类ID',
  price DECIMAL(10, 2) NOT NULL COMMENT '价格',
  stock INT DEFAULT 0 COMMENT '库存',
  sales INT DEFAULT 0 COMMENT '销量（冗余字段）',
  status TINYINT DEFAULT 1 COMMENT '状态：1-上架，0-下架',
  description TEXT COMMENT '描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX idx_category (category_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 4. 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号',
  user_id INT NOT NULL COMMENT '用户ID',
  username VARCHAR(50) COMMENT '用户名（冗余字段）',
  total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
  status TINYINT DEFAULT 0 COMMENT '状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消',
  payment_method VARCHAR(20) COMMENT '支付方式',
  payment_time DATETIME COMMENT '支付时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user (user_id),
  INDEX idx_order_no (order_no),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 5. 订单商品表
CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单商品ID',
  order_id INT NOT NULL COMMENT '订单ID',
  product_id INT NOT NULL COMMENT '商品ID',
  product_name VARCHAR(200) COMMENT '商品名称（冗余字段）',
  price DECIMAL(10, 2) NOT NULL COMMENT '商品单价（快照）',
  quantity INT NOT NULL COMMENT '购买数量',
  subtotal DECIMAL(10, 2) NOT NULL COMMENT '小计',
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  INDEX idx_order (order_id),
  INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表';

-- 6. 评论表
CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
  user_id INT NOT NULL COMMENT '用户ID',
  product_id INT NOT NULL COMMENT '商品ID',
  content TEXT NOT NULL COMMENT '评论内容',
  rating TINYINT COMMENT '评分：1-5',
  like_count INT DEFAULT 0 COMMENT '点赞数（冗余字段）',
  status TINYINT DEFAULT 1 COMMENT '状态：1-显示，0-隐藏',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  INDEX idx_user (user_id),
  INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';

7.4 范式分析

符合范式的设计：

code

符合范式的设计：
│
├──  用户表
│   └── 所有字段直接依赖于主键 id
│
├──  商品表
│   ├── category_id 通过外键关联
│   └── 其他字段直接依赖于主键 id
│
├──  订单表
│   ├── user_id 通过外键关联
│   └── 其他字段直接依赖于主键 id
│
└──  订单商品表
    ├── order_id、product_id 通过外键关联
    └── 联合主键保证唯一性

反范式设计：

code

反范式设计（性能优化）：
│
├──  orders 表
│   └── username（冗余用户名，避免 JOIN）
│
├──  order_items 表
│   ├── product_name（冗余商品名）
│   └── price（商品价格快照）
│
├──  products 表
│   └── sales（冗余销量统计）
│
└──  comments 表
    └── like_count（冗余点赞数统计）

八、最佳实践

8.1 数据库设计原则

code

数据库设计原则：
│
├── 1⃣ 优先符合范式
│   ├── 先设计符合第三范式
│   └── 后期根据性能优化
│
├── 2⃣ 合理使用反范式
│   ├── 只冗余必要字段
│   └── 保证数据一致性
│
├── 3⃣ 命名规范
│   ├── 表名：小写 + 下划线 + 复数
│   └── 字段名：小写 + 下划线
│
├── 4⃣ 添加注释
│   ├── 表注释：COMMENT '用户表'
│   └── 字段注释：COMMENT '用户ID'
│
└── 5⃣ 使用索引
    ├── 主键自动创建索引
    ├── 外键建议创建索引
    └── 常用查询字段创建索引

8.2 范式设计检查清单

code

范式设计检查清单：
│
├── 第一范式（1NF）
│   ├──  所有字段都是原子值？
│   ├──  没有嵌套结构？
│   └──  没有重复列？
│
├── 第二范式（2NF）
│   ├──  是否使用联合主键？
│   ├──  非主键字段完全依赖主键？
│   └──  没有部分依赖？
│
├── 第三范式（3NF）
│   ├──  非主键字段只依赖主键？
│   ├──  没有传递依赖？
│   └──  字段之间相互独立？
│
└── 反范式优化
    ├──  是否需要冗余字段？
    ├──  如何保证数据一致性？
    └──  文档是否清晰？

8.3 性能优化建议

sql

-- 1. 合理使用索引
CREATE INDEX idx_user_status ON users(status);

-- 2. 避免过度 JOIN
-- 查询订单时，如果频繁需要用户名，考虑冗余

-- 3. 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 4. 定期优化表
OPTIMIZE TABLE orders;

-- 5. 分表分库
-- 数据量大时，考虑按时间或 ID 分表

九、常见问题与解决方案

问题	原因	解决方案
数据冗余严重	不符合第三范式	拆分表，消除传递依赖
更新异常	数据冗余导致	符合范式，避免冗余
查询性能差	过度 JOIN	适当反范式，冗余字段
插入异常	部分依赖	拆分表，符合第二范式
删除异常	传递依赖	拆分表，符合第三范式
字段拆分困难	不符合第一范式	扁平化字段设计

十、学习要点总结

10.1 核心要点

第一范式：字段不可再分，保证原子性
第二范式：消除部分依赖，非主键字段完全依赖主键
第三范式：消除传递依赖，非主键字段只依赖主键
反范式：适当冗余，提高查询性能
设计原则：先符合范式，后性能优化

10.2 记忆技巧

code

三大范式记忆技巧：
│
├── 第一范式（1NF）
│   └── 原子性：字段不可再分
│       └── "拆分嵌套结构"
│
├── 第二范式（2NF）
│   └── 唯一性：消除部分依赖
│       └── "非主键完全依赖主键"
│
└── 第三范式（3NF）
    └── 独立性：消除传递依赖
        └── "非主键只依赖主键"

口诀：
"一原子，二完全，三直接"

10.3 学习路径

code

学习路径规划：
│
├── 第一阶段：理解概念（1-2 天）
│   ├── 理解三大范式的定义
│   ├── 理解部分依赖和传递依赖
│   └── 理解反范式设计
│
├── 第二阶段：实践练习（1 周）
│   ├── 设计符合范式的表
│   ├── 识别反范式问题
│   └── 优化数据库设计
│
└── 第三阶段：深入应用（持续）
    ├── 复杂业务数据库设计
    ├── 性能优化实践
    └── 分库分表设计

十一、延伸学习资源

11.1 官方文档

11.2 推荐阅读

《数据库系统概念》
《高性能 MySQL》
《SQL 反模式》

11.3 练习建议

基础练习：设计符合三大范式的学生管理系统
进阶练习：设计电商系统数据库
实战练习：优化现有项目的数据库设计
思考练习：什么情况下应该使用反范式？

十二、知识图谱

code

数据库设计三大范式知识图谱：
│
├── 第一范式（1NF）
│   ├── 定义：原子性
│   ├── 要求：字段不可再分
│   ├── 反范式：嵌套结构
│   └── 解决方案：扁平化
│
├── 第二范式（2NF）
│   ├── 定义：消除部分依赖
│   ├── 要求：完全依赖主键
│   ├── 反范式：部分依赖
│   └── 解决方案：拆分表
│
├── 第三范式（3NF）
│   ├── 定义：消除传递依赖
│   ├── 要求：直接依赖主键
│   ├── 反范式：传递依赖
│   └── 解决方案：拆分表
│
├── 反范式设计
│   ├── 目的：提高查询性能
│   ├── 代价：数据冗余
│   └── 原则：先范式，后反范式
│
└── 最佳实践
    ├── 优先符合范式
    ├── 合理使用反范式
    ├── 保证数据一致性
    └── 性能优化

笔记整理时间：2026-03-07
最后更新时间：2026-03-07