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

Cleaner yes, but certainly I/O intensive to get the current stock. It depends on what fits best.

Walter,