库存系统:基于交易或存储数量，用触发器更新?

本文介绍了库存系统:基于交易或存储数量，用触发器更新?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

你会:

存储每次购买和使用情况，并使用 SUM() 和 GROUP BY 即时计算仓库数量?

同1，但每天合并数量，使用前一天的值?

数量作为 Int 字段，通过应用层更新?

同 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

只要您的 StockTable 为 ID 和 Stock 字段正确编入索引，我认为不会有性能问题需要担心(我当然会做所有鉴于您没有提供任何数据库信息).

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).