I have to develop an inventory tracking system that will allow the customer to run reports for any selected time frame of inventory turns - ins and outs. At the end of the report, it has to accurately show what the current inventory was at that time. The only way I can think of doing this is to have a CRON job run every night that records the inventory of every product by date. This way every day will have a beginning and final inventory tally. But because there are so many products, this will fill up the database pretty quick. I figure after a year a new table will need to be created and the previous one archived.
Does anyone else have any other ideas on how to achieve something like this without recording daily inventory levels?