生鲜供应链生产加工模块数据库设计与实践

1. 生产加工模块数据库设计概述

在生鲜配送供应链管理系统中，生产加工模块（Manufacturing Domain，简称MD）是整个系统的核心组成部分之一。这个模块负责管理从原材料到成品的整个加工过程，包括BOM（物料清单）管理、生产工单执行、用料控制以及基础数据维护等关键业务流程。

作为从业十多年的供应链系统架构师，我深知生鲜行业对生产加工环节的特殊要求：时效性强、损耗控制严格、批次追溯要求高。这套表结构设计正是基于这些行业特点，经过多个实际项目验证的成熟方案。整个MD模块共包含15张核心表，采用标准的域前缀命名规范（md_），明细表统一以item结尾，确保数据结构清晰可维护。

2. 核心表结构设计与业务逻辑

2.1 BOM与结构定义表

2.1.1 md_bom（商品BOM主表）

这是生产加工的基础表，定义了成品与原料的组成关系。关键设计点包括：

• bom_code：唯一BOM编码，采用"成品SKU+版本"的生成规则
• product_sku_id：关联商品主数据
• bom_version：支持同一成品多个BOM版本共存
• base_quantity：基准产出量，用于计算原料用量比例

CREATE TABLE md_bom ( id BIGINT PRIMARY KEY COMMENT '主键ID', bom_code VARCHAR(100) NOT NULL COMMENT 'BOM编码', product_sku_id BIGINT NOT NULL COMMENT '成品SKU', unit_id BIGINT NOT NULL COMMENT '成品单位', bom_version INT NOT NULL COMMENT 'BOM版本', base_quantity DECIMAL(12,4) NOT NULL DEFAULT 1 COMMENT '基准产出数量', remark VARCHAR(250) DEFAULT NULL COMMENT '备注', audit_state TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态 0待审 1已审 -1作废', enabled TINYINT NOT NULL DEFAULT 1 COMMENT '启用状态', del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记', creator BIGINT NOT NULL DEFAULT 0 COMMENT '创建人', create_date BIGINT DEFAULT NULL COMMENT '创建时间', updater BIGINT NOT NULL DEFAULT 0 COMMENT '修改人', update_date BIGINT DEFAULT NULL COMMENT '修改时间', UNIQUE KEY uk_bom (product_sku_id, unit_id, bom_version, del_flag), KEY idx_bom_code (bom_code) ) COMMENT = '商品BOM主表';

2.1.2 md_bom_item（BOM明细表）

存储BOM的原料组成明细，关键字段设计：

• material_sku_id：原料SKU ID
• net_quantity：理论净用量
• loss_rate：损耗率（%），用于计算实际需求
• plan_quantity：计划用量 = 净用量 × (1 + 损耗率)

实际项目经验：生鲜行业的损耗率设置非常关键，建议初期设置保守值，通过实际生产数据不断优化。我们曾遇到因损耗率设置不当导致原料短缺的情况。

2.2 商品拆分表设计

2.2.1 md_bom_split_template（拆分模板主表）

支持整转零、组合拆分等轻加工场景，如整猪分割、礼盒拆分等。设计特点：

• template_code：模板编码，建议采用"SPLIT"+日期+序号
• 审核机制确保模板准确性

2.2.2 md_bom_split_template_item（拆分模板明细）

记录拆分后的SKU及数量关系，支持一对多拆分。

2.2.3 md_bom_split_record（拆分执行记录）

每次执行拆分操作生成一条记录，包含：

• record_code：拆分单号，规则如"SP"+日期+序号
• template_id：关联使用的模板

2.2.4 md_bom_split_record_item（拆分执行明细）

记录实际拆分结果，用于库存调整和成本核算。

2.3 生产加工单据表

2.3.1 md_production_order（生产工单主表）

核心生产指令单据，关键设计：

• order_code：工单编号，建议规则"PO"+日期+序号
• workshop_id：生产车间，支持多车间管理
• production_date：计划生产日期
• posting_state：过账状态，控制WMS交互

CREATE TABLE md_production_order ( id BIGINT PRIMARY KEY COMMENT '主键ID', order_code VARCHAR(100) NOT NULL COMMENT '生产加工单号', workshop_id BIGINT DEFAULT NULL COMMENT '生产车间', production_date BIGINT DEFAULT NULL COMMENT '生产日期', audit_state TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态', posting_state TINYINT NOT NULL DEFAULT 0 COMMENT '过账状态', enabled TINYINT NOT NULL DEFAULT 1 COMMENT '启用状态', del_flag TINYINT NOT NULL DEFAULT COMMENT '删除标记', creator BIGINT DEFAULT NULL COMMENT '创建人', create_date BIGINT DEFAULT NULL COMMENT '创建时间', UNIQUE KEY uk_prod_order (order_code, del_flag) ) COMMENT = '生产加工单主表';

2.3.2 md_production_order_item（生产工单明细）

记录工单的成品产出计划：

• product_sku_id：目标成品SKU
• plan_quantity：计划生产数量
• complete_quantity：实际完成数量（动态更新）

2.4 生产用料与批次表

2.4.1 md_production_material（生产用料事实表）

核心用料记录表，特点：

• 最小粒度：每个工单项的每种原料一条记录
• 三量记录：净用量、计划量、实际用量
• 直接关联成本计算和库存扣减

2.4.2 md_production_material_lot（用料批次锁定）

实现先进先出和批次追溯：

• lot_no：锁定具体批次
• lock_quantity：锁定数量
• 与WMS库存批次强关联

2.4.3 md_production_picking_item（领料出库绑定）

记录实际出库情况，关键字段：

• picking_code：WMS出库单号
• out_quantity：实际出库量
• 提供完整的"生产→用料→出库"追溯链

2.5 基础资料表

2.5.1 md_production_worker（生产工人）

简单的人员主数据，支持：

• worker_code：工号
• worker_name：姓名
• 与工时统计、绩效等模块关联

2.5.2 md_production_workshop（生产车间）

车间基础信息，支持多车间管理。

2.5.3 md_process（工序主表）

定义加工工序，如：清洗→切割→包装。

2.5.4 md_process_item（工序步骤明细）

记录工序的详细步骤和顺序。

3. 关键业务逻辑实现

3.1 BOM版本控制机制

在实际项目中，我们采用"生效日期+版本号"的双重控制：

1. 新建BOM时版本号自动+1
2. 生产工单记录使用的BOM版本
3. 历史BOM保留但不启用，确保追溯

// BOM版本生成示例代码 public String generateBomVersion(Long productSkuId) { Integer maxVersion = bomMapper.selectMaxVersionBySku(productSkuId); return maxVersion != null ? maxVersion + 1 : 1; }

3.2 生产领料出库流程

完整的物料领用流程包括：

1. 工单审核通过后生成用料计划
2. 根据库存批次锁定具体物料（md_production_material_lot）
3. 生成WMS领料单并绑定（md_production_picking_item）
4. 实际出库后回写actual_quantity

避坑经验：务必在领料前检查批次库存是否充足，我们曾因并发锁定导致超领。解决方案是采用SELECT FOR UPDATE加锁。

3.3 成本核算关联设计

通过sku_unit_id关联成本模块：

• 生产用料事实表记录详细的原料消耗
• 成本模块通过sku_unit_id获取单价
• 支持按工单、成品的成本精细核算

4. 性能优化与扩展设计

4.1 索引设计策略

针对核心查询场景优化：

1. 工单查询：idx_prod_item (order_id)
2. 用料查询：idx_material_sku (sku_unit_id)
3. 批次追溯：idx_lot (sku_unit_id, lot_no)

4.2 水平分表考虑

对于大型生鲜企业，建议：

• 按日期分表：md_production_order_202301
• 定期归档历史数据
• 热点数据单独优化

4.3 扩展字段设计

预留30%的备用字段：

• 每个主表添加ext_字段（ext1, ext2...）
• 使用JSON类型的ext_info存储灵活数据

5. 常见问题与解决方案

5.1 并发领料冲突

现象：多个工单同时领用同一批次导致超领 解决方案：

1. 采用乐观锁控制版本
2. 关键操作添加事务隔离
3. 库存检查使用SELECT FOR UPDATE

5.2 BOM变更影响

现象：生产中途BOM变更导致数据不一致 处理方案：

1. 已开工单锁定BOM版本
2. 新工单使用新BOM
3. 增加变更记录表跟踪历史

5.3 批次追溯性能

优化方案：

1. 建立组合索引（sku_unit_id + lot_no）
2. 定期汇总批次数据
3. 使用Elasticsearch辅助查询

6. 实际应用建议

经过多个生鲜配送项目验证，这套表结构设计需要注意：

1. 初始化配置：

• 先建立基础数据（车间、工序、工人）
• 再配置BOM和拆分模板
• 最后开展生产业务

2. 权限控制要点：

• 车间数据隔离
• BOM修改需高级权限
• 工单审核分离

3. 数据校验策略：

• BOM循环引用检查
• 损耗率合理范围验证
• 工单关闭前完成率检查

这套表结构已经成功应用于多个日均处理量10万+的生鲜配送中心，支持从原料到成品的全流程精细化管理。特别是在损耗控制和批次追溯方面表现优异，帮助客户将原料损耗率降低了15%-20%。