首页 新闻 会员 周边

请教一下数据库设计大神,库存管理系统,期初库存表怎么设计?困惑我很久了,需要指点一下?

0
悬赏园豆:50 [待解决问题]

请教一下数据库设计大神,我现在想要设计一个和金蝶的进销存库存管理一样的需求和效果。要能根据任意时间查询库存和成本。我现在的困惑是这个期初数据怎么设计,现在输入任意查询时间,我都得知道在这之前的成本和库存数量。然后再根据每笔出入库来核算新的成本。不知道表怎么设计,请大神指教一下。困惑我好几天了,我想了很多种方案最后都自我推翻掉了。我还想过通过每次查询玩以后在后台通过代码去计算,但是这样我试了逻辑实现太复杂,如果遇到分页也很麻烦。现在就是这个期初把我困住了。


类似图片上这样,我根据任意时间搜索,都得需要知道期初。如果我是多个商品,还要根据每个商品去计算期初。我们现在仓库有100多个,商品都达到5000种。这种表应该怎么设计。

liu.peng的主页 liu.peng | 初学一级 | 园豆:154
提问于:2023-05-06 09:53
< >
分享
所有回答(6)
0

产品表(product):记录所有的产品信息
id:产品编号
name:产品名称
unit_price:产品单价

入库表(stock_in):记录产品入库信息
id:入库编号
product_id:入库产品编号
quantity:入库数量
unit_price:每个产品的单价
total_price:总价
created_at:入库时间

出库表(stock_out):记录产品出库信息
id:出库编号
product_id:出库产品编号
quantity:出库数量
unit_price:每个产品的单价
total_price:总价
created_at:出库时间

库存表(stock):记录实时的库存情况
product_id:产品编号
quantity:库存数量
unit_price:平均单价(即成本)

成本表(cost):记录历史上每个产品的成本
product_id:产品编号
cost_price:成本
start_date:生效日期
end_date:失效日期

这样的话,你可以通过以下步骤进行计算:

根据查询的时间,从 cost 表中查找到该时间点之前的成本,以及上一个成本的失效时间。
根据 stock_in 和 stock_out 表中的数据,在 stock 表中计算出当前每个产品的库存数量和平均成本。具体计算方法为:使用加权平均法,将当前时刻之前的所有入库单的总价和数量相加,再减去出库单的总价和数量,得到当前库存总额和数量,用总额除以总数即可得到平均成本。
如果当前时间点处于上一次成本失效时间和本次成本生效时间之间,则使用上一次的成本;否则使用最新成本插入一条新记录到 cost 表中,并更新 stock 表中的平均成本。

在特定日期之前每个产品的总库存和总成本
SELECT
product.id as product_id,
SUM(stock.quantity) as total_quantity,
SUM(stock.quantity * stock.unit_price) as total_cost
FROM
product
INNER JOIN stock ON product.id = stock.product_id
INNER JOIN cost ON product.id = cost.product_id
WHERE
cost.start_date <= '2023-05-06 02:04:33' -- 查询日期
GROUP BY
product.id;

风中起舞 | 园豆:226 (菜鸟二级) | 2023-05-06 10:06

我们现在的需要是主要想根据任意时间段进行查询。比如现在是5月30日,我要查看4月1到4月15日的库存和成本情况,同时还得体现动态的成本。你提出的是是截止到某个时间点,这个反而容易。

支持(0) 反对(0) liu.peng | 园豆:154 (初学一级) | 2023-05-06 10:34

现在麻烦的地方就是,每次查询都要能体现一条期初的数据行。

支持(0) 反对(0) liu.peng | 园豆:154 (初学一级) | 2023-05-06 10:35

@liu.peng: SELECT
product.id as product_id,
SUM(stock.quantity) as total_quantity,
cost.unit_price as current_cost,
SUM(stock.quantity * cost.unit_price) as total_cost
FROM
product
INNER JOIN stock ON product.id = stock.product_id
INNER JOIN (SELECT * FROM cost WHERE start_date <= '2022-04-15' ORDER BY start_date DESC LIMIT 1) as cost ON product.id = cost.product_id
WHERE
stock.date >= '2022-04-01' AND stock.date <= '2022-04-15'
GROUP BY
product.id;

支持(0) 反对(0) 风中起舞 | 园豆:226 (菜鸟二级) | 2023-05-06 10:36
0

我后面通过sql语句自己每次查期初,通过 union all连接。目前是解决问题了

liu.peng | 园豆:154 (初学一级) | 2023-05-06 17:03

这么做效率怎么样呢?

支持(0) 反对(0) 信念是基石 | 园豆:298 (菜鸟二级) | 2023-05-12 17:57
0

库存、台帐
然后各种SQL组合

Giant150 | 园豆:1165 (小虾三级) | 2023-05-09 15:46
0

成本是要核算后才有的,需要做个成本核算的功能。
每次成本核算后都要更新成本历史表,才能准确取到。

LiveCoding | 园豆:497 (菜鸟二级) | 2023-05-11 13:47
0

程序员角度:
1 确认需求接口
2 构建代码model
3 找个支持code first模式的框架,自动生成数据库

猝不及防 | 园豆:2831 (老鸟四级) | 2023-05-18 11:32
0

根据您的需求描述,我认为您可以按照以下方式设计数据库表:

  1. 商品表:记录所有商品的信息,包括商品名称、编号、单位等基本信息。在此基础上还需要添加一个“期初数量”和“期初成本”字段。

  2. 入库单表:记录每一次入库的信息,包括入库单号、入库时间、供应商信息等。在此基础上还需要添加一个“入库数量”、“入库单价”等相关信息。

  3. 出库单表:记录每一次出库的信息,包括出库单号、出库时间、客户信息等。在此基础上还需要添加一个“出库数量”、“出库单价”等相关信息。

  4. 库存记录表:记录每一次可以修改库存数目的操作,包括入库、出库、盘点等。在此基础上还需要添加一个“当前库存数量”和“当前库存成本”的字段。

通过上述表的设计,您可以实现以下功能:

  1. 查询某个时间点的库存数量和成本:可以根据商品表中“期初数量”和“期初成本”,结合入库/出库记录、库存记录等信息进行计算,得出某个时间点的库存数量和成本。

  2. 根据入库/出库记录计算新的库存成本:可以根据入库/出库记录的单价、数量等信息,以及上一次库存记录的成本和数量,计算出本次操作后的新的库存成本和数量。

  3. 生成出入库单据:可以根据出入库单表中的记录撤销或者查询操作,并生成相应的出入库单据。

在实现过程中,需要编写相应的存储过程(或者触发器等),在每次入库/出库/盘点等操作时,更新库存记录表中的数据,并计算出新的库存数量和成本。

需要注意的是,以上只是一种基本思路,实际设计过程中需要考虑更多的细节和业务需求,包括对于库存数量和成本的精确度要求、数据量、并发操作等因素。同时,还需要在设计过程中充分考虑到查询的效率和速度,避免出现性能瓶颈。

小九九呀 | 园豆:383 (菜鸟二级) | 2023-06-17 20:16
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册