关系数据建模

实体关系模型在选择表之前识别实体类型、实体属性及其关系。随后，关系模式以定义的粒度对每个事实只表示一次。例如，order_line 表示某个订单中的一个产品，而 order 表示订单头。

实体、键和关系 #

每个表都需要一个能够唯一标识其行的候选键。选择主键时，应确保其稳定、非空并适合连接。使用代理键并不意味着不需要针对自然业务标识建立唯一约束，例如账户的外部标识符。

按以下方式映射关系基数：

1. 对于一对一关系，在可选的一侧放置 UNIQUE 外键；如果两个表的生命周期和访问控制一致，也可以合并这些表。
2. 对于一对多关系，将外键放在多的一侧的表中。
3. 对于多对多关系，创建一个关联表，其中包含指向两个实体的外键，以及属于该关系的属性。

NOT NULL 表示必须参与该关系。可为空的外键表示对于该行而言，该关系是可选的。外键约束可以防止孤立引用，而 ON DELETE 的行为必须反映业务规则：限制删除、级联删除、设置为 NULL，或在显式工作流中处理删除。

规范化 #

规范化从函数依赖开始：如果 X 的一个值决定 Y 的一个值，则写作 X -> Y。第一范式要求每列包含声明域中的一个值，并且不存在重复组。第二范式移除仅依赖于复合候选键一部分的非键属性。第三范式移除传递依赖，即某个非键属性决定另一个非键属性。这些规则使每个事实拥有唯一的权威位置，从而防止插入、更新和删除异常。

只有在测量读取成本并定义如何保证重复值持续正确之后，才应进行反规范化。记录副本由事务、触发器、生成列、变更数据捕获消费者还是重建作业维护；然后监控数据陈旧程度并执行对账。索引、汇总表或查询重写可以解决读取路径问题，而无需创建不受控制的业务数据副本。

CREATE TABLE order_line (
  order_id BIGINT NOT NULL,
  line_number SMALLINT UNSIGNED NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_order_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT fk_order_line_product
    FOREIGN KEY (product_id) REFERENCES product (id)
) ENGINE = InnoDB;

复合主键明确表示了该表的粒度：一个订单内的某个行号只存在一次。InnoDB要求存在一个索引，其前导列覆盖每个外键，并且可能会自动创建该索引；在添加冗余索引之前，应检查 SHOW CREATE TABLE 和查询计划。

参考资料 #

• InnoDB外键约束
• MySQL约束