I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the last 30 seconds. Later, I want to retrieve this data from the database and use it to generate charts showing information like the number of oranges sold for a store over the last 24 hours, last x weeks, last x months, and last x years.
If I just dump everything into one table, it seems like it would grow very quickly, especially if you have lots of data sources (stores). My thought was that the data could be averaged so that it was less granular over time. That is, keep detailed records over the last couple of hours (entries in the DB for every 30 seconds), then perhaps averages of 15 minute time spans for the last few weeks, then keep averages of each day for the last few months, etc.
This way you have a large number of recent records, a good number of relatively older records, and a few old records. However all the data is still there, it's just summed up and averaged into one entry over days or months instead of 30 seconds.
Does this approach make sense? Is there a better approach? How would I organize this into a table? Would it be multiple tables? Is SQL (probably MySQL) a good fit or would something work better? Any thoughts on this would be greatly appreciated!