📚 课程目录第4章从ER图到逻辑模型

4. 从ER图到逻辑模型：关系模式设计、主键与外键、范式化与反范式化权衡

实体-关系图（ER图）是领域建模的视觉化产物，而逻辑模型则是将ER图转化为数据库或索引可实现的表结构。本章聚焦于如何将ER图中的实体、属性和关系，精确映射为关系模式，并深入探讨主键/外键设计、范式化与反范式化的核心权衡。

4.1 从ER图到关系模式的映射规则

将ER图转换为关系模式（表结构）遵循一套标准规则，确保语义无损且结构合理。以下是核心映射步骤：

ER图元素	关系模式映射规则	示例
强实体	每个强实体独立映射为一张表，实体的属性成为表的列。	实体“用户” → 表 `users`
弱实体	映射为一张表，必须包含其依赖的强实体的主键作为外键，且自身主键通常由外键+部分键组成。	实体“订单项” → 表 `order_items`，包含 `order_id` 外键
1:1 关系	将一方的主键作为外键放入另一方表中（任选一方，通常选择总访问量较少的一方）。	用户与用户详情：在 `user_details` 表中加入 `user_id` 外键
1:N 关系	将“1”方的主键作为外键放入“N”方表中。	用户与订单：在 `orders` 表中加入 `user_id` 外键
M:N 关系	创建一张独立的关联表，包含两个实体的主键作为联合主键或复合主键。	学生与课程：创建 `enrollments` 表，包含 `student_id` 和 `course_id`
复合属性	将复合属性拆分为多个简单列，或单独创建一张表。	属性“地址” → 拆分为 `street`, `city`, `zip`
多值属性	创建一张新表，包含原实体主键作为外键，以及该多值属性列。	属性“电话号码” → 创建 `user_phones` 表

映射原则： 优先保证“每个非空原子属性只存储一次”，这是后续范式化的基础。在搜索引擎索引设计中，M:N 关系往往通过嵌套文档或反向索引来优化，而非单纯依赖关联表。

4.2 主键与外键：数据完整性的基石

主键（Primary Key）和外键（Foreign Key）是关系模式中维护数据完整性的核心机制。在索引设计中，它们同样影响文档的唯一标识和实体间的引用方式。

4.2.1 主键设计策略

自然主键： 使用业务上具有唯一性的属性（如 ISBN、身份证号）。优点是无冗余，缺点是可能因业务变更而失效。
代理主键： 使用无业务含义的自增ID或UUID。优点是稳定、高效，适合作为外键引用。在搜索引擎中，文档ID通常采用代理主键。
复合主键： 由多个列共同组成唯一标识。常用于关联表或弱实体表。

4.2.2 外键与引用完整性

外键用于建立表之间的引用关系，确保子表中的记录必须对应父表中存在的记录。在关系数据库中，外键约束自动维护引用完整性。在搜索引擎索引中，外键概念被“文档ID引用”或“嵌套文档”替代，但逻辑等价。

  索引设计启示： 在搜索引擎中，外键关系通常通过 parent_id 字段或 nested 数据类型实现。例如，Elasticsearch 中一个订单文档可以嵌套多个订单项文档，避免了传统关系数据库中的 JOIN 操作。

4.3 范式化：消除冗余，保证一致性

范式化（Normalization）是一套逐步分解表结构、消除数据冗余和更新异常的设计方法。常见的范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和 Boyce-Codd 范式（BCNF）。

范式	核心要求	解决的问题	示例违反
1NF	每列都是原子值，不可再分；每行唯一。	消除重复组和数组	一个字段存储多个电话号码
2NF	满足1NF，且所有非主键列完全依赖于主键（消除部分依赖）。	复合主键下的部分依赖	订单项表中存储商品名称（仅依赖商品ID，而非订单ID+商品ID）
3NF	满足2NF，且所有非主键列直接依赖于主键（消除传递依赖）。	非主键列依赖于其他非主键列	员工表中存储部门名称（部门名称依赖于部门ID，而非员工ID）
BCNF	满足3NF，且每个决定因素都是候选键。	主键列对非主键列的依赖	学生-课程-教师表中，教师决定课程，但教师不是候选键

注意： 过度范式化（如达到5NF或6NF）在搜索引擎索引中很少使用，因为会导致大量表关联，严重影响查询性能。在索引设计中，通常只保证达到3NF即可。

4.4 反范式化：以空间换时间

反范式化（Denormalization）是有意引入冗余数据，以减少查询时的表关联次数，提升读取性能。在搜索引擎索引设计中，反范式化是核心策略之一。

4.4.1 反范式化的常见场景

预计算聚合： 在订单表中直接存储“订单总金额”，避免每次查询时计算所有订单项价格之和。
冗余引用字段： 在订单项中直接存储“商品名称”和“商品分类”，避免关联商品表。
数组/嵌套结构： 在用户文档中直接嵌套其所有地址信息，而非通过外键关联地址表。

4.4.2 范式化 vs 反范式化：权衡矩阵

维度	范式化	反范式化
数据一致性	高（单点更新，无冗余）	低（多副本更新，可能不一致）
写入性能	快（只更新一张表）	慢（需更新多张表或多个文档）
读取性能	慢（需要 JOIN 或多次查询）	快（单次查询即可获取完整数据）
存储空间	小（无冗余）	大（存在冗余）
索引复杂度	低（表结构简单）	高（需处理嵌套、数组等复杂类型）
适用场景	OLTP、强一致性要求	OLAP、搜索引擎、读多写少

4.5 范式化与反范式化的权衡决策流程

下图展示了从ER图到最终逻辑模型时，如何根据业务需求在范式化和反范式化之间做出决策。

4.6 搜索引擎索引中的反范式化实践

在搜索引擎索引（如 Elasticsearch、Solr）中，反范式化是默认策略。以下是一个典型的电商搜索索引设计示例：

4.6.1 范式化关系模式（数据库层）

-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(200),
    category_id INT,
    supplier_id INT
);

-- 分类表
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_category_id INT
);

-- 供应商表
CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    name VARCHAR(200),
    rating DECIMAL(2,1)
);

4.6.2 反范式化索引文档（搜索引擎层）

{
  "product_id": 1001,
  "name": "无线蓝牙耳机",
  "category_name": "电子产品 > 音频设备",
  "category_path": ["电子产品", "音频设备"],
  "supplier_name": "声学科技",
  "supplier_rating": 4.5,
  "price": 299.00,
  "stock": 150,
  "tags": ["蓝牙", "降噪", "无线"]
}

  关键洞察： 在搜索引擎索引中，category_name、supplier_name 等字段被直接冗余存储到商品文档中。这使得搜索时无需关联分类表和供应商表，查询速度提升数倍。代价是当分类名称或供应商信息变更时，需要更新所有相关商品文档。

4.7 本章小结

映射规则： 从ER图到关系模式需遵循实体→表、属性→列、关系→外键或关联表的标准化映射。
主键与外键： 主键确保行唯一性，外键维护引用完整性。在索引设计中，文档ID是主键，嵌套或父文档ID是外键的等价物。
范式化： 通过1NF、2NF、3NF逐步消除冗余，保证数据一致性，适用于OLTP场景。
反范式化： 通过引入冗余提升读取性能，是搜索引擎索引的核心设计策略，适用于OLAP和搜索场景。
权衡决策： 根据读写比例、一致性要求和存储成本，在范式化和反范式化之间做出合理选择。搜索引擎索引通常采用“数据库层范式化 + 索引层反范式化”的混合策略。