In our Mysql Database, we are updating qty on hand values for inventory system, however these values seem to be erroneous. We cant track down the problem.
I came across a few articles on the subject and a few said that You do not store qty on hand values in your db.
I would like to improve my database design by getting advice from the community.
Sales data is correct, Inventory movement data is correct. Just storing the actual qty on hand value seems to be the problem.
The reason we are storing it so that we dont have to keep summing up 1000s of records every time.
How would you handle this problem from a design perspective.
In general, you should use system triggers to achieve this type of redundancy with any hope of controlling and reconciling it. Also insert the transactions into a human readable "log" file.
On day end, compare values in this "on hand" table with it's previous version PLUS new stock MINUS sales. If OK, then delete data in the "log" file and replace the previous version of the log file with the current value.
Another option is to use views but it will not significantly improve performance.
Last edited by AnanthaP; 12-14-14 at 08:19.
Reason: More relevant