3. 数据模型设计
批号追溯的数据模型是整个接口开发的基础。ERP 与 MES 在批号管理上各有侧重：ERP 关注物料账务与库存批次，MES 关注生产流转与质量追溯。本章设计的四张核心表——批号主表（BatchMaster）、批次明细表（BatchDetail）、物料表（Material）、工序表（Operation）——构成了跨系统追溯的数据骨架。
3.1 四表关系总览
批号主表是追溯的入口，记录每个批次的全局信息；批次明细表记录该批次经过的每一道工序的详细执行数据；物料表提供物料编码、规格、单位等静态属性；工序表定义生产过程中的标准作业步骤。四表通过外键关联，形成从“批次 → 工序 → 物料”的完整追溯链。
设计原则：主表与明细表采用 1:N 关系，物料表与工序表作为基础数据字典，被主表和明细表引用。所有表均使用自增主键 + 业务主键（BatchNo、MaterialCode、OperationCode）的双重标识策略。
3.2 批号主表（BatchMaster）字段设计
批号主表是追溯的根节点，每个批次在此表中生成一条记录。ERP 系统在物料入库或生产订单下达时创建批号，MES 系统在生产投料或完工时补充生产数据。
| 字段名 | 数据类型 | 约束 | 说明 |
|---|---|---|---|
| BatchID | INT | PK, 自增 | 物理主键，无业务含义 |
| BatchNo | VARCHAR(50) | UK, NOT NULL | 业务批号，ERP/MES 共用，格式如 BATCH20250301-001 |
| MaterialID | INT | FK → Material | 物料主键，关联物料表 |
| Quantity | DECIMAL(18,3) | NOT NULL | 批次数量，单位来自物料表 |
| BatchStatus | TINYINT | NOT NULL, DEFAULT 0 | 0=待生产, 1=生产中, 2=已完成, 3=已冻结, 4=已报废 |
| ProductionDate | DATE | NULL | 生产日期 |
| ExpiryDate | DATE | NULL | 有效期/保质期 |
| SourceType | TINYINT | NOT NULL | 来源类型：1=ERP创建, 2=MES创建, 3=手工录入 |
| CreatedAt | DATETIME | NOT NULL | 创建时间 |
| UpdatedAt | DATETIME | NOT NULL | 最后更新时间 |
设计要点：BatchNo 作为业务唯一键，建议采用“前缀 + 日期 + 流水号”的编码规则，便于人工识别和跨系统匹配。SourceType 字段用于区分批号的创建来源，在接口同步时可根据此字段决定数据流向。
3.3 批次明细表（BatchDetail）字段设计
批次明细表记录批次在每一道工序上的执行情况。MES 系统在工序完工时写入明细记录，ERP 系统通过接口读取这些明细实现生产追溯。
| 字段名 | 数据类型 | 约束 | 说明 |
|---|---|---|---|
| DetailID | INT | PK, 自增 | 物理主键 |
| BatchID | INT | FK → BatchMaster | 关联批号主表 |
| OperationID | INT | FK → Operation | 关联工序表 |
| SequenceNo | INT | NOT NULL | 工序顺序号，用于排序 |
| StartTime | DATETIME | NULL | 工序开始时间 |
| EndTime | DATETIME | NULL | 工序结束时间 |
| InputQty | DECIMAL(18,3) | NOT NULL | 投入数量 |
| OutputQty | DECIMAL(18,3) | NOT NULL | 产出数量 |
| ScrapQty | DECIMAL(18,3) | DEFAULT 0 | 报废数量 |
| DetailStatus | TINYINT | NOT NULL | 0=待执行, 1=执行中, 2=已完成, 3=已跳过 |
| Operator | VARCHAR(50) | NULL | 操作人 |
| Remark | VARCHAR(500) | NULL | 备注/异常说明 |
常见误区：不要将工序执行数据直接放在批号主表中。一个批次可能经过 5~20 道工序，每道工序的起止时间、投入产出都不同，必须使用明细表存储。SequenceNo 字段用于保证工序顺序，避免依赖插入顺序。
3.4 物料表（Material）与工序表（Operation）
物料表和工序表是基础数据字典，在 ERP 和 MES 中通常已经存在。接口设计时，建议只引用其主键或编码，避免在批号表中冗余存储物料名称、规格等静态信息。
| 表名 | 核心字段 | 数据来源 | 同步策略 |
|---|---|---|---|
| Material | MaterialID, MaterialCode, Name, Spec, Unit, Type | ERP 物料主数据 | 每日增量同步，MES 只读 |
| Operation | OperationID, OperationCode, Name, Description, DeptID | ERP 工艺路线 / MES 工序定义 | 变更时实时推送 |
3.5 表间关联与索引设计
合理的索引设计直接影响接口查询性能。批号追溯最常见的查询场景是：根据批号查明细、根据物料查批次、根据时间段查生产记录。
-- 批号主表索引
CREATE INDEX IDX_BatchMaster_BatchNo ON BatchMaster(BatchNo);
CREATE INDEX IDX_BatchMaster_MaterialID ON BatchMaster(MaterialID);
CREATE INDEX IDX_BatchMaster_ProductionDate ON BatchMaster(ProductionDate);
-- 批次明细表索引
CREATE INDEX IDX_BatchDetail_BatchID ON BatchDetail(BatchID);
CREATE INDEX IDX_BatchDetail_OperationID ON BatchDetail(OperationID);
CREATE INDEX IDX_BatchDetail_StartTime ON BatchDetail(StartTime);
-- 物料表索引
CREATE INDEX IDX_Material_Code ON Material(MaterialCode);
-- 工序表索引
CREATE INDEX IDX_Operation_Code ON Operation(OperationCode);
性能建议：BatchNo 和 MaterialCode 作为业务查询入口，必须建立唯一索引或普通索引。BatchDetail 的 BatchID 外键索引是避免 N+1 查询的关键。如果批次明细数据量超过 100 万行，建议对 StartTime 建立复合索引（BatchID + StartTime）。
3.6 数据模型设计检查清单
在进入接口开发前，请对照以下清单确认数据模型是否满足追溯需求：
- 唯一性：BatchNo 和 MaterialCode 是否已建立唯一索引？
- 完整性：批号主表与明细表的外键关系是否强制约束？
- 可追溯性：能否通过 BatchNo 在 3 次 JOIN 内查到物料名称、工序名称、操作人、时间？
- 扩展性：明细表是否预留了 Remark 字段用于记录异常信息？
- 兼容性：SourceType 字段是否覆盖了 ERP 创建、MES 创建、手工录入三种场景？
- 性能：StartTime 和 BatchID 是否建立了复合索引以支持时间段查询？
实战建议：在开发环境中先创建这四张表，插入 3~5 条测试数据，手动执行 SELECT 语句验证关联查询是否顺畅。这一步能提前发现字段类型不匹配、索引缺失等问题，避免在接口联调时返工。