inventory system: transaction-based or store quant

Question

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

Would you:

1. store each purchase & usage, and uses SUM() and GROUP BY to calculate the warehouse quantity on-the-fly?
2. same as 1, but consolidate the quantity daily, and use the value of the previous day?
3. quantity as an Int field, update through application layer?
4. same as 3, but make use of DB trigger?

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.

Which one would you choose?

Answer 1

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.

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

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

Answer 2

You want transactional data if an audit trail is important. And, I've never seen a real system where it was't.

As far as performance is concerned I would:

1. capture a denormalized value on a periodic basis - hourly or daily for instance
2. move transactional records involved in this denormalization process to another table (i.e. from "current" to "warehouse").

Then totals would be a sum of this denormalized value and current transactions.

This approach also facilitates backups as the number of transactional records could exceed available disk space. So write out the warehouse to tape backup for instance.