绿洲ERP电商系统


ERP库存管理模块设计方案

<hr /> <h3><strong>优化后的ERP库存管理模块设计方案</strong></h3> <hr /> <h4><strong>一、问题复盘与改进方向</strong></h4> <p><strong>原方案缺陷分析:</strong></p> <ol> <li><strong>库存表缺陷</strong>:缺少批次管理、库存状态(良品/次品)、有效期等关键字段。</li> <li><strong>出入库记录表缺陷</strong>:未记录操作人、调拨来源/目标仓库,单据关联方式过于简单。</li> <li><strong>缺少调拨单管理</strong>:调拨操作需独立单据跟踪。</li> <li><strong>历史库存快照缺失</strong>:无法回溯历史库存状态。</li> </ol> <hr /> <h4><strong>二、优化后的表设计与建表语句</strong></h4> <h5><strong>1. 核心表结构</strong></h5> <h6><strong>(1) 产品表 (product)</strong></h6> <pre><code class="language-sql">CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, product_no VARCHAR(50) UNIQUE NOT NULL COMMENT &amp;#039;产品编码&amp;#039;, name VARCHAR(100) NOT NULL COMMENT &amp;#039;名称&amp;#039;, spec VARCHAR(200) COMMENT &amp;#039;规格&amp;#039;, unit VARCHAR(20) NOT NULL COMMENT &amp;#039;单位&amp;#039;, min_stock DECIMAL(15,2) COMMENT &amp;#039;安全库存&amp;#039;, max_stock DECIMAL(15,2) COMMENT &amp;#039;最大库存&amp;#039;, is_batch TINYINT(1) DEFAULT 0 COMMENT &amp;#039;是否启用批次管理&amp;#039;, shelf_life INT COMMENT &amp;#039;保质期(天)&amp;#039;, remark TEXT ) COMMENT &amp;#039;产品表&amp;#039;;</code></pre> <h6><strong>(2) 仓库表 (warehouse)</strong></h6> <pre><code class="language-sql">CREATE TABLE warehouse ( id INT PRIMARY KEY AUTO_INCREMENT, code VARCHAR(20) UNIQUE NOT NULL COMMENT &amp;#039;仓库编码&amp;#039;, name VARCHAR(100) NOT NULL COMMENT &amp;#039;仓库名称&amp;#039;, type ENUM(&amp;#039;NORMAL&amp;#039;,&amp;#039;COLD&amp;#039;,&amp;#039;HAZARD&amp;#039;) DEFAULT &amp;#039;NORMAL&amp;#039; COMMENT &amp;#039;仓库类型&amp;#039;, address VARCHAR(200) COMMENT &amp;#039;地址&amp;#039;, manager_id INT COMMENT &amp;#039;负责人&amp;#039;, remark TEXT ) COMMENT &amp;#039;仓库表&amp;#039;;</code></pre> <h6><strong>(3) 库存表 (inventory)</strong></h6> <pre><code class="language-sql">CREATE TABLE inventory ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL COMMENT &amp;#039;产品ID&amp;#039;, warehouse_id INT NOT NULL COMMENT &amp;#039;仓库ID&amp;#039;, batch_no VARCHAR(50) COMMENT &amp;#039;批次号&amp;#039;, location VARCHAR(100) COMMENT &amp;#039;库位&amp;#039;, status ENUM(&amp;#039;NORMAL&amp;#039;,&amp;#039;DEFECT&amp;#039;,&amp;#039;FROZEN&amp;#039;,&amp;#039;INSPECT&amp;#039;) DEFAULT &amp;#039;NORMAL&amp;#039; COMMENT &amp;#039;库存状态&amp;#039;, quantity DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT &amp;#039;数量&amp;#039;, production_date DATE COMMENT &amp;#039;生产日期&amp;#039;, expiry_date DATE COMMENT &amp;#039;失效日期&amp;#039;, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_inventory (product_id, warehouse_id, batch_no, location, status), FOREIGN KEY (product_id) REFERENCES product(id), FOREIGN KEY (warehouse_id) REFERENCES warehouse(id) ) COMMENT &amp;#039;库存表&amp;#039;;</code></pre> <h6><strong>(4) 出入库记录表 (inout_log)</strong></h6> <pre><code class="language-sql">CREATE TABLE inout_log ( id INT PRIMARY KEY AUTO_INCREMENT, type ENUM(&amp;#039;IN&amp;#039;, &amp;#039;OUT&amp;#039;) NOT NULL COMMENT &amp;#039;类型&amp;#039;, sub_type ENUM( &amp;#039;PURCHASE_IN&amp;#039;, -- 采购入库 &amp;#039;PRODUCTION_IN&amp;#039;, -- 生产入库 &amp;#039;TRANSFER_IN&amp;#039;, -- 调拨入库 &amp;#039;RETURN_IN&amp;#039;, -- 退货入库 &amp;#039;SALE_OUT&amp;#039;, -- 销售出库 &amp;#039;TRANSFER_OUT&amp;#039;, -- 调拨出库 &amp;#039;SCRAP_OUT&amp;#039; -- 报废出库 ) NOT NULL COMMENT &amp;#039;子类型&amp;#039;, quantity DECIMAL(15,2) NOT NULL COMMENT &amp;#039;数量&amp;#039;, product_id INT NOT NULL COMMENT &amp;#039;产品ID&amp;#039;, batch_no VARCHAR(50) COMMENT &amp;#039;批次号&amp;#039;, from_warehouse_id INT COMMENT &amp;#039;来源仓库(出库时必填)&amp;#039;, to_warehouse_id INT COMMENT &amp;#039;目标仓库(入库时必填)&amp;#039;, operator_id INT NOT NULL COMMENT &amp;#039;操作人&amp;#039;, ref_order_type ENUM( &amp;#039;PURCHASE_ORDER&amp;#039;, &amp;#039;SALES_ORDER&amp;#039;, &amp;#039;PRODUCTION_ORDER&amp;#039;, &amp;#039;TRANSFER_ORDER&amp;#039;, &amp;#039;DELIVERY_ORDER&amp;#039; ) COMMENT &amp;#039;关联单据类型&amp;#039;, ref_order_id INT COMMENT &amp;#039;关联单据ID&amp;#039;, log_time DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES product(id), FOREIGN KEY (from_warehouse_id) REFERENCES warehouse(id), FOREIGN KEY (to_warehouse_id) REFERENCES warehouse(id) ) COMMENT &amp;#039;出入库流水表&amp;#039;;</code></pre> <h6><strong>(5) 调拨单表 (transfer_order)</strong></h6> <pre><code class="language-sql">CREATE TABLE transfer_order ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) UNIQUE NOT NULL COMMENT &amp;#039;调拨单号&amp;#039;, from_warehouse_id INT NOT NULL COMMENT &amp;#039;调出仓库&amp;#039;, to_warehouse_id INT NOT NULL COMMENT &amp;#039;调入仓库&amp;#039;, status ENUM(&amp;#039;DRAFT&amp;#039;,&amp;#039;IN_TRANSIT&amp;#039;,&amp;#039;COMPLETED&amp;#039;,&amp;#039;CANCELLED&amp;#039;) DEFAULT &amp;#039;DRAFT&amp;#039;, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, complete_time DATETIME, operator_id INT COMMENT &amp;#039;操作人&amp;#039;, FOREIGN KEY (from_warehouse_id) REFERENCES warehouse(id), FOREIGN KEY (to_warehouse_id) REFERENCES warehouse(id) ) COMMENT &amp;#039;调拨单表&amp;#039;;</code></pre> <h6><strong>(6) 库存快照表 (inventory_snapshot)</strong></h6> <pre><code class="language-sql">CREATE TABLE inventory_snapshot ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, warehouse_id INT NOT NULL, batch_no VARCHAR(50), quantity DECIMAL(15,2) NOT NULL, snapshot_time DATETIME NOT NULL COMMENT &amp;#039;快照时间&amp;#039;, FOREIGN KEY (product_id) REFERENCES product(id), FOREIGN KEY (warehouse_id) REFERENCES warehouse(id) ) COMMENT &amp;#039;库存历史快照表&amp;#039;;</code></pre> <hr /> <h4><strong>三、关键改进说明</strong></h4> <ol> <li> <p><strong>批次管理</strong> </p> <ul> <li><code>inventory</code>表增加<code>batch_no</code>、<code>production_date</code>、<code>expiry_date</code>,支持按批次跟踪库存。</li> <li><code>product.is_batch</code>字段控制是否启用批次管理。</li> </ul> </li> <li> <p><strong>库存状态精细化</strong> </p> <ul> <li>区分正常品(NORMAL)、次品(DEFECT)、冻结库存(FROZEN)、在检库存(INSPECT)。</li> </ul> </li> <li> <p><strong>调拨流程规范化</strong> </p> <ul> <li> <p>调拨操作需先创建<code>transfer_order</code>,再生成对应的<code>IN</code>和<code>OUT</code>记录:</p> <pre><code class="language-sql">-- 调拨出库(从A仓减少) INSERT INTO inout_log (type, sub_type, ..., ref_order_type=&amp;#039;TRANSFER_ORDER&amp;#039;, ref_order_id=1001); -- 调拨入库(到B仓增加) INSERT INTO inout_log (type, sub_type, ..., ref_order_type=&amp;#039;TRANSFER_ORDER&amp;#039;, ref_order_id=1001);</code></pre> </li> </ul> </li> <li> <p><strong>单据关联灵活性</strong> </p> <ul> <li>通过<code>ref_order_type</code>和<code>ref_order_id</code>关联任意类型单据(采购单、销售单、调拨单等)。</li> </ul> </li> <li><strong>库存快照机制</strong> <ul> <li>每天凌晨生成<code>inventory_snapshot</code>,支持历史库存查询: <pre><code class="language-sql">INSERT INTO inventory_snapshot SELECT product_id, warehouse_id, batch_no, quantity, NOW() FROM inventory;</code></pre></li> </ul></li> </ol> <hr /> <h4><strong>四、核心业务逻辑示例</strong></h4> <ol> <li> <p><strong>采购入库流程</strong> </p> <pre><code class="language-sql">-- 1. 采购单确认后生成入库记录 INSERT INTO inout_log ( type=&amp;#039;IN&amp;#039;, sub_type=&amp;#039;PURCHASE_IN&amp;#039;, product_id=1001, quantity=50, to_warehouse_id=1, ref_order_type=&amp;#039;PURCHASE_ORDER&amp;#039;, ref_order_id=2001 ); -- 2. 更新库存表(按批次) INSERT INTO inventory (product_id, warehouse_id, batch_no=&amp;#039;B20230101&amp;#039;, quantity=50) ON DUPLICATE KEY UPDATE quantity = quantity + 50;</code></pre> </li> <li> <p><strong>销售出库流程</strong> </p> <pre><code class="language-sql">-- 1. 检查库存是否充足 SELECT quantity FROM inventory WHERE product_id=1001 AND warehouse_id=1 AND status=&amp;#039;NORMAL&amp;#039;; -- 2. 生成出库记录(若库存充足) INSERT INTO inout_log ( type=&amp;#039;OUT&amp;#039;, sub_type=&amp;#039;SALE_OUT&amp;#039;, product_id=1001, quantity=10, from_warehouse_id=1, ref_order_type=&amp;#039;SALES_ORDER&amp;#039;, ref_order_id=3001 ); -- 3. 扣减库存 UPDATE inventory SET quantity = quantity - 10 WHERE product_id=1001 AND warehouse_id=1 AND status=&amp;#039;NORMAL&amp;#039;;</code></pre> </li> </ol> <hr /> <h4><strong>五、扩展建议</strong></h4> <ol> <li> <p><strong>库存预警</strong> </p> <pre><code class="language-sql">-- 查询低于安全库存的产品 SELECT p.product_no, p.name, i.warehouse_id, i.quantity FROM inventory i JOIN product p ON i.product_id = p.id WHERE i.quantity &amp;lt; p.min_stock;</code></pre> </li> <li> <p><strong>先进先出(FIFO)策略</strong> </p> <pre><code class="language-sql">-- 出库时按生产日期排序 SELECT * FROM inventory WHERE product_id=1001 AND status=&amp;#039;NORMAL&amp;#039; ORDER BY production_date ASC LIMIT 1;</code></pre> </li> <li><strong>动态库存视图</strong> <pre><code class="language-sql">CREATE VIEW v_inventory_summary AS SELECT p.product_no, p.name, w.name AS warehouse, SUM(i.quantity) AS total, SUM(CASE WHEN i.status=&amp;#039;NORMAL&amp;#039; THEN i.quantity ELSE 0 END) AS available FROM inventory i JOIN product p ON i.product_id = p.id JOIN warehouse w ON i.warehouse_id = w.id GROUP BY p.id, w.id;</code></pre></li> </ol> <hr /> <h4><strong>六、总结</strong></h4> <p>优化后的方案实现了: </p> <ol> <li><strong>精细化管理</strong>:支持批次、状态、有效期跟踪。 </li> <li><strong>完整单据链路</strong>:通过<code>ref_order_type</code>和<code>ref_order_id</code>关联所有业务单据。 </li> <li><strong>可追溯性</strong>:库存快照+调拨单记录确保全流程可回溯。 </li> <li><strong>扩展性</strong>:预留字段支持未来扩展(如质检流程、序列号管理)。</li> </ol>

页面列表

ITEM_HTML