💡 Apr 4, 2024 重新整理，完全重写三月份逻辑。基于 Monitor ERP 而整理。
Feb 25,2025 大幅升级，借助于 Ima.copilot 修改，特别是增加了示例数据。
归属主题：
在制造业中，一个常见的业务过程就是“生产入库”，完整的生产入库过程包含批次管理、库位管理、入库管理等多个子模块，其中的关系错综复杂，因此对数据表结构提出了较高的要求——其中最重要的是描述多对多关系的“关系表”。
虽然“关系型数据库”设计中强调，不需要一个物理文件记录多表之间的关系，仅需要使用外键即可实现。但为了确保数据读写的一致性，多表关系都会控制在“一对一”和“一对多”的场景之下，“多对多”的关系处理就会被处理为两个“一对多”的关系，于是就有了“关系表“承担这种复杂业务的桥梁。在业务过程中，这种关系表也是具体业务过程的体系。
以这个场景为例，介绍一下业务过程与数据库底表之间的映射关系。
一、极简的生产入库，及其对应的明细表记录
首先设想一个最简单的场景：生产班组生产出来的一宗产品，增加批次标记后，一次性全部入库。
为了理解，这里假设一家螺丝厂“打螺丝有限公司”，专门生产各种型号的螺丝。螺丝厂有两个生产班组：班组 A 和班组 B。生产”PG13不锈钢螺栓”包含三个环节：投料焊接（车间B-2线）、表面钝化（环保处理区）和成品检测（东区质检站） ，三个物理阶段构成一个完整的制造单元。
假设，生产班组A，在生产线 “1A”，于2024年2月27日生产 完成1000个“螺丝”，质检员检验合格后，标记“PASS-20240227AAA”的批次标签，交由仓库管理员入库。仓库管理员一次性全部存入“半成品库”。
上述的过程可以用一个简单的数据表表示：
| 生产班组 | 生产线 | 产品型号 | 生产日期 | 数量 | 批次标签 | 入库仓库 |
| A | 1A | PG13不锈钢螺栓 | 2024-02-27 | 1000 | PASS-20240227AAA | W1 |
这个业务过程可以用4W1H，形象地用介绍：哪个班组（WHO）、在何时（WHEN）、何地（WHERE）、生产了何种产品（WHAT），生产数量多少（How Much）。
传统上，如此简单场景可以用 Excel 记录，但在数字化时代，上述过程必须以规范化、结构化的数据表方式存储于“运营数据库”之中。假设把生产结果的批次结果标记为表名“生产批次及入库明细表”，英文对应ProductRecord。该业务的主要字段如下：
- 生产线（ProductionLine）：表示哪个工作中心完成的，这里姑且可以表示1A 产线。
- 产品名称（
ProductName）：说明这个批次生产的是什么产品。 - 生产日期（
ProductionDate）：记录这个批次的产品何时生产的。 - 数量（
Quantity）：记录这个批次的产品数量。 - 批次号（
BatchNumber）：唯一标识每个生产批次的编号。 - 入库仓库（
Warehouse）：记录这个批次的产品存储的仓库。
然而，稍微复杂一点的业务场景，这种极简设计马上就会导致一系列问题。
比如，一次生产的多个产品，可能根据不同的批次规则，赋予不同的批次号，这就意味着生产数量需要拆分为多行显示。
二、区分生产批次记录、入库登记过程两个阶段
现实情况，默认库位无法容纳同一批次全部数量，不得不分多个库位存放。此时，两个业务行为出现了明显的分化：一次入库申请，现在要对应仓管员的两次入库动作。
因此，之前的“生产批次及入库明细表”就必须拆分为两个表：生产批次表 和 批次入库表。
1、数据示例
这个过程，在技术上用数据表的关系来理解：“生产批次”（ProductRecord）中的一行数据，可以在入库明细表中有两行或者多行对应。为此，我们需要设计一个全新的数据表：“入库记录”，用来记录在每个库位分别放入了多少个产品。
由于“入库记录”是在“生产批次”表之后的，它们必然是 1：N的关系。“入库记录表”可以命名为：ProductRecordInBound，这个表中需要记录批次号（相当于入库的来源）、入库时间、入库库位、入库数量。由于“入库批次号”在前面的“生产批次”（ProductRecord）中已经存在，一个更稳定、高效地方式是构建两个表的关系。因此，“入库记录表”（ProductRecordInBound）可以包含如下的基础字段：
| 字段名称 | 数据类型 | 字段解释 |
|---|---|---|
| RecordID | INT | 主键，自增，唯一标识每一条入库记录 |
| ProductRecordID | INT | 外键，关联到生产批次表的主键 |
| WarehouseLocation | VARCHAR(50) | 库位标识，如W1、W2等 |
| Quantity | INT | 存入该库位的产品数量 |
| InboundDate | DATE | 入库日期 |
举例而言，生产班组A，在生产线 “1A”，于2024年2月27日生产 完成1000个“螺丝”，质检员检验标记“PASS-20240227AAA”的批次标签，交由仓库管理员入库。仓库管理员把其中的400个存入了 W1库位，而把600个存入了 W2库位。以下是入库记录表的示例数据：
| RecordID | ProductRecordID | Warehouse Location | Quantity | InboundDate |
|---|---|---|---|---|
| 1 | 1 | W1 | 400 | 2024-02-27 |
| 2 | 1 | W2 | 600 | 2024-02-27 |
关系说明
- ProductRecordID 是外键，关联到生产批次表（ProductRecord）的主键（ID）。
- WarehouseLocation 记录了具体的库位标识。
- Quantity 记录了每个库位存放的产品数量。
- InboundDate 记录了入库日期。
相对应的，“生产批次表” (ProductRecord)中需要增加一个字段，用来实现两个表的关联，简化的生产批次表如下：
| ProductRecordID | ProductionTeam | ProductionLine | ProductModel | ProductionDate | Quantity | BatchLabel |
|---|---|---|---|---|---|---|
| 1 | A | 1A | PG13不锈钢螺栓 | 2024-02-27 | 1000 | PASS-20240227AAA |
二、从一次性入库到“分批入库”
1、从简单到进件
下图中，左侧表示业务过程，右侧表示数据库的“映射”情况，随着业务从极简状态走向进阶状态，数据表也随着拆分应对这个复杂性。
需要特别强调的是，从真实业务场景到虚拟数据库工程的映射过程中，最重要的是“编码”，不仅包含时间、地点、产品的编码，更重要的是行为本身的编码，并对编码赋予完整性、唯一性等技术约束。这里有三个关键编码：
- 生产批次表（
ProductRecord），ID为唯一字段，代表一个批次，同时也是后续入库的来源。 - 批次入库库存表（
ProductRecordInBound），入库流水号 （ID）标记入库行为编号，每一行唯一。 - 入库表中，增加了 ProductRecordID 字段，用来构建与“生产批次表”表的关联。
生产批次表（ProductRecord）中，ID 代表“为指定生产数量，赋予批次标记”过程，而在“批次入库库存表”（ProductRecordInBound）中，ID表示“一次最为具体的入库行为”。它们可以标记每个表的唯一性，因此在技术上作为主键，是数据明细“唯一性约束”的关键。技术上的主键，对应业务中最完整、且最完整的一次动作。
而入库表中的ProductRecordID，则完全是用来构建关系而存在，除此之外，别无他用；我们可以通俗地把它称之为“关系字段”，正是有了一个个关系字段，多个表才能编程成为一个关系网络，业务主题才能完整。
有没有可能，不拆分表而登记在一起？可以，如下所示。
| ProductionTeam | ProductionLine | ProductName | ProductionDate | Quantity | BatchNumber | WarehouseLocation | InboundQuantity | InboundDate |
|---|---|---|---|---|---|---|---|---|
| A | 1A | PG13不锈钢螺栓 | 2024-02-27 | 1000 | PASS-20240227AAA | W1 | 400 | 2024-02-27 |
| A | 1A | PG13不锈钢螺栓 | 2024-02-27 | 1000 | PASS-20240227AAA | W2 | 600 | 2024-02-27 |
2、从极简到进件的两种方式：关系表 VS 大宽表
看上去，这样就不需要单独的“关系字段”表示两个过程的独立性，但是随着而来的代价是：
- 大量字段重复存储，包括“生产批次”中的所有字段，特别是1000个重复出现两次，容易出现误解
- 更新困难，如果生产入库要分修改，比如后来的600个最后发现放不下了，不得不拆出来10个放在第三个库位，那么新行中的“生产批次”也要同步更新，这样往往带来不稳定性
技术上，把这种重复记录称之为“冗余”，它会导致不稳定性、低性能，分析上则要花更多精力去重。所以并非最佳实践。
因此，在“运营数据库”设计中，凡是两个相对独立的、一对多的业务过程，一律拆分为两个事务表记录，这是一条“黄金法则”。
| 数据表关系 | 关系解释 |
|---|---|
| 1：1 | 一个批次的数量，只会存入一个库位。比如，同一个批次的1000个产品，一次性存入库位 SF1-1. |
| 1：N | 一个批次的数量，可以存入多个库位。 比如同一批次的1000个 产品，其中400个存入库位 W2，剩余600个存入库位 W2. |
关系型数据库的设计，提供了一种更加优雅、简洁的方案，即将关系转换为“连接字段”，使其成为多表设计的一部分。这个方法 ，就是关系型数据库中普遍使用的“外键”（foreign key）。外键既不增加额外的数据表（从而确保数据库结构尽可能简单），又能确保数据表之间的关联（从而确保生产入库业务逻辑上的完整性）。如下右侧所示：
通俗的说，只需要在“多”端那一次，标记上对应的“一”端的来源。这样既不增加新表，也不增加当前表的结构（数量不会出现重复）。每个表既可以单独使用，又可以按需合并。
关系型数据库的一大优势，就是不需要为“关系”本身单独设置文件记录，只需要能建立两个表之间的匹配关系就可以。也就是说，关系蕴含在数据之中，而非单独的指针或者文件声明。
三、记录库位的实时数量
在上面，我们已经完成了批次管理和分库位入库两个业务记录，接下来的一个难点是：如何记录每个组件在不同库位的实时数量。
1、经典三表关系
由于一个库位的当前余额，可能来自于多个入库动作，因此这里也存在明细的“一对多”关系，所以最佳的策略是单独设计一个“组件库位实时表”，并用字段和“入库行为表”建立关联。
这样，入库就形成了三表独立、前后关联的结构。如下所示：
它们之间分别描述的业务过程是：
- ProductRecord表：描述一个批次的产品的入库申请，可能是产品生产完成质检合格，或者采购到货质检通过。其中重点是批次——批次对应具有相同特征的产品集合，而非最小的产品粒度。
- PartLocation表：每个产品在每个库位的实时数量。这里的库位是仓库管理的基本单位，仓库和库区太大，库位是最小的仓库网格，比如南区一楼一号货架，标记“SF1-1”。
- 中间表是
ProductRecordInBound，表示产品的批次到库位入库表，用来表示上述二者的关系（由于是两个表的中间表，所以有些 ERP 会用两个表名称的组合命名这个表，比如ProductRecordPartLocation ）。
2、实时库存结果示例
需要特别注意的是，由于一个生产批次可以存入多个库位、一个库存可以存入多个批次的产品，因此“生产批次”和“库位数据”之间是多对多的关系。这就意味着，“库存实时表”中不能出现“生产批次”相关字段。（我之前强调，是因为帮助我的 AI 也误解了这个问题）。
一个极简的PartLocation 表设计如下：
| 字段名称 | 数据类型 | 字段解释 |
|---|---|---|
| LocationID | INT | 主键，自增，唯一标识每个库位记录 |
| WarehouseLocation | VARCHAR(50) | 库位标识，如W1、W2等 |
| ProductName | VARCHAR(100) | 产品名称 |
| RealTimeQuantity | INT | 当前库位的实时数量 |
| LastUpdated | DATETIME | 最后更新时间 |
示例数据
假设生产班组A在生产线“1A”上生产了1000个螺丝，并分两次入库不同的库位。示例数据：
| LocationID | WarehouseLocation | ProductName | RealTimeQuantity | LastUpdated |
|---|---|---|---|---|
| 100 | W1 | PG13不锈钢螺栓 | 400 | 2024-02-27 10:00:00 |
| 200 | W2 | PG13不锈钢螺栓 | 600 | 2024-02-27 10:00:00 |
需要注意的是，入库数量即不在左侧批次中，也不在右侧是实时数量中，而在中间的关系表中。从这个意义上看，这里的关系是有具体业务过程对应的，即“指定产品的指定批次分配到指定库位的入库行为”，不同于逻辑上的关系。
3、与 PartLocation 库存余额可以关联的中间表
随着库存实时数据的产生，连接“生产批次”和“库存余额”的中间表也可以进一步升级，从而更好地成为二者的桥梁。
在之前的设计中，“批次入库库存表”（ProductRecordInBound）中，只有如下的字段，它可以和“生产批次表”关联（通过 ProductRecordID），可以指向具体的库位（WarehouseLocation），但是无法指向存入指定库位那一刻的实时库存余额。
| RecordID | ProductRecordID | Warehouse Location | Quantity | InboundDate |
|---|---|---|---|---|
| 1 | 1 | W1 | 400 | 2024-02-27 |
| 2 | 1 | W2 | 600 | 2024-02-27 |
为此，需要在 ProductRecordInBound 表中添加 PartLocationID 字段，以便与 PartLocation 表关联，从而详细记录每个批次的入库情况及其对各个库位实时库存的影响。以下是修改后的中间表 (ProductRecordInBound)表设计：
| 字段名称 | 数据类型 | 字段解释 |
|---|---|---|
| InBoundID | INT | 主键，自增，唯一标识每一条入库记录 |
| ProductRecordID | INT | 外键，关联到生产批次表的主键 |
| PartLocationID | INT | 外键，关联到组件库位实时表的主键 |
| Quantity | INT | 入库数量 |
| InboundDate | DATETIME | 入库日期和时间 |
假设“PG13不锈钢螺栓”在W1库位之前存在300个了，当前批次存入 400个之后，“PG13不锈钢螺栓”、在W1的实时数量变成了700；而“PG13不锈钢螺栓”在 W2之前没有库存，此次存入600个之后，W2 的实时数量还是600。因此，PartLocation 数据表中就有三行数据，如下所示：
| LocationID | Warehouse Location | ProductName | RealTime Quantity | LastUpdated |
|---|---|---|---|---|
| 0801 | W1 | PG13不锈钢螺栓 | 300 | 2024-02-01 08:00:00 |
| 0912 | W1 | PG13不锈钢螺栓 | 700 | 2024-02-27 10:00:00 |
| 0523 | W2 | PG13不锈钢螺栓 | 600 | 2024-02-27 10:00:00 |
于此相对应的，中间表 (ProductRecordInBound) 中新增的 PartLocation 可以指向上述表中的 LocationID 主键。
| InBoundID | ProductRecordID | PartLocationID | Quantity | InboundDate |
|---|---|---|---|---|
| 1 | 1 | 0912 | 400 | 2024-02-27 09:00:00 |
| 2 | 1 | 0523 | 600 | 2024-02-27 09:00:00 |
- PartLocation 表
LocationID改为 4 位随机数编码，不再暗示时间先后次序。- 0801 这一记录表示 W1 库位在 2 月 1 日之前的初始库存为 300 个。
- 0912 这一记录表示新的入库操作后 W1 库位的实时库存变为 700 个。
- 0523 这一记录表示 W2 库位在新的入库操作后的实时库存为 600 个。
- ProductRecordInBound 表
PartLocationID与PartLocation表中的LocationID相关联，确保数据关系的正确性。
4、数据规范化：最高性能、最优策略
从表之间的结构来看，上述三表关系已是最优方案了。业务数据库追求最低程度的冗余，从而提高事务处理的效率、提高数据一致性。理解三表关系的难点是“关系”的“外键”（foreign Key）。
在上面的示例中，入库的对象其实是产品的特定批次，但由于“产品”和“批次”的组合，可以在ProductRecord表中用其主键（PK=ID）来表示，因此，第二个表中就可以直接使用主键的值作为其外键，这是结构上的最优解。
同理，既然入库的结果是更新“指定产品、指定库位”的当前数量，而“产品”和“库位”的组合，可以用第三个表的主键来表示，因此，第二个表可以优化如下：
换一种方式表达，就是如下的关系图：
如此看来，第二个表就用两个外键（FK）左右牵手连接了其他两个表。在业务中，左侧相当于是“入库分类申请”（把一批产品标记批次，然后分别准备入库），右侧相当于“入库后的结果”（一个库位的多个组件进进出出，只记录结果），而中间表代表入库的动作——正因为此，入库的数量存在于中间表上，而非第三个表上。
2024-4 第一版本，基于 Monitor ERP 和客户项目实践整理
2025-02-25 基于 Ima.copilot 修改完善