数据库设计：范式与 ER 图

9.1 为什么需要规范化

不良的表设计会导致数据冗余（同一数据在多处存储），进而引发三种"更新异常"：

反面示例：不规范的 orders 表

order_id	customer_name	customer_email	product_name	category	price
1	Alice	alice@ex.com	键盘	电脑外设	599
2	Alice	alice@ex.com	鼠标	电脑外设	129
3	Bob	bob@ex.com	键盘	电脑外设	599

• 更新异常：Alice 改邮箱需要更新多行，漏改一行就不一致
• 插入异常：想添加一个新商品分类，但没有对应订单，无法插入
• 删除异常：删除 Bob 的订单，Bob 这个客户的信息也被删掉了

解决方案：把客户信息、商品信息、订单信息分别放到独立的表中。这就是规范化（Normalization）。

9.2 ER 图（实体关系图）

ER 图（Entity-Relationship Diagram）是数据库设计的"蓝图"，在写 SQL 之前用图形表达数据模型。

• 实体（Entity） 现实世界中可以独立存在的"东西"，对应数据库中的一张表。如：用户、商品、订单。用矩形表示。
• 属性（Attribute） 实体的特征，对应表中的列。如：用户的 username、email。用椭圆表示（或直接列在矩形内）。
• 关系（Relationship） 实体之间的联系。如：用户"下"订单，订单"包含"商品。用菱形或连接线表示。
• 基数（Cardinality） 关系的数量约束，有三种：1:1（一对一）、1:N（一对多）、M:N（多对多）。

9.3 函数依赖

• 函数依赖 X → Y 对于每一个 X 的值，Y 有且只有一个确定值与之对应。如：学号 → 姓名（知道学号就能唯一确定姓名）。
• 完全函数依赖 Y 依赖于整个复合主键 (X1, X2)，去掉任意一个部分，Y 就不再确定。如：(订单ID, 商品ID) → 数量。
• 部分函数依赖 Y 只依赖于复合主键的一部分。如：(订单ID, 商品ID) → 商品名称，商品名称只依赖于商品ID，与订单ID无关，这就是部分依赖，是 2NF 要消除的。
• 传递函数依赖 学号 → 院系编号 → 院长姓名，院长姓名通过院系编号传递依赖于学号，是 3NF 要消除的。

9.4 第一范式（1NF）

1NF 要求：表中每一列都是原子值（不可再分），不允许多值属性或重复组。

违反 1NF 的例子

student_id	name	phones（多值）
1	Alice	13812345678, 02188888888

改为 1NF

把多值属性拆分到单独的行，或创建独立的 phones 表：

SQLCREATE TABLE student_phones (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  phone_type ENUM('mobile', 'home', 'work') DEFAULT 'mobile'
);

9.5 第二范式（2NF）

2NF 要求：满足 1NF，且每个非主键属性完全依赖于主键（消除部分函数依赖）。2NF 仅在复合主键时才需要关注。

违反 2NF 的例子（订单明细表）

(order_id, product_id) 复合主键	quantity	product_name（只依赖 product_id）	product_price（只依赖 product_id）
(1, 101)	2	键盘	599
(1, 102)	1	鼠标	129
(2, 101)	1	键盘	599（重复！）

改为 2NF

SQL-- 把依赖 product_id 的属性拆出去
CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  price DECIMAL(10,2)
);

CREATE TABLE order_items (
  order_id   INT,
  product_id INT,
  quantity   INT NOT NULL,
  -- 保存下单时的快照价格（允许冗余，因为价格可变）
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

9.6 第三范式（3NF）

3NF 要求：满足 2NF，且每个非主键属性直接依赖于主键，不存在传递依赖。

违反 3NF 的例子

employee_id (PK)	name	dept_id	dept_name（传递依赖：employee → dept_id → dept_name）
1	Alice	D01	研发部
2	Bob	D01	研发部（冗余！）
3	Carol	D02	市场部

改为 3NF

SQL-- 把传递依赖的属性拆到单独的表
CREATE TABLE departments (
  id   CHAR(3) PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
  id      INT PRIMARY KEY,
  name    VARCHAR(50),
  dept_id CHAR(3) REFERENCES departments(id)
  -- dept_name 不在这里，需要时 JOIN departments 表
);

9.7 BCNF（Boyce-Codd 范式）

BCNF 比 3NF 更严格：对于每一个函数依赖 X → Y，X 必须是超键（能唯一标识整行的列集合）。

BCNF 主要解决 3NF 中仍存在的一种特殊情况：非主键属性决定了候选键的一部分。在实际业务中遇到较少，大多数表达到 3NF 即可。

9.8 反范式化（Denormalization）

规范化减少了冗余，但也增加了 JOIN 操作。在读多写少的高并发场景下，可以故意引入冗余来换取查询性能，这叫反范式化。

SQL-- 反范式化示例：在 posts 表中冗余存储 comment_count
-- 规范化做法：每次需要评论数时 SELECT COUNT(*) FROM comments WHERE post_id = ?
-- 反范式化：直接在 posts.comment_count 存储冗余值

ALTER TABLE posts ADD COLUMN comment_count INT NOT NULL DEFAULT 0;

-- 插入评论时同步更新 comment_count（通过触发器自动维护）
CREATE TRIGGER trig_inc_comment_count
AFTER INSERT ON comments
FOR EACH ROW
  UPDATE posts SET comment_count = comment_count + 1
  WHERE id = NEW.post_id;

9.9 多对多关系的实现

关系型数据库不能直接表示 M:N 关系，必须通过中间表（联结表/关联表）实现。

SQL-- 示例：文章 与 标签 是 M:N 关系（一篇文章可有多个标签，一个标签可属于多篇文章）
CREATE TABLE tags (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (           -- 中间表
  post_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),   -- 复合主键防止重复
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

-- 查询某篇文章的所有标签
SELECT t.name
FROM tags t
INNER JOIN post_tags pt ON t.id = pt.tag_id
WHERE pt.post_id = 1;

-- 查询某个标签下所有文章
SELECT p.title
FROM posts p
INNER JOIN post_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE t.name = '数据库';

9.10 实战：完整电商数据库设计

SQL-- 用户表
CREATE TABLE users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  email      VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- 商品表
CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock       INT UNSIGNED NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 收货地址表（用户可有多个地址）
CREATE TABLE addresses (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  receiver   VARCHAR(50) NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  province   VARCHAR(50) NOT NULL,
  city       VARCHAR(50) NOT NULL,
  street     VARCHAR(200) NOT NULL,
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 订单表（主表）
CREATE TABLE orders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  address_id  INT UNSIGNED NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status      ENUM('pending','paid','shipped','completed','cancelled')
              NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)    REFERENCES users(id),
  FOREIGN KEY (address_id) REFERENCES addresses(id),
  INDEX idx_orders_user_id (user_id)
);

-- 订单明细表（实现 orders 与 products 的多对多）
CREATE TABLE order_items (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,    -- 快照价格
  FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  INDEX idx_items_order_id (order_id)
);

电商 ER 关系汇总