请教一下数据库设计大神，库存管理系统，期初库存表怎么设计？困惑我很久了，需要指点一下?
0
悬赏园豆：50
[待解决问题]
请教一下数据库设计大神，我现在想要设计一个和金蝶的进销存库存管理一样的需求和效果。要能根据任意时间查询库存和成本。我现在的困惑是这个期初数据怎么设计，现在输入任意查询时间，我都得知道在这之前的成本和库存数量。然后再根据每笔出入库来核算新的成本。不知道表怎么设计，请大神指教一下。困惑我好几天了，我想了很多种方案最后都自我推翻掉了。我还想过通过每次查询玩以后在后台通过代码去计算，但是这样我试了逻辑实现太复杂，如果遇到分页也很麻烦。现在就是这个期初把我困住了。
类似图片上这样，我根据任意时间搜索，都得需要知道期初。如果我是多个商品，还要根据每个商品去计算期初。我们现在仓库有100多个，商品都达到5000种。这种表应该怎么设计。
所有回答(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;
我们现在的需要是主要想根据任意时间段进行查询。比如现在是5月30日，我要查看4月1到4月15日的库存和成本情况，同时还得体现动态的成本。你提出的是是截止到某个时间点，这个反而容易。
现在麻烦的地方就是，每次查询都要能体现一条期初的数据行。
@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
我后面通过sql语句自己每次查期初，通过 union all连接。目前是解决问题了
这么做效率怎么样呢?
0
库存、台帐
然后各种SQL组合
0
成本是要核算后才有的，需要做个成本核算的功能。
每次成本核算后都要更新成本历史表，才能准确取到。
0
程序员角度：
1 确认需求接口
2 构建代码model
3 找个支持code first模式的框架，自动生成数据库
0
根据您的需求描述，我认为您可以按照以下方式设计数据库表：
-
商品表：记录所有商品的信息，包括商品名称、编号、单位等基本信息。在此基础上还需要添加一个“期初数量”和“期初成本”字段。
-
入库单表：记录每一次入库的信息，包括入库单号、入库时间、供应商信息等。在此基础上还需要添加一个“入库数量”、“入库单价”等相关信息。
-
出库单表：记录每一次出库的信息，包括出库单号、出库时间、客户信息等。在此基础上还需要添加一个“出库数量”、“出库单价”等相关信息。
-
库存记录表：记录每一次可以修改库存数目的操作，包括入库、出库、盘点等。在此基础上还需要添加一个“当前库存数量”和“当前库存成本”的字段。
通过上述表的设计，您可以实现以下功能：
-
查询某个时间点的库存数量和成本：可以根据商品表中“期初数量”和“期初成本”，结合入库/出库记录、库存记录等信息进行计算，得出某个时间点的库存数量和成本。
-
根据入库/出库记录计算新的库存成本：可以根据入库/出库记录的单价、数量等信息，以及上一次库存记录的成本和数量，计算出本次操作后的新的库存成本和数量。
-
生成出入库单据：可以根据出入库单表中的记录撤销或者查询操作，并生成相应的出入库单据。
在实现过程中，需要编写相应的存储过程（或者触发器等），在每次入库/出库/盘点等操作时，更新库存记录表中的数据，并计算出新的库存数量和成本。
需要注意的是，以上只是一种基本思路，实际设计过程中需要考虑更多的细节和业务需求，包括对于库存数量和成本的精确度要求、数据量、并发操作等因素。同时，还需要在设计过程中充分考虑到查询的效率和速度，避免出现性能瓶颈。
