Stores (store issues, returns, production receive, lost, found)
Now I have an separate database for stock movements, each time there is a transaction in either of the above departments, data will be replicated/copied/updated into stock movement tables
Now I have complete stock movement register in a single table ( stock movements )
I wrote some procedures to calculate stock valuation for each item including running totals, moving average rate fro each transaction etc.
Now the problem is every time a departmental database is updated/inserted/deleted I have to run valuation process again so that valuation need to be ready for critical reporting i.e. consumption reports, production costing etc
My stock_movement table have multi-milltion rows/transactions for each item, so online valuation is taking time and form-submission is slow