Price Tracking App: Best Practice

Question

I am working on a price tracking application that gathers data from different online stores and remap them to our own product database. We are already gathering prices together with the date they are retrieved.

Our first presentation of this data would be retrieving average price and also prices in a form of line chart between price and time, we would like to see how the price changes over time.

My question is, what are the best practices over storing and retrieving this historical data (for the purpose of charting)? I have several things in mind currently:

• Create an external app that will retrieve the values from the DB, generate a denormalized file containing required data, and purge the DB (to keep it small)
• Expose the file as JSON for easy client-side retrieval (instead of server-generated to reduce load)

Platform: IIS, ASP.NET, SQL Server 2005

We are currently using Google Charting API for other parts of the application, that may be taken into consideration.

Answer 1

Martin Fowler has a nice article dealing with things that change with time. It might give you some inspiration.

From the first sight storage seems pretty obvious: a table with ID, GTIN (or whatever else you're using to track products), date and price. Hence extracting data for charting is trivial: select range of prices for one or more GTINs and plot them onto graph.