inventory database with stock level tracking - how to implement stock check before checkout

Question

I am designing a database for an inventory management application that will handle stock level tracking of items and checkout order submission and processing. so far, i got: an Item table

• item id
• desc
• initial stock level
• other relevant info

an order table with

• order id
• order date
• customer id

an order_detail table with

• order detail id
• order id
• item id
• quantity

i am tracking the stock level using a transactions table where each time an order is processed or the stock is replinished, a record is inserted into that table with a transaction type to indicate if it's an in or an out transaction. transactions_in_out

• transaction id
• item id
• transaction type (in/out)
• quantity
• individual price

my problem is that before processing the order and inserting an out transaction, i would like to check whether stock level is sufficient, and also activate low stock alerts after transaction is inserted( although that is a different sort of problem). as it stands the only way to do this is to recalculate the current stock level!! if my tables grow as planned, this will have horrible effects on performance . what is the easiest way of doing this?

Answer 1

I would update the item/product table to include a current_stock_level column. There would be a check constraint against this column to ensure that it is always >= 0.

Having done this, your transaction to insert the order will need to decrement the stock level according to the quantity ordered as well. If someone has ordered more stock than is available, they will violate the constraint and receive an error.

If you want to verify the stock level against the transactions, then keep the initial_stock_level column. You can then run a daily/weekly/whenever process to verify that this minus the sum of orders equals the current stock level. If these are different then you can either update the current level or raise an alert to someone to investigate this.

Once this verification process (successfully) completes, update the initial_stock_level to the current level. Also include a stock_level_verified date to indicate the last time this was run.

Note if your system has constant use 24x7, you may run into concurrency issues running this checking process as people can decrement the stock while you're doing this. You'll need to think about how you're going to manage this.