I am going to write a small inventory tracking app for a small company.
What they need is only to keep track goods in, goods out, supplier, customer, and purchase price and sell price so we are able to calculate purchase price, sales price, and stock value.
- Puchase table is for goods in, it links to product table, have timestamp, quantity and price attributes to calculate the purchased price
- Sales table is for goods out, it links to product table, have timestamp, quantity and price attributes to calculate the sale price
My question is:
When we need to calculate the number of stock on hands, and the stock value, do we need to calculate the purchase table minus sales table? Also for calculating the values using First In First Out method? Normally this would be the easiest way, but I am thinking in the future where we are having large number of record, this method will be very expensive in term of processing power.
What would be the alternative for this?
I'll be appreciate all kind of input.
Thanks in advance.