库存管理
<p>以下是基于库存管理需求的数据库表设计，结合业务场景（采购入库、销售出库、多仓库管理）并延续之前的表结构规范：</p>
<pre><code class="language-sql">-- ----------------------------
-- 仓库信息表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_warehouse;
CREATE TABLE lv_inv_warehouse (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
warehouse_code VARCHAR(50) NOT NULL COMMENT &#039;仓库编码&#039;,
warehouse_name VARCHAR(100) NOT NULL COMMENT &#039;仓库名称&#039;,
warehouse_type INT(10) COMMENT &#039;仓库类型：10自有仓/20供应商仓/30海外仓&#039;,
address VARCHAR(200) COMMENT &#039;仓库地址&#039;,
manager_id BIGINT(20) COMMENT &#039;负责人&#039;,
is_active TINYINT(1) DEFAULT 1 COMMENT &#039;是否启用（0否/1是）&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_warehouse_code (warehouse_code),
KEY idx_iw_warehouse_type (warehouse_type)
) COMMENT=&#039;仓库信息表&#039;;
-- ----------------------------
-- 库存表（核心表）
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_stock;
CREATE TABLE lv_inv_stock (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
sku_code VARCHAR(50) NOT NULL COMMENT &#039;SKU编码&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;仓库ID&#039;,
total_quantity INT NOT NULL DEFAULT 0 COMMENT &#039;总库存量&#039;,
available_quantity INT NOT NULL DEFAULT 0 COMMENT &#039;可用库存量&#039;,
locked_quantity INT DEFAULT 0 COMMENT &#039;锁定库存量（预占）&#039;,
unit VARCHAR(20) COMMENT &#039;计量单位&#039;,
last_inbound_time DATETIME COMMENT &#039;最后入库时间&#039;,
last_outbound_time DATETIME COMMENT &#039;最后出库时间&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_sku_warehouse (sku_code, warehouse_id), -- 同一SKU在同一仓库唯一
KEY idx_is_sku_code (sku_code),
KEY idx_is_warehouse_id (warehouse_id)
) COMMENT=&#039;库存表&#039;;
-- ----------------------------
-- 入库单主表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_inbound;
CREATE TABLE lv_inv_inbound (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
inbound_no VARCHAR(50) NOT NULL COMMENT &#039;入库单号&#039;,
pur_order_id BIGINT(20) COMMENT &#039;关联采购订单ID&#039;,
inbound_type INT(10) COMMENT &#039;入库类型：10采购入库/20退货入库/30调拨入库&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;目标仓库ID&#039;,
operator_id BIGINT(20) COMMENT &#039;操作人&#039;,
inbound_time DATETIME COMMENT &#039;入库时间&#039;,
total_quantity INT COMMENT &#039;总数量&#039;,
status INT(10) DEFAULT 10 COMMENT &#039;状态：10待入库/20部分入库/30已完成/40已取消&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_inbound_no (inbound_no),
KEY idx_ii_pur_order_id (pur_order_id),
KEY idx_ii_warehouse_id (warehouse_id)
) COMMENT=&#039;入库单主表&#039;;
-- ----------------------------
-- 入库明细表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_inbound_detail;
CREATE TABLE lv_inv_inbound_detail (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
inbound_id BIGINT(20) NOT NULL COMMENT &#039;入库单ID&#039;,
sku_code VARCHAR(50) NOT NULL COMMENT &#039;SKU编码&#039;,
quantity INT NOT NULL COMMENT &#039;入库数量&#039;,
batch_no VARCHAR(50) COMMENT &#039;批次号&#039;,
production_date DATE COMMENT &#039;生产日期&#039;,
expire_date DATE COMMENT &#039;过期日期&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_iid_inbound_id (inbound_id),
KEY idx_iid_sku_code (sku_code)
) COMMENT=&#039;入库明细表&#039;;
-- ----------------------------
-- 出库单主表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_outbound;
CREATE TABLE lv_inv_outbound (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
outbound_no VARCHAR(50) NOT NULL COMMENT &#039;出库单号&#039;,
sales_order_id BIGINT(20) COMMENT &#039;关联销售订单ID&#039;,
outbound_type INT(10) COMMENT &#039;出库类型：10销售出库/20换货出库/30调拨出库&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;源仓库ID&#039;,
operator_id BIGINT(20) COMMENT &#039;操作人&#039;,
outbound_time DATETIME COMMENT &#039;出库时间&#039;,
total_quantity INT COMMENT &#039;总数量&#039;,
status INT(10) DEFAULT 10 COMMENT &#039;状态：10待出库/20部分出库/30已完成/40已取消&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_outbound_no (outbound_no),
KEY idx_io_sales_order_id (sales_order_id),
KEY idx_io_warehouse_id (warehouse_id)
) COMMENT=&#039;出库单主表&#039;;
-- ----------------------------
-- 出库明细表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_outbound_detail;
CREATE TABLE lv_inv_outbound_detail (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
outbound_id BIGINT(20) NOT NULL COMMENT &#039;出库单ID&#039;,
sku_code VARCHAR(50) NOT NULL COMMENT &#039;SKU编码&#039;,
quantity INT NOT NULL COMMENT &#039;出库数量&#039;,
batch_no VARCHAR(50) COMMENT &#039;批次号&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_iod_outbound_id (outbound_id),
KEY idx_iod_sku_code (sku_code)
) COMMENT=&#039;出库明细表&#039;;
-- ----------------------------
-- 库存操作记录表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_operation_log;
CREATE TABLE lv_inv_operation_log (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
sku_code VARCHAR(50) NOT NULL COMMENT &#039;SKU编码&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;仓库ID&#039;,
biz_type INT(10) COMMENT &#039;业务类型：10采购入库/20销售出库/30库存调整&#039;,
biz_ref_no VARCHAR(50) COMMENT &#039;关联业务单号&#039;,
quantity_change INT COMMENT &#039;库存变化量（正数增加/负数减少）&#039;,
operator_id BIGINT(20) COMMENT &#039;操作人&#039;,
operation_time DATETIME COMMENT &#039;操作时间&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_iol_sku_warehouse (sku_code, warehouse_id),
KEY idx_iol_biz_ref_no (biz_ref_no)
) COMMENT=&#039;库存操作记录表&#039;;
-- ----------------------------
-- 库存调整表（用于盘盈/盘亏）
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_adjustment;
CREATE TABLE lv_inv_adjustment (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
adjust_no VARCHAR(50) NOT NULL COMMENT &#039;调整单号&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;仓库ID&#039;,
adjust_type INT(10) COMMENT &#039;调整类型：10盘盈/20盘亏&#039;,
adjust_reason TEXT COMMENT &#039;调整原因&#039;,
operator_id BIGINT(20) COMMENT &#039;操作人&#039;,
adjust_time DATETIME COMMENT &#039;调整时间&#039;,
status INT(10) DEFAULT 10 COMMENT &#039;状态：10待处理/20已确认&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_adjust_no (adjust_no),
KEY idx_ia_warehouse_id (warehouse_id)
) COMMENT=&#039;库存调整表&#039;;
-- ----------------------------
-- 库存统计表
-- ----------------------------
DROP TABLE IF EXISTS lv_inv_statistics;
CREATE TABLE lv_inv_statistics (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
sku_code VARCHAR(50) NOT NULL COMMENT &#039;SKU编码&#039;,
warehouse_id BIGINT(20) NOT NULL COMMENT &#039;仓库ID&#039;,
stat_date DATE COMMENT &#039;统计日期&#039;,
inbound_quantity INT DEFAULT 0 COMMENT &#039;累计入库量&#039;,
outbound_quantity INT DEFAULT 0 COMMENT &#039;累计出库量&#039;,
adjustment_quantity INT DEFAULT 0 COMMENT &#039;调整量&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_stat_sku_warehouse (sku_code, warehouse_id, stat_date)
) COMMENT=&#039;库存统计表&#039;;</code></pre>
<h3>设计说明：</h3>
<ol>
<li>
<p><strong>核心表结构</strong>：</p>
<ul>
<li><strong>lv_inv_warehouse</strong>：存储多仓库信息（自有仓/供应商仓/海外仓）</li>
<li><strong>lv_inv_stock</strong>：实时库存快照，包含总库存、可用库存、锁定库存</li>
<li><strong>入库/出库单</strong>：采用主表+明细表结构（lv_inv_inbound/lv_inv_outbound + detail表）</li>
<li><strong>库存操作日志</strong>：记录所有库存变动流水（lv_inv_operation_log）</li>
</ul>
</li>
<li>
<p><strong>关键业务逻辑</strong>：</p>
<ul>
<li><strong>库存锁定</strong>：通过 <code>locked_quantity</code> 字段实现销售预占库存</li>
<li><strong>批次管理</strong>：入库明细表包含 <code>batch_no</code> 和有效期字段，支持先进先出（FIFO）</li>
<li><strong>多仓库联动</strong>：通过 <code>warehouse_type</code> 区分仓库归属，实现跨仓业务处理</li>
</ul>
</li>
<li>
<p><strong>统计优化</strong>：</p>
<ul>
<li><strong>实时统计</strong>：通过 <code>lv_inv_stock</code> 表直接查询当前库存</li>
<li><strong>历史统计</strong>：<code>lv_inv_statistics</code> 表按日汇总数据，提升报表性能</li>
</ul>
</li>
<li><strong>扩展能力</strong>：
<ul>
<li>支持 <strong>库存调整</strong>（盘盈/盘亏）场景</li>
<li>通过 <code>biz_ref_no</code> 字段可关联采购单、销售单等外部业务单据</li>
<li>预留 <code>batch_no</code> 字段可扩展批次管理功能</li>
</ul></li>
</ol>
<h3>使用建议：</h3>
<ol>
<li>
<p><strong>库存更新策略</strong>：</p>
<ul>
<li>在入库/出库操作时，<strong>同时更新</strong> <code>lv_inv_stock</code> 表和 <code>lv_inv_operation_log</code> 表</li>
<li>使用数据库事务保证数据一致性</li>
</ul>
</li>
<li>
<p><strong>库存锁定流程</strong>：</p>
<pre><code class="language-sql">-- 示例：销售下单时锁定库存
UPDATE lv_inv_stock
SET available_quantity = available_quantity - 10,
locked_quantity = locked_quantity + 10
WHERE sku_code = &#039;SKU001&#039; AND warehouse_id = 1;</code></pre>
</li>
<li><strong>库存统计查询</strong>：
<pre><code class="language-sql">-- 示例：查询某仓库实时库存
SELECT sku_code, total_quantity, available_quantity
FROM lv_inv_stock
WHERE warehouse_id = 1;</code></pre></li>
</ol>