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

At one of the clients I worked at, in a southern city that rhymes with Orlando, the warehouse manager took a while to get used to this concept. He was a terrific southern gentleman but he was used to being able to do some creative accounting at inventory time to make his numbers. He said, Mahk, I need another 200,000 to make the boss happy. I said there's no way to do it. All your inventory is tied to a location and if it's not there, it's not there. His boss (a real moron for someone with such an important position) didn't like this at all but finally came to acceptance.

We had one client who pretty creatively fought the system. Reputedly mobbed up. They set up trailers in the parking lot as "slot locations" and parked bogus inventory there. But they got caught.