I have an application that needs to handle huge amounts of data. I get roughly
100,000 records per hour. I need to store this information for several months. I
would want to use MySQL as my DB.
I have devised a set of archiving tables (EveryHour, EveryDay, EveryWeek and
EveryMonth). I populate these tables from the previous tables after crunching. I am considering using multiple tables within the same level (as in EveryDay1, EveryDay2 etc). I would use a MetaData table which would have information about about how data is spread over these multiple tables based on start index and end index.
I want to know if there is a better approach to do this.