Stock management - calculating available stock items (1 Viewer)

I am building a database to manage stock items and track orders. It needs to keep track of sales and the number of items currently available for sale.

I looked at the Northwind (2007) sample, and it seems that it calculates the number of items in stock using a query that sums the quantity sold, the quantity purchased, and then deducts one from the other to calculate the number available for sale.

I could make my database work like this. However, I can't believe this is the most efficient way to do it - doesn't it mean there's a relatively high processing requirement each time somebody wants to view stock availability (and that this will increase, the longer the database is used for)?

I wonder if it would be better to have a table that lists current stock levels, and when stock is purchased add the quantity to the available stock value for each item, and have a similar deduction from available stock when items are sold.

The problem is, that whilst I think this latter option would be more efficient, I don't know how I'd do it!

Please can someone help me to:

1) Decide if my thinking is correct and my way more efficient?
2) If my way is more efficient, get started with implementing it?

Thanks,

Gary

The question to ask is rather what amount of data are you expecting? Do you plan to purge old data?

There is a precident to step out of the normalization rules in this scenario - I believe that some of the banking systems use the method described - however the amount of data they are dealing with is "large"

DCB,

Thanks for your reply. There will not be a massive amount of data - probably 500-1000 transactions (incoming and outgoing stock) each year, so I guess that probably isn't overly large.

However, the data need to be available for archival purposes for 5-8 years, and I don't know if 1000/year * 8 is going to slow the system down significantly if it's having to calculate stock levels on the fly.

Does this help you to advise me? (As you might gather, I'm not very experienced with Access!)

Gary

Well 10 000 Records wouldnt really scare me at all
I would stick with the query

However you make a very good point here

However, the data need to be available for archival purposes for 5-8 years

Click to expand...

Treat this as exactly that - its never a good idea to
1) have useless data lying around
2) not have backups

So combine these two requirements into one
When you do one of your backups say per year do the purge at the same time - Logistically with any stock system you need to do stock takes - This would be the time to deal with the above

Stock take
Correct stock levels
Purge data older than (x) by pushing it to another BE location

As for the backups you need to develop a method for this anyway so all you are doing is purging at the same time

Like the OP I originally assumed the best way was to maintain a current stock quantity figure. Many systems do this but it isn't best practice.

There are circumstances where this figure can be wrong such as two sales people updating the number together. It can conflict with the derived value and database normalisation principles forbid the storing of a value that can be derived from other records.

I support a compromise of updating the stock quantity based on the sales and purchase figures at some maintenance interval but not at the time of the sale. This way the quantity query does not have to calculate all the way from the previous physical stocktake which may have been up to a year ago.

This update can be achieved by looking at all stock movements after the previous update time but before some already passed time (rather than now). This prevents new transactions processed during the update from corrupting the calculation.

Galaxiom said:

Like the OP I originally assumed the best way was to maintain a current stock quantity figure. Many systems do this but it isn't best practice.

Click to expand...

Me too. Many of the systems I've encountered stored stock as an explicit value.

The big problem with doing it this way is that it divorces the update of stock from the creation of a transaction - the process of generating an order, GRN, etc needs to do (at least) TWO distinct processes:
-Create a document detailing the transaction
-Update the stock by the appropriate amount

It is possible for one of these processes to work and the other to fail - meaning that there is then a transaction in the journal that didn't affect the stock, or that a stock change occurred without any documentation.

The key strength of calculating stock on demand is that the transaction document and the change of stock are one and the same - if for any reason the transaction document fails to hit the journal, the stock remains unchanged.

It took me a long time to emotionally accept that the live calculation of stock is the more robust method - and I also agree with Galaxiom that the most acceptable compromise is opening stock +/- transactions since the date of opening stock (essentially, all this is, is the rolling up of all transactions previous to the opening stock date, into a single value).