软件分析与设计笔记 18：数据库分析：实体、关系与数据字典

这是我在学习软件分析与设计时整理的系列笔记。概念部分是我按自己的理解写的，示例与图示都是自己编、自己画的。理解有偏差的地方欢迎指出。

贯穿案例：片区家电维修派单系统。09 篇抽出了领域模型，这一篇把它落成 ERD 与数据字典。

0 直接建表会出什么问题

需求刚谈完，最自然的下一个动作就是打开数据库工具开始建表。但这个顺序会带来三类麻烦：

• 字段名各写各的。 同一个人名，有的表叫 name，有的叫 custName，有的叫 c_name；
• 关系靠猜。 「一个客户能有多台机器」没人问过，开发按自己的理解建了外键；
• 类型和长度随手定。 电话字段给了 10 位，结果来了个带区号的号码。

放到本例里，最典型的「猜错」是这条：「一个工程师能同时属于多个片区吗？」 如果开发猜「不能」，就只在工程师表上加一个 district_id；等到旺季要跨片区支援，才发现要加一张关联表 —— 那时候已经有几千条数据要迁移。

数据库分析（database analysis）就是把这三件事在动手之前先确定下来：有哪些实体、它们之间是什么关系、每个字段叫什么、什么类型、什么业务规则。

顺带明确一下数据库管理系统（DBMS） 的位置：它位于应用和数据库之间，负责数据的检索与更新（关系型的那一类通常缩写为 RDBMS）。应用不直接碰文件，而是通过它来读写数据。

1 数据模型与实体关系图

1.1 三个元素与判断要点

数据模型（data model）在这个阶段就是用实体关系图（entity relationship diagram, ERD） 表达的。它由三样东西组成：

「关系」那一行值得多说一句。关系不是「有个箭头」，它本身有业务含义，所以要有名字：

• 客户与工单之间的菱形写「报修」—— 它代表一个业务动作；
• 工单与配件消耗之间的菱形写「包含」—— 它代表整体与部分；
• 配件消耗与库存项之间的菱形写「引用」—— 它代表一种较弱的关联。

菱形上写不写名字，是 ERD 合格与否的一个分界。不写名字的图，只是几个框加几根线，读的人要自己猜这层关系是什么。

1.2 实体和一般名词的分界线

「能被唯一标识」这个条件是实体和一般名词的分界线。像「红色」这种属性值，本身不能单独成为一个实体。

本例里有两个容易误判的名词：

第二个判断很典型：码表（code table）与实体的分界，在于「业务要不要维护它」。固定不变的几个值用枚举或码表；会增删改、还要挂额外的属性（比如分类下的保修政策），才建实体。

2 基数必须由业务方确认

2.1 一对一、一对多、多对多

基数（cardinality） 是关系两端各自可以有多少个实例。图上写的是 1 和 *，背后对应的是业务规则：

第四行是多对多。它在关系模型里没法直接落地，要拆出一个中间实体（例如 ENGINEER_DISTRICT），这一点在设计阶段处理。

这也是为什么基数必须问清：多对多的答案会让表多出一张。如果开发猜成一对多，那张关联表根本不会被建出来。

2.2 基数不要自己假设

这里有条纪律值得单独强调：基数不要自己假设，一定要去问。「一个工单能不能有多台机器」「一个客户能不能有多个联系人」——这类问题只有业务方能回答，猜错的话，改起来要动数据、动代码、动报表。

本例里三个必须问的问题，以及问出来之后的影响：

这三个问题的答案，直接决定表数量是 5 张还是 8 张。问一句的成本是五分钟，猜错的成本是几天的迁移。

2.3 还要确认可选性

另外还有一端需要确认：关系的可选性（optionality） —— 这一端是必须存在（1），还是可以没有（0..1）。这决定了字段是否允许为空。

本例里一个具体的可选性问题：一张工单必须已经派了工程师吗？ 答案是不一定 —— 刚创建的工单还没有工程师。所以 work_order.engineer_id 必须允许为 NULL，基数是 0..1 而不是 1。

如果这条没问清，写成 NOT NULL，那「创建工单」这个动作就根本插不进去数据 —— 因为那一刻还没有工程师。

3 属性分析与数据命名

3.1 属性归属

属性分析（attribute analysis）要回答的是：每个实体上到底有哪些属性、它们属于谁。

这里的第一道坎是属性归属：customer_name 该放在工单上，还是放在客户上？判断标准是依赖关系 —— 一个客户的姓名，跟他报修几次无关，所以它属于客户。放在工单上就会重复存储（这正是笔记 19 要解决的问题）。

本例里有个更微妙的归属问题：客户地址该放哪？

答案是：分两处。客户表上存「常用地址」，工单上存「本次上门地址」。理由是：

• 客户可能搬家，改客户表地址不该改历史工单的地址；
• 同一客户的不同工单可能在不同地址（比如给父母家报修）。

「看起来是同一个字段，其实有两份不同含义」——这类判断只有把业务走一遍才能发现。而如果只存一份（都放客户表），历史工单的地址就会跟着客户搬家而变，那是数据事故。

3.2 命名标准

第二道坎是命名。数据命名标准（data naming standard） 通常包含这些规则：

• 实体名用单数名词（WORK_ORDER，不是 WORK_ORDERS）；
• 属性名带上实体前缀（customer_name、order_date），避免在跨表查询时产生歧义；
• 统一用下划线分隔，不用驼峰或空格混用；
• 名称里避免出现项目缩写（半年后没人记得 tmp_flg 是什么）。

本例里遵守这套规则的字段清单：

work_order(order_no, customer_id, engineer_id, appliance_type,
           fault_desc, status, created_at, closed_at)
part_usage(work_order_id, part_no, qty)
stock_item(part_no, name, on_hand_qty, safe_qty, district_id)

注意 part_usage 里两个外键的命名：work_order_id 和 part_no。前缀说明它指向谁 —— 这正是命名标准的价值：开发看字段名就知道该 join 哪张表。

3.3 类别关键字

还有一类元素叫类别关键字（class keyword）：用来区分同一属性的不同类别，例如状态字段 order_status 的取值集合。它的作用是把「这个字段能取哪些值」集中记录在一处，而不是散落在代码里。

本例的工单状态取值必须一次定清楚：

这张表不只是「有哪些值」，还包括「能从哪转到哪」。状态机写在这个阶段，后面 22 篇那些对象的行为才有依据 —— 比如 WorkOrder.close() 只在 IN_PROGRESS 下才允许。

4 数据字典

4.1 四列结构

把上面这些汇总成一张表，就是数据字典（data dictionary）：

4.2 最值钱也最常空着的一列

第四列是整张字典里最值钱的一列，也是最常被空着的一列。它要写的是「这个字段在业务上意味着什么、有什么约束」。

本例填出来是这样（work_order 表）：

这些规则如果只留在需求文档的正文里，开发很可能漏掉；写进数据字典，它就和字段绑在一起了。

注意 engineer_id 那一行的「派单前为空」 —— 这就是 §2.3 讨论的可选性，落到字典里变成一句话。这一步是把业务规则翻译成技术约束的关键位置。

5 键（key）

5.1 五种键

标识一个实体实例用的信息，统称标识符（identifier），落到表上就是各种键：

• 主键（primary key） —— 唯一标识一行，不允许重复、不允许为空；
• 简单键（simple key） —— 由单个属性构成，例如 order_no；
• 复合键（composite key） —— 由多个属性共同构成，例如 part_usage 的 work_order_id + part_no；
• 人工键（artificial key） —— 业务本身没有合适的唯一标识时，人为加一个编号（例如流水号）；
• 外键（foreign key） —— 指向另一个表主键的字段，用来表达关系。

本例里三种键都有，值得逐个看：

第三行那个复合键有个隐含的业务规则：同一张工单里，同一个配件只出现一次，数量累加。如果业务允许「同一个配件分两次申请」（比如先申请 2 个，后来再申请 1 个），那就要再加一个序号，主键变成三列。

这个判断又要回去问业务方 —— 这就是数据建模里绕不开的一环：每一个技术决定背后都有一条业务规则。

5.2 实体与实体实例

最后补充一个术语：实体实例（entity occurrence） —— 实体是「客户」这个概念，而「张先生这个客户」是一个实体实例。主键要保证的正是每个实体实例都能被唯一标识。

5.3 一个容易忽略的键问题：业务标识会变吗

本例里有个值得停下来想的点：客户用手机号还是客户编号做主键？

如果用手机号：客户换号，所有关联的工单都要改外键 —— 而手机号是会换的； 如果用人造编号：换号只改一个字段，工单不受影响。

结论是人造编号。这条判断的依据是「标识会不会变」，而它同样是个业务问题（客户换号有多常见）。

本节自测

1. 为什么「基数」不能由开发自己决定？猜错会付出什么代价？
2. customer_name 应该放在客户表还是工单表？判断依据是什么？
3. 数据字典的第四列写的是什么？为什么它不能被省略？
4. 简单键和复合键的区别是什么？各举本例中的一个例子。
5. 什么情况下需要引入人工键？
6. 本例中客户地址为什么要存两份（客户表 + 工单表）？
7. 本例为什么不用手机号做客户表的主键？
8. 「工单状态」是实体吗？码表和实体的分界在哪？

参考答案

1. 因为基数背后是业务规则，只有业务方能回答。猜错要动数据、动代码、动报表。本例最典型：「一个工程师能属多个片区吗」—— 若猜「不能」，旺季跨片区支援时才发现要加关联表，那时已有几千条数据要迁移（§2.2）。
2. 放客户表。判断依据是依赖关系 —— 客户姓名与他报修几次无关。放工单表会重复存储（§3.1）。
3. 写的是定义与业务规则（这个字段业务上意味着什么、有什么约束）。不能省是因为这些规则若只留在需求正文里，开发很容易漏；写进字典后规则就和字段绑在一起了（§4.2）。
4. 简单键由单个属性构成（本例 stock_item.part_no）；复合键由多个属性共同构成（本例 part_usage 的 work_order_id + part_no）（§5.1）。
5. 当业务本身没有合适的天然唯一标识时。本例 work_order 用人工键 order_no，因为「客户 + 时间」也可能重复（§5.1）。
6. 因为两者含义不同：客户表存「常用地址」，工单表存「本次上门地址」。客户搬家不该改变历史工单的地址；同一客户的不同工单也可能在不同地址（给父母家报修）（§3.1）。
7. 因为手机号会变。用手机号做主键的话，客户换号时所有关联工单的外键都要改；用人造编号只需改一个字段（§5.3）。
8. 不是实体，它是工单的一个属性取值（没有自己的身份）。码表与实体的分界在于「业务要不要维护它」：固定几个值用码表；会增删改、还要挂额外属性的才建实体（§1.2）。

术语表