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 &#039;产品编码&#039;,
name VARCHAR(100) NOT NULL COMMENT &#039;名称&#039;,
spec VARCHAR(200) COMMENT &#039;规格&#039;,
unit VARCHAR(20) NOT NULL COMMENT &#039;单位&#039;,
min_stock DECIMAL(15,2) COMMENT &#039;安全库存&#039;,
max_stock DECIMAL(15,2) COMMENT &#039;最大库存&#039;,
is_batch TINYINT(1) DEFAULT 0 COMMENT &#039;是否启用批次管理&#039;,
shelf_life INT COMMENT &#039;保质期（天）&#039;,
remark TEXT
) COMMENT &#039;产品表&#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 &#039;仓库编码&#039;,
name VARCHAR(100) NOT NULL COMMENT &#039;仓库名称&#039;,
type ENUM(&#039;NORMAL&#039;,&#039;COLD&#039;,&#039;HAZARD&#039;) DEFAULT &#039;NORMAL&#039; COMMENT &#039;仓库类型&#039;,
address VARCHAR(200) COMMENT &#039;地址&#039;,
manager_id INT COMMENT &#039;负责人&#039;,
remark TEXT
) COMMENT &#039;仓库表&#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 &#039;产品ID&#039;,
warehouse_id INT NOT NULL COMMENT &#039;仓库ID&#039;,
batch_no VARCHAR(50) COMMENT &#039;批次号&#039;,
location VARCHAR(100) COMMENT &#039;库位&#039;,
status ENUM(&#039;NORMAL&#039;,&#039;DEFECT&#039;,&#039;FROZEN&#039;,&#039;INSPECT&#039;) DEFAULT &#039;NORMAL&#039; COMMENT &#039;库存状态&#039;,
quantity DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT &#039;数量&#039;,
production_date DATE COMMENT &#039;生产日期&#039;,
expiry_date DATE COMMENT &#039;失效日期&#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 &#039;库存表&#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(&#039;IN&#039;, &#039;OUT&#039;) NOT NULL COMMENT &#039;类型&#039;,
sub_type ENUM(
&#039;PURCHASE_IN&#039;, -- 采购入库
&#039;PRODUCTION_IN&#039;, -- 生产入库
&#039;TRANSFER_IN&#039;, -- 调拨入库
&#039;RETURN_IN&#039;, -- 退货入库
&#039;SALE_OUT&#039;, -- 销售出库
&#039;TRANSFER_OUT&#039;, -- 调拨出库
&#039;SCRAP_OUT&#039; -- 报废出库
) NOT NULL COMMENT &#039;子类型&#039;,
quantity DECIMAL(15,2) NOT NULL COMMENT &#039;数量&#039;,
product_id INT NOT NULL COMMENT &#039;产品ID&#039;,
batch_no VARCHAR(50) COMMENT &#039;批次号&#039;,
from_warehouse_id INT COMMENT &#039;来源仓库（出库时必填）&#039;,
to_warehouse_id INT COMMENT &#039;目标仓库（入库时必填）&#039;,
operator_id INT NOT NULL COMMENT &#039;操作人&#039;,
ref_order_type ENUM(
&#039;PURCHASE_ORDER&#039;,
&#039;SALES_ORDER&#039;,
&#039;PRODUCTION_ORDER&#039;,
&#039;TRANSFER_ORDER&#039;,
&#039;DELIVERY_ORDER&#039;
) COMMENT &#039;关联单据类型&#039;,
ref_order_id INT COMMENT &#039;关联单据ID&#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 &#039;出入库流水表&#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 &#039;调拨单号&#039;,
from_warehouse_id INT NOT NULL COMMENT &#039;调出仓库&#039;,
to_warehouse_id INT NOT NULL COMMENT &#039;调入仓库&#039;,
status ENUM(&#039;DRAFT&#039;,&#039;IN_TRANSIT&#039;,&#039;COMPLETED&#039;,&#039;CANCELLED&#039;) DEFAULT &#039;DRAFT&#039;,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
complete_time DATETIME,
operator_id INT COMMENT &#039;操作人&#039;,
FOREIGN KEY (from_warehouse_id) REFERENCES warehouse(id),
FOREIGN KEY (to_warehouse_id) REFERENCES warehouse(id)
) COMMENT &#039;调拨单表&#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 &#039;快照时间&#039;,
FOREIGN KEY (product_id) REFERENCES product(id),
FOREIGN KEY (warehouse_id) REFERENCES warehouse(id)
) COMMENT &#039;库存历史快照表&#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=&#039;TRANSFER_ORDER&#039;, ref_order_id=1001);
-- 调拨入库（到B仓增加）
INSERT INTO inout_log (type, sub_type, ..., ref_order_type=&#039;TRANSFER_ORDER&#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=&#039;IN&#039;,
sub_type=&#039;PURCHASE_IN&#039;,
product_id=1001,
quantity=50,
to_warehouse_id=1,
ref_order_type=&#039;PURCHASE_ORDER&#039;,
ref_order_id=2001
);
-- 2. 更新库存表（按批次）
INSERT INTO inventory (product_id, warehouse_id, batch_no=&#039;B20230101&#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=&#039;NORMAL&#039;;
-- 2. 生成出库记录（若库存充足）
INSERT INTO inout_log (
type=&#039;OUT&#039;,
sub_type=&#039;SALE_OUT&#039;,
product_id=1001,
quantity=10,
from_warehouse_id=1,
ref_order_type=&#039;SALES_ORDER&#039;,
ref_order_id=3001
);
-- 3. 扣减库存
UPDATE inventory SET quantity = quantity - 10
WHERE product_id=1001 AND warehouse_id=1 AND status=&#039;NORMAL&#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 &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=&#039;NORMAL&#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=&#039;NORMAL&#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>