库存系统:基于交易还是存储数量，是否通过触发器进行更新? [英] inventory system: transaction-based or store quantity, update with trigger?

问题描述

您将如何为RDBMS中的库存管理系统设计数据模型?

How would you design the data model for an inventory management system in RDBMS?

你愿意吗?

1. 存储每次购买&用法，并使用SUM()和GROUP BY即时计算仓库数量?
2. 与1相同，但每天合并数量，并使用前一天的值吗?
3. 作为Int字段的数量，是通过应用程序层更新的吗?
4. 与3相同，但是要使用DB触发器吗?

基于事务的库存系统似乎在捕获的详细程度方面更胜一筹，但很难正确实施.性能会随着时间下降.

Transaction-based inventory system seems to be superior in terms of level of details it captures, but it is harder to implement it correctly. Performance will degrade over time.

基于数量的库存系统似乎容易得多，但是可能需要额外的锁定以确保数量值是++或-正确.

Quantity-based inventory system seems much easier, but might need extra lockings to make sure the Qty value is ++ or -- correct.

您会选择哪个?

推荐答案

我很可能会走触发路线，并在将事务推送到数据库中时更新数量.这样一来，无需一堆子查询和计算就可以很容易地查看当前数量.

I would most likely go the trigger route, and update the quantity as transactions are pushed into the database. This makes it really easy to see what the current quantity is without need of a bunch of subqueries and calculations.

如果是在触发器中完成的，那么您可以确保无论交易来自何处，库存表中的数量总是会更新(无论是否通过硬INSERT或通过应用程序添加了交易).

If it's done in a trigger, then you can ensure that regardless of where the transaction comes from, the quantities in your stock tables will always be updated (whether there are transactions added via hard INSERTs or via the application).

如果有日志记录问题，则将一些日志记录包装到触发器中，以在单独的日志记录表中跟踪数量之前/之后的情况.

If there are logging concerns, then wrap some logging into your trigger to track before/after quantities into a separate logging table.

触发器可能看起来像这样(未经测试):

A trigger might look like this (not tested):

CREATE TRIGGER [dbo].[OrderAdded] 
   ON  [dbo].[Orders] 
   AFTER INSERT
AS 
BEGIN
    DELCARE @ProductID int; DECLARE @Qty int;
    SET @ProductID = (SELECT ProductID FROM inserted);
    SET @Qty = (SELECT Qty FROM inserted);
    UPDATE StockTable 
    SET Stock = Stock - @Qty
    WHERE ID = @ProductID

END

只要您为ID和Stock字段正确索引StockTable，我就不会担心性能问题(我当然会考虑所有这些，您没有提供任何数据库信息.

I don't see that there would be a performance issue to worry about so long as you've got your StockTable properly indexed for the ID and Stock field (I'm of course making all of this up given that you didn't provide any DB information).

这篇关于库存系统:基于交易还是存储数量，是否通过触发器进行更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！