从业务的数据建模：“生产入库”场景的业务逻辑与数据库设计

__BLOCK_p__💡 Apr 4, 2024 重新整理，完全重写三月份逻辑。基于 Monitor ERP 而整理。 Feb 25,2025 大幅升级，借助于 Ima.copilot 修改，特别是增加了示例数据。

归属主题：

在制造业中，一个常见的业务过程就是“生产入库”，完整的生产入库过程包含批次管理、库位管理、入库管理等多个子模块，其中的关系错综复杂，因此对数据表结构提出了较高的要求——其中最重要的是描述多对多关系的“关系表”。

虽然“关系型数据库”设计中强调，不需要一个物理文件记录多表之间的关系，仅需要使用外键即可实现。但为了确保数据读写的一致性，多表关系都会控制在“一对一”和“一对多”的场景之下，“多对多”的关系处理就会被处理为两个“一对多”的关系，于是就有了“关系表“承担这种复杂业务的桥梁。在业务过程中，这种关系表也是具体业务过程的体系。

以这个场景为例，介绍一下业务过程与数据库底表之间的映射关系。

一、极简的生产入库，及其对应的明细表记录

首先设想一个最简单的场景：生产班组生产出来的一宗产品，增加批次标记后，一次性全部入库。

为了理解，这里假设一家螺丝厂“打螺丝有限公司”，专门生产各种型号的螺丝。螺丝厂有两个生产班组：班组 A 和班组 B。生产"PG13不锈钢螺栓"包含三个环节：投料焊接（车间B-2线）、表面钝化（环保处理区）和成品检测（东区质检站） ，三个物理阶段构成一个完整的制造单元。

假设，生产班组A，在生产线 “1A”，于2024年2月27日生产 完成1000个“螺丝”，质检员检验合格后，标记“PASS-20240227AAA”的批次标签，交由仓库管理员入库。仓库管理员一次性全部存入“半成品库”。

上述的过程可以用一个简单的数据表表示：

这个业务过程可以用4W1H，形象地用介绍：哪个班组（WHO）、在何时（WHEN）、何地（WHERE）、生产了何种产品（WHAT），生产数量多少（How Much）。

传统上，如此简单场景可以用 Excel 记录，但在数字化时代，上述过程必须以规范化、结构化的数据表方式存储于“运营数据库”之中。假设把生产结果的批次结果标记为表名“生产批次及入库明细表”，英文对应ProductRecord。该业务的主要字段如下：

然而，稍微复杂一点的业务场景，这种极简设计马上就会导致一系列问题。

比如，一次生产的多个产品，可能根据不同的批次规则，赋予不同的批次号，这就意味着生产数量需要拆分为多行显示。

二、区分生产批次记录、入库登记过程两个阶段

现实情况，默认库位无法容纳同一批次全部数量，不得不分多个库位存放。此时，两个业务行为出现了明显的分化：一次入库申请，现在要对应仓管员的两次入库动作。

因此，之前的“生产批次及入库明细表”就必须拆分为两个表：生产批次表 和 批次入库表。

1、数据示例

这个过程，在技术上用数据表的关系来理解：“生产批次”（ProductRecord）中的一行数据，可以在入库明细表中有两行或者多行对应。为此，我们需要设计一个全新的数据表：“入库记录”，用来记录在每个库位分别放入了多少个产品。

由于“入库记录”是在“生产批次”表之后的，它们必然是 1：N的关系。“入库记录表”可以命名为：ProductRecordInBound，这个表中需要记录批次号（相当于入库的来源）、入库时间、入库库位、入库数量。由于“入库批次号”在前面的“生产批次”（ProductRecord）中已经存在，一个更稳定、高效地方式是构建两个表的关系。因此，“入库记录表”（ProductRecordInBound）可以包含如下的基础字段：

📊 字段名称 | 数据类型 | 字段解释

举例而言，生产班组A，在生产线 “1A”，于2024年2月27日生产 完成1000个“螺丝”，质检员检验标记“PASS-20240227AAA”的批次标签，交由仓库管理员入库。仓库管理员把其中的400个存入了 W1库位，而把600个存入了 W2库位。以下是入库记录表的示例数据：

📊 RecordID | ProductRecordID | Warehouse Location | Quantity | InboundDate

关系说明

相对应的，“生产批次表” (ProductRecord)中需要增加一个字段，用来实现两个表的关联，简化的生产批次表如下：

📊 ProductRecordID | ProductionTeam | ProductionLine | ProductModel | ProductionDate | Quantity | BatchLabel

二、从一次性入库到“分批入库”

1、从简单到进件

下图中，左侧表示业务过程，右侧表示数据库的“映射”情况，随着业务从极简状态走向进阶状态，数据表也随着拆分应对这个复杂性。

需要特别强调的是，从真实业务场景到虚拟数据库工程的映射过程中，最重要的是“编码”，不仅包含时间、地点、产品的编码，更重要的是行为本身的编码，并对编码赋予完整性、唯一性等技术约束。这里有三个关键编码：

生产批次表（ProductRecord）中，ID 代表“为指定生产数量，赋予批次标记”过程，而在“批次入库库存表”（ProductRecordInBound）中，ID表示“一次最为具体的入库行为”。它们可以标记每个表的唯一性，因此在技术上作为主键，是数据明细“唯一性约束”的关键。技术上的主键，对应业务中最完整、且最完整的一次动作。

而入库表中的ProductRecordID，则完全是用来构建关系而存在，除此之外，别无他用；我们可以通俗地把它称之为“关系字段”，正是有了一个个关系字段，多个表才能编程成为一个关系网络，业务主题才能完整。

有没有可能，不拆分表而登记在一起？可以，如下所示。

📊 ProductionTeam | ProductionLine | ProductName | ProductionDate | Quantity | BatchNumber | WarehouseLocation | InboundQua

2、从极简到进件的两种方式：关系表 VS 大宽表

看上去，这样就不需要单独的“关系字段”表示两个过程的独立性，但是随着而来的代价是：

技术上，把这种重复记录称之为“冗余”，它会导致不稳定性、低性能，分析上则要花更多精力去重。所以并非最佳实践。

因此，在“运营数据库”设计中，凡是两个相对独立的、一对多的业务过程，一律拆分为两个事务表记录，这是一条“黄金法则”。

📊 数据表关系 | 关系解释

关系型数据库的设计，提供了一种更加优雅、简洁的方案，即将关系转换为“连接字段”，使其成为多表设计的一部分。这个方法 ，就是关系型数据库中普遍使用的“外键”（foreign key）。外键既不增加额外的数据表（从而确保数据库结构尽可能简单），又能确保数据表之间的关联（从而确保生产入库业务逻辑上的完整性）。如下右侧所示：

通俗的说，只需要在“多”端那一次，标记上对应的“一”端的来源。这样既不增加新表，也不增加当前表的结构（数量不会出现重复）。每个表既可以单独使用，又可以按需合并。

关系型数据库的一大优势，就是不需要为“关系”本身单独设置文件记录，只需要能建立两个表之间的匹配关系就可以。也就是说，关系蕴含在数据之中，而非单独的指针或者文件声明。

三、记录库位的实时数量

在上面，我们已经完成了批次管理和分库位入库两个业务记录，接下来的一个难点是：如何记录每个组件在不同库位的实时数量。

1、经典三表关系

由于一个库位的当前余额，可能来自于多个入库动作，因此这里也存在明细的“一对多”关系，所以最佳的策略是单独设计一个“组件库位实时表”，并用字段和“入库行为表”建立关联。

这样，入库就形成了三表独立、前后关联的结构。如下所示：

它们之间分别描述的业务过程是：

2、实时库存结果示例

需要特别注意的是，由于一个生产批次可以存入多个库位、一个库存可以存入多个批次的产品，因此“生产批次”和“库位数据”之间是多对多的关系。这就意味着，“库存实时表”中不能出现“生产批次”相关字段。（我之前强调，是因为帮助我的 AI 也误解了这个问题）。

一个极简的PartLocation 表设计如下：

📊 字段名称 | 数据类型 | 字段解释

示例数据

假设生产班组A在生产线“1A”上生产了1000个螺丝，并分两次入库不同的库位。示例数据：

📊 LocationID | WarehouseLocation | ProductName | RealTimeQuantity | LastUpdated

需要注意的是，入库数量即不在左侧批次中，也不在右侧是实时数量中，而在中间的关系表中。从这个意义上看，这里的关系是有具体业务过程对应的，即“指定产品的指定批次分配到指定库位的入库行为”，不同于逻辑上的关系。

3、与 PartLocation 库存余额可以关联的中间表

随着库存实时数据的产生，连接“生产批次”和“库存余额”的中间表也可以进一步升级，从而更好地成为二者的桥梁。

在之前的设计中，“批次入库库存表”（ProductRecordInBound）中，只有如下的字段，它可以和“生产批次表”关联（通过 ProductRecordID），可以指向具体的库位（WarehouseLocation），但是无法指向存入指定库位那一刻的实时库存余额。

📊 RecordID | ProductRecordID | Warehouse Location | Quantity | InboundDate

为此，需要在 ProductRecordInBound 表中添加 PartLocationID 字段，以便与 PartLocation 表关联，从而详细记录每个批次的入库情况及其对各个库位实时库存的影响。以下是修改后的中间表 (ProductRecordInBound)表设计：

📊 字段名称 | 数据类型 | 字段解释

假设“PG13不锈钢螺栓”在W1库位之前存在300个了，当前批次存入 400个之后，“PG13不锈钢螺栓”、在W1的实时数量变成了700；而“PG13不锈钢螺栓”在 W2之前没有库存，此次存入600个之后，W2 的实时数量还是600。因此，PartLocation 数据表中就有三行数据，如下所示：

📊 LocationID | Warehouse Location | ProductName | RealTime Quantity | LastUpdated

于此相对应的，中间表 (ProductRecordInBound) 中新增的 PartLocation 可以指向上述表中的 LocationID 主键。

📊 InBoundID | ProductRecordID | PartLocationID | Quantity | InboundDate

4、数据规范化：最高性能、最优策略

从表之间的结构来看，上述三表关系已是最优方案了。业务数据库追求最低程度的冗余，从而提高事务处理的效率、提高数据一致性。理解三表关系的难点是“关系”的“外键”（foreign Key）。

在上面的示例中，入库的对象其实是产品的特定批次，但由于“产品”和“批次”的组合，可以在ProductRecord表中用其主键（PK=ID）来表示，因此，第二个表中就可以直接使用主键的值作为其外键，这是结构上的最优解。

同理，既然入库的结果是更新“指定产品、指定库位”的当前数量，而“产品”和“库位”的组合，可以用第三个表的主键来表示，因此，第二个表可以优化如下：

换一种方式表达，就是如下的关系图：

如此看来，第二个表就用两个外键（FK）左右牵手连接了其他两个表。在业务中，左侧相当于是“入库分类申请”（把一批产品标记批次，然后分别准备入库），右侧相当于“入库后的结果”（一个库位的多个组件进进出出，只记录结果），而中间表代表入库的动作——正因为此，入库的数量存在于中间表上，而非第三个表上。

2024-4 第一版本，基于 Monitor ERP 和客户项目实践整理 2025-02-25 基于 Ima.copilot 修改完善