Originally Posted by
ArviLaanemets
For applications like this (where you have to follow movements in/out for different storage units, and to calculate various balances), the transactions table is a must. A possible database structure on fly:
tblProducts: ProductID, ProductCode, ProductName, ProductUnit, ...;
tblStorages: StorageID, StorageName, ...
tblProductMovements: MovementID, MovementType, MovementDate, MovementOrderNo, ProductID, StorageID, MovedQty, UnitTradePrice, ... (Where MovementType has separate values for incoming purchased products, outgoing sold products, movement between storages, writing of products because nonconformity, writing of products because overaging, correcting entries, etc. MovementOrderNo is used for purchase/selling movements and may be used as foreign key to link with Orders table(s). UnitTradePrice price is also for purchase/selling movements, as you can't have those prices hard-coded into database, or even into some table - they may change at every transaction, and often you don't have any control over them.);
As you probably need to calculate the value of products in any of storages or in total, and maybe also when moving products between storages, or between storage and production, generally for any internal movement, you also need a table where the prices used for this purpose are stored:
tblStoragePrices: StoragePriceID, ProductID, UnitStoragePrice, ValidFrom. (The cost of internal movement or the value of product in storage is calculated based on fixed date, and the valid price for this date is read from tblStoragePrices.)
The quantity of given product in given storage is calculated as sum of all incoming quantities minus sum of all outgoing quantities. No need to save it anywhere - in this way you'll have less headaches in future.
To avoid calculating storage balances over long time period, you can make e.g. yearly inventories, and save the results in separate inventory table:
tblInventories: InventoryID, InventoryDate, StorageID, ProductID, InventoryQty;
At end of inventory, the balance of all product quantities in storage is compared with inventory quantity, and for any differences correction entries are made for storage. After that you can calculate product quantities in storage at any date as the inventory quantity at last inventory before this date plus all incomings between inventory date and report date minus all outgoings between inventory date and report date.