>>>Dmitry,
>>>
>>>The strategy that I would use is that the stock would be recorded in a single column on the items table (your inv_code column?). Then updating the stock is as simple as updating a single column.
>>
>>In practice, there may be several reasons to keep stock separately, as there's perhaps stock by warehouse, or stock by supplier, or stock by batch (medications have that) or by some other kind of unit or any combination thereof, so your key is loc+item or item+batch.
>>
>>>Aside from that, just one transaction table with all the in AND out going transactions. So not two seperate tables, but just one. In this table you've got both the purchases and sales, just indicated by a single column. You might, or might not record the stock in the transaction table for checking consisitency with the items table.
>>
>>Actually, we've found that keeping the stock is best done by totalling the transactions, completely ignoring the current stock. Any new transaction (or update to existing) will trigger this recalculation for the item involved, but the number in the stock table is pretty much read only. Turned out to be much cleaner than relying on any previously stored stock amount/price/quantity. But, OTOH, yes, ONE transaction table.
>
>If there's one thing I know about it's inventory. The software company I worked for constantly ran into companies who had tried to cut corners and ran into problems. There is no shortcut to keeping track of things down to the SKU and transaction level. Then you can query as needed. Besides, in database theory you don't store sums in any table. Dr. Codd changed all that.

Which theory would that be? Normalisation? Eh... I don't know anyone that normalizes tables to the 5th NF or even 3rd Boyce -Codd. Storing redundant data is allowed as long as it is controlled.