1 问题表述
(本文讨论的技术背景是:数据库-mysql,后台-java,前台-bs端.但讨论的内容应用并不被这些技术所限的)
软件系统的常用设计中,常见的一个模型就是库存模型.一个最基本简单的库存模型,应至少包含三个逻辑模块的设计:
- 库存商品数量的存储方式设计;
- 商品入库/出库的逻辑设计;
- 库存数量的查询设计;
如果只是实现这三种设计,当然是十分简单的.只需设计一张实时库存表(作为冗余表),商品出入库时使该表的数字有所增减,查询时从该表中直接查询出来即可.
现实项目中基本不会有这么简单的设计,许多问题或要求的提出使得这样的逻辑设计基本无法胜任得以解决问题.
在此本人追加两个在业务逻辑中碰到的合理但不简单的需求:
需求 | 现状不符合的原因 |
商品出/入库的日期可以指定 | 难点体现在库存查询上,无此需求相当于是审批表单的时刻为出/入库的默认时间,增加此需求后实时库存表查询实时和历史库存的任务均无法胜任 |
同样的商品对应不同的价格 | 对于入库问题一般不大,难点在于出库和库存金额的查询,出库难在如何自动计算出合理的成本价格,查询难在需要实时和历史金额均可查询 |
在阅读下文前,请先仔细阅读以上表格.
如果不能深入理解以上表格所述的各种原因,则接下来的逻辑设计可能也不会理解.则不太建议继续往下读.
2 方案提供
从最开始的简单库存模型设计,到现在这个较为成熟的模型,一路上也是摸着石头过河,中间试过很多种方案,但篇幅所限,中间那些不太完美的方案就不再细说,直接将最终方案拿出来供大家参考.
先说前提的业务条件.出于方便大家理解起见,真正工作中所用的模型拿出来会有所精简,去除掉与本文中解决问题无关的内容.
库存商品数量方面,需要考虑仓库别,商品别,要素至少包含价格和数量.
2.1 库存商品数量的存储方式设计;
- 仓库(t_store)设计如下:
字段 | 名称内容 |
store_code | 仓库编码 |
store_name | 仓库名称 |
- 商品(t_product)设计如下:
字段 | 名称内容 |
p_code | 商品名称 |
p_name | 商品名称 |
- 商品出入库表单(t_stock_outin)设计如下(实际业务中一般会是主从表的设计方式,同样是方便理解此处从简):
字段 | 名称内容 |
form_code | 表单编码 |
form_date | 表单日期,也是决定库存变更的日期 |
store_code | 仓库编码 |
p_code | 商品编码 |
is_positive | 出库or入库,出库时为0,入库时为1 |
count | 影响商品数量 |
in_price | 成本价格 |
approval_state | 审批状态(简单起见,仅区分0,1,0表示已保存,1表示已审批) |
(为方便理解,本文仅列出成本价格,去除困扰读者理解的销售价格,且仅在入库时是显式的,出库时是没有该参数的)
- 库存变更日志表(t_stock_log)设计如下(也是本模型的核心所在):
字段 | 名称内容 |
log_id | 日志id,自增 |
form_code | 表单编码,关联商品出入库单 |
form_date | 表单日期,同时也是出入库日期 |
log_index | 日志索引,每个批次中对应日志的唯一标识,格式:20180724000001 |
store_code | 仓库编码 |
p_code | 商品编码 |
add_count | 增加库存数量(减少为负值) |
history_count | 历史库存数量 |
in_price | 成本价格 |
可以看到,库存变更日志表与商品出库如表单的字段设计的非常相似,但还是有区别的.现在可以解释的原因包括:
a. 实际业务逻辑中,影响出入库的表单一般不止一种,包括销售开单,采购开单,生产入库,调拨入库,库存盘点等单据,这些表格一般不会放在一个表格中去设计(每个模块都有自己独特的东西,放在一块儿反而很乱),库存日志表在这种情况下起到了统一核心内容格式的作用;
b. 商品出入库表单表示的是单据的内容,仅在审批完成时表示库存变更;而库存变更日志表,其存在本身即表示库存实际的变更.两者的意义是不一样的;
2.2 商品出入库的逻辑设计
库存变更日志表,作为商品出入库表的一种冗余设计,在其审批完成时增加数据,在其审批撤销时删除对应数据.
出入库的核心逻辑有两个,其一是检查库存数量是否足够,严谨的说,即操作后仍使每一条库存日志的历史库存为非负数.再详细些讲,即操作后:
a. 要保证当前插入数据的历史库存为非负数(所以仅需要考虑审批-增加时的情况);
b. 要保证操作(审批-增加,撤销)当前日志以后(在这里我们以log_index为判断先后的标记)的库存日志历史库存仍为非负数.
这里讨论的出入库,都是p_code-in_price-add_count已知的情况.如果是商品出库,往往不会给出具体价格这个参数,从仅有商品到商品编码与价格俱备,涉及到一个计算环节,这个计算环节我们放在最后再说.
具体而言,需要考虑到以下几个步骤:
- 检查当前历史库存数量是否足够(纯粹的增加库存一般不需检查,但为了让代码通用型更强,姑且在所有审批环节均予以检查).
查询sql如下(审批与审批撤销的检查需要区分开):
-- 审批-增加日志sql
select tsl.store_code,tsl.p_code,tsl.in_price,sum(tsl.add_count) as history_count
from t_stock_log tsl
where tsl.store_code=?
and tsl.form_date<=?
and tsl.p_code in (?,?)
group by tsl.p_code,tsl.in_price
-- 还可直接采用直接查询history_count的方式(两者使用一种即可)
select * from (
select store_code,p_code,in_price,history_count
from t_stock_log
where store_code=?
and form_date<=?
and p_code in (?,?,..)
order by log_index desc
)wt
group by p_code,in_price获取到对应p_code-in_price批次的数量后,和当前待处理的数量相加/相减(根据是审批/审批撤销有所不同,在后台java中计算即可),如果小于0,则不允许审批撤销.
- 检查使用批次(即操作日志涉及批次)在此后数量变化时对应的最小库存数量是否足够.这话可能说的有点抽象,举个简单的例子来说明:
单据日期 | 单号 | 变更数量(+/-分别表示入库/出库) | 历史库存 |
2018-07-21 | 001 | +50 | 50 |
2018-07-22 | 002 | +35 | 85 |
2018-07-23 | 003 | -40 | 45 |
2018-07-24 | 004 | -20 | 25 |
譬如要审批撤销单据002,需检查此后各日志对应每一个批次的历史库存数量是否可能会因为此次撤销而变成负值(否则可能会出现查询某天历史库存时数量为负的情况).查询sql如下:
select * from (
select form_code,form_date,store_code,p_code,in_price,history_count
from t__stock_log
where store_code=?
and form_date>?
and p_code in (?,?,..)
order by history_count asc
)tmp
group by p_code,in_price此后的操作与检查1如出一辙,即令每一个批次的数量与当前待处理的数量相加/相减,如果小于0则不允许审批撤销;
对于环节1和环节2,其逻辑本身再予以简化,即查看每个批次在单据之后.
(如果是插入单据,则仅需考虑该日期本身及该日期之后, 如果为删除单据,则需考虑当前当局log_index之后):
先说插入情况:
-- 插入情况
select * from (
select ? as form_date,store_code,p_code,in_price,sum(add_count) as history_count
from t_stock_log
where form_date<=?
and store_code=?
and p_code in (?,?..)
group by p_code,in_price
union
select tsl.form_date,tsl.store_code,tsl.p_code,tsl.in_price,tsl.history_count
from t_stock_log tsl
inner join (
select p_code,in_price
from t_stock_log
where form_date<=?
and store_code=?
and p_code in (?,?,..)
group by p_code,in_price
)wt
on wt.p_code=tsl.p_code
and wt.in_price=tsl.in_price
where tsl.form_date>?
order by history_count asc
)wt
group by p_code,in_price再说撤销(删除)情况的检查:
select tsl.store_code,tsl.p_code,tsl.in_price,min(tsl.history_count)
from t_stock_log tsl
inner join (
select * from (
select * from t_stock_log
where form_code=?
)wt1
)wt on wt.store_code=tsl.store_code
and wt.p_code=tsl.p_code
and wt.in_price=tsl.in_price
and wt.log_index<tsl.log_index
group by tsl.store_code,tsl.p_code,tsl.in_price- 插入(审批)/删除(撤销)库存日志 两种情况还是需要分别讨论:
- 插入库存日志,分为四个步骤:
a. 自动获取计算log_index; 查询获取各批次对应的表单日期的最大索引,根据最大索引+1得出应赋索引.
-- 查询表单所有对应商品的日期对应最大索引+1
select store_code,p_code,in_price,ifnull(max(log_index%1000000),0)+1 as max_index
from t_stock_log
where store_code=?
and form_date=?
and p_code in (?,?,..)
group by p_code,in_price各自参数获取到应赋索引后,分别处理(表单日期转化为yyyyMMdd格式,后接最大索引,如20180721000002,long格式),再分别挂到作为参数的日志中(每条明细的log_index一般是不一样的),供下一步骤使用.
b. 插入本条数据;
共计有两种插入方式(两种思想参考:mysql联表批处理操作):
-- 参数显式体现在java中(batchUpdate)
insert into t_stock_log
(form_code,type_code,form_date,log_index,store_code,p_code,price,add_count,cst_create)
values
(?,?,?,?,?,?,?,?,now())
-- 参数隐式体现在mysql中(需要出入库表额外增加字段记录log_index)
insert into t_stock_log
(form_code,form_date,log_index,store_code,p_code,add_count,in_price)
(select form_code,form_date,log_index,store_code,p_code,if(is_positive,count,-count),in_price from t_stock_outin where form_code=?);两种插入方式各有优缺点,前者在java中能做到较低的耦合性,后者则体现更简洁的代码,具体选择哪种根据实际情况取舍.
c. 更新本条数据的历史库存;
sql语句如下:
update t_stock_log tsl3,(
select tsl.store_code,tsl.p_code,tsl.in_price,ifnull(sum(tsl.add_count),0)as history_count
from t_stock_log tsl
right join (select * from t_stock_log where form_code=?) tsl2
on tsl2.store_code=tsl.store_code
and tsl2.p_code=tsl.p_code
and tsl2.in_price=tsl.in_price
where tsl.log_index<=tsl2.log_index
group by tsl.store_code,tsl.p_code,tsl.in_price
)tsl4
set tsl3.history_count=tsl4.history_count
where tsl3.form_code=?
and tsl3.store_code=tsl4.store_code
and tsl3.p_code=tsl4.p_code
and tsl3.in_price=tsl4.in_price注:虽然该语句很长,逻辑并不十分复杂.即查询出表单对应库存日志集合当前和此前的add_count总和,再赋予到当前表单对应的库存日志集合中.
还可通过查询前一条history_count的方式实现,此处就不再展开了.
d. 更新此后的批次历史库存;
update t_stock_log tsl2,(
select store_code,form_date,log_index,p_code,in_price,add_count
from t_stock_log
where form_code=?
)wtsl
set tsl2.history_count=tsl2.history_count+wtsl.add_count
where tsl2.log_index>wtsl.log_index
and tsl2.store_code=wtsl.store_code
and tsl2.p_code=wtsl.p_code
and tsl2.in_price=wtsl.in_price- 删除库存日志,分为两个步骤:
a. 分别改变此后库存日志的历史库存 与上一条sql十分相似,就是把关键的+号改为了-号.
update t_stock_log tsl2,(
select store_code,form_date,log_index,p_code,in_price,add_count
from t_stock_log
where form_code=?
)wtsl
set tsl2.history_count=tsl2.history_count-wtsl.add_count
where tsl2.log_index>wtsl.log_index
and tsl2.store_code=wtsl.store_code
and tsl2.p_code=wtsl.p_code
and tsl2.in_price=wtsl.in_priceb. 删除表单号对应库存日志
delete from t_stock_log
where form_code=?2.3 库存数量的查询设计
该设计,既适用于历史查询,也适用于实时查询(只需将日期放到今天即可).
相比库存日志的操作,查询语句相对而言就简单很多了.此处也仅提供比较通用的查询某仓库某些商品各价位的库存数量的合集:
-- 可根据history_count或add_count两种方式进行查询,此处提供根据add_count查询的sql
select tsl.store_code,tsl.p_code,tsl.in_price,sum(tsl.add_count) as history_count
from t_stock_log tsl
where tsl.store_code=?
and tsl.form_date<=?
and tsl.p_code in (?,?,..)
group by tsl.p_code,tsl.in_price2.4 库存出库根据商品所需数量自动计算出各价位的数量
在库存出库单审批完成需要减库存时,参数只会提供store_code,p_code和count,对于另一个较重要的参数in_price,往往不会提供.
这使得减库存就会稍微麻烦些,也是包含此前很多方案考虑的核心逻辑要素之一.
一般采用比较合理的方案是:先通过计算,得出减少的库存数量应当分布在哪些价位上的,再对各自批次减少库存.
具体的操作实施,大致可分为3步:
- 查询当前及今后各价格批次对应的最小数量之和,是否满足总和要求.
-- 该方法难以使用一条sql完成,此处采用sql+java后台共同处理的方式
-- 该sql的意义是查询表单日期当天中已经出现过的商品价格批次在该表单日期之后(不含当天)的每天库存数量,各商品对应的最小值
select * from (
select form_date,store_code,p_code,in_price,sum(add_count) as history_count
from t_stock_log
where form_date<=?
and store_code=?
and p_code in (?,?,..)
group by p_code,in_price
union
select form_date,store_code,p_code,in_price,history_count
from t_stock_log
where form_date>?
and store_code=?
and p_code in (?,?,..)
order by history_count asc
)wt
group by p_code,in_price
order by form_date asc此处可能有人会不理解该sql的实际意义,此处同样举个简单的例子(方便理解起见,此处仅列出相同商品的情况)说明:
条目 | 2018-07-26 | 2018-07-27 | 2018-07-28 |
10元变化 | +50 | 0 | -20 |
10元汇总 | 50 | 50 | 30 |
12元变化 | +40 | 0 | -30 |
12元汇总 | 40 | 40 | 10 |
15元变化 | 0 | 0 | +40 |
15元汇总 | 0 | 0 | 40 |
所有汇总 | 90 | 90 | 80 |
此时如果要开一张2018-07-27出库领料的单据,领料数量为70,那么此时是否允许领料呢?
(在看以下解释前,建议自己先根据表格内容思考下.)
当前已有的,共计3种价格:10元,12元和15元.各自在当前(07-27)及此后(07-28)的最小数量分别是30,10和0.总计最小数量之和为40.
所以该日期可出库的最大数量为40个,即使当前及今后总数量的最小值为80,这张出库70的领料单据仍是不允许被审批成功的.
- 后台(java)根据先进先出原则计算各商品各价格批次需要多少数量 仍是使用此前sql查询到的最小数量集合.由于sql获取时,已经按照日期进行排序,正常取值即可.以下为java参考代码:
for(StockLog mSlMin:lstSl){
if(mSlMin.getHistory_count()==null){
mAsslMin.setHistory_count(0);
}
int intCountAfter=mSlMin.getHistory_count()+mSlMin.getAdd_count()*intPositive;
if(intCountAfter<0){
//此处的StrUtil是hutool.jar的工具类
String strMessageTemp="库存变更后将影响后期单据库存数量.{}用品{}价格{}库存为{}";
String strMessage=StrUtil.format(strMessageTemp,
mSlMin.getForm_code(),
mSlMin.getP_code(),
mSlMin.getIn_price(),
mSlMin.getHistory_count());
mapResult.put("message",strMessage);
return mapResult;
}
}- 合并结果并执行增加库存的通用方法(参考2.2)
3.末尾问题
- 由于审批撤销导致的减少库存时可能僵化,这是由逻辑决定而非设计决定;
- 许多查询使用到了子查询,数据量大了之后可能会导致查询速度较慢,所以仅适用于中小规模的数据量(具体是多少有待测试);
