3

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?

kfc
  • 291
  • 7
  • 24

1 Answers1

4

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.

Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • I was hoping to avoid implementing this, I don't want to rely on a saved current stock field because if ever some discrepancy pops up, it would be too late to fix it even with the correctional process. I want to rely on a single process for stock checks/updates. is there no simpler way? – kfc May 24 '13 at 07:32
  • It depends upon how strict you want to be about not exceeding your available stock. A DB check constraint will ensure this can't go below 0. You could make a daily summary of the transactions and calculate the deltas from that, rather than relying on current stock levels. You'll need to implement the checks to verify people aren't ordering more than is available yourself however, increasing the chance it won't handle concurrent orders for the same product correctly. – Chris Saxon May 24 '13 at 07:55
  • Bear in mind whichever approach you take you're going to need some form of correctional process. Stock can be stolen, damaged or just mis-counted so the values in the database may not match what you actually have with 100% accuracy. You'll need some form of adjustment process to handle this. – Chris Saxon May 24 '13 at 07:55
  • just off the top of my head, i am probably going to go with the solution you proposed and to handle concurrency issues I am thinking of synchronizing access to the code updating the current stock field. – kfc May 27 '13 at 14:19