📚 课程目录第3章数据库设计与范式工程

3. 数据库设计与范式工程

数据库设计不是“先建表再补字段”，而是一条从业务语义到物理存储的收敛链路。企业级项目里，表结构一旦上线，后续每次变更都要付出迁移、兼容和数据修复成本。因此，设计阶段必须把需求、实体关系、范式约束和物理落地分开处理，再逐层验证。

一、需求分析：先定义数据边界与访问方式

需求分析的目标不是罗列功能，而是识别“哪些对象需要持久化、它们之间如何关联、谁以什么频率读写”。以订单系统为例，典型需求可归纳为：

客户可下多笔订单，每笔订单包含多个商品明细；
商品属于某个类目，价格可能随促销变化；
订单状态需支持待支付、已支付、已发货、已完成、已取消；
高频查询为“按客户查订单列表”和“按订单号查明细”，低频查询为“按类目统计销量”。

这些信息决定了后续的实体划分、索引策略和是否允许冗余。需求阶段应输出一份数据字典雏形，至少包含字段名、业务含义、类型、是否可空、默认值和来源。例如 order_status 用 TINYINT 还是 VARCHAR，取决于状态是否固定、是否需要可读性；若状态固定且查询频繁，用 TINYINT 配合字典表更节省空间。

二、ER建模：把业务语义转成实体与关系

ER建模的核心是识别实体、属性和联系基数。订单系统中，customer、order、order_item、product、category 是实体；customer 与 order 是 1:N，order 与 order_item 是 1:N，product 与 category 是 N:1。多对多关系必须拆成中间表，例如商品与订单的多对多通过 order_item 落地，并在中间表上记录数量、成交单价等关系属性。

ER图不追求画得漂亮，而追求“基数正确、可选性明确”。例如订单是否必须属于客户？若允许游客下单，则 customer_id 可空，但会带来后续对账困难；若强制注册，则 customer_id 非空。这个选择直接影响物理模型的约束和索引。

三、范式与反范式：约束与性能的权衡

范式解决的是“同一事实只存一处”，减少插入、更新和删除异常。第一范式要求字段原子性，第二范式要求非主属性完全依赖主键，第三范式要求消除传递依赖。以订单明细为例，若把 product_name、category_name 直接冗余到 order_item，就违反了第三范式，因为商品名依赖 product_id，而 product_id 又依赖订单明细主键。

但企业级系统并不总是追求最高范式。订单明细中冗余 product_name 和 unit_price 是合理的反范式：商品改名或调价后，历史订单必须保留成交时的快照。此时冗余不是“错误”，而是业务语义要求。判断标准是：该字段是否代表“当时的事实”，且是否会被高频读取。若只是为省一次 JOIN 而冗余，则要谨慎，因为更新异常和维护成本会上升。

四、从逻辑模型到物理模型：类型、索引与约束

逻辑模型确定实体和关系后，物理模型要落到具体数据库对象。以下是一段可直接参考的建表片段，展示订单核心表的物理落地方式：

CREATE TABLE `order` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` BIGINT UNSIGNED NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer_create` (`customer_id`, `create_time`),
  KEY `idx_status_create` (`status`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_item` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `product_name` VARCHAR(128) NOT NULL COMMENT '下单时商品名快照',
  `unit_price` DECIMAL(10,2) NOT NULL COMMENT '成交单价快照',
  `qty` INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_product` (`order_id`, `product_id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里有几个关键判断：order 表用 idx_customer_create 支撑“按客户查订单列表并按时间排序”；idx_status_create 支撑后台按状态筛选。金额用 DECIMAL 而非 FLOAT，避免浮点误差。状态用 TINYINT 并配注释，兼顾空间与可读性。order_item 对 (order_id, product_id) 建唯一索引，防止同一订单重复添加同一商品；若业务允许同一商品分多行（如不同促销批次），则应改为普通索引并增加批次字段。

反范式字段必须带“快照”语义，并在字段注释中写明来源和不可更新原因。否则后续维护者容易误以为可以随意同步更新，导致历史数据被污染。

五、设计验证：用查询与约束反推模型

物理模型落地后，不要只看表结构，而要用真实查询验证。例如执行 EXPLAIN SELECT * FROM `order` WHERE customer_id = 1001 ORDER BY create_time DESC LIMIT 20;，观察是否命中 idx_customer_create，以及是否出现 Using filesort。若出现，说明索引顺序或字段选择需要调整。再检查 order_item 的 uk_order_product 是否真的阻止了重复插入，可用事务回滚方式测试：

START TRANSACTION;
INSERT INTO order_item(order_id, product_id, product_name, unit_price, qty)
VALUES (1, 2001, '测试商品', 99.00, 1);
INSERT INTO order_item(order_id, product_id, product_name, unit_price, qty)
VALUES (1, 2001, '测试商品', 99.00, 1);
-- 预期第二条触发 Duplicate entry，验证唯一约束生效
ROLLBACK;

设计评审时，建议逐项确认：实体是否覆盖全部业务对象；关系基数是否与需求一致；范式取舍是否有明确理由；索引是否匹配高频查询；字段类型是否避免隐式转换；约束是否在应用层和数据库层保持一致。只有这些检查通过，逻辑模型才算真正落地为可维护的物理模型。