hello everyone. i have a database that gets about 5,000 records updated daily. each record contains numerical information lets say like "price", "commission" etc... i want to archive the values of these records daily so that i could display the monthly information in a line graph for comparison based on price, commission etc... my question is, what would be the best way of doing this? (memory usage should be kept in mind as well). note that information is basically obtained through an xml file which is parsed and the values of it put into a database. for the most part, it'd be 5000 same records that gets updated, but there can still be cases in which new records are added as well. here are some of the ideas i had but im sure there's a better, more efficient way to handle this.
1) my original idea was to maybe create a new table for every day of the month and just storing the data in the table accordingly. this way, i guess i could just join the tables based on the values. but then i don't know about this approach because that would mean that every month there would be like 150,000 records in the database and i dunno if that's too many to be parsing and whatnot?
2) i thought maybe i can just have another column in the table called "previous_stats" or something. every day when i insert new records into the database, i can somehow just write the values of the current day (if its an already existing record) into the column for everyday of the month. This way, i would only have to keep like 5,000 records in the database for the whole month (assuming that there aren't new records being added to the daily total) as information is already stored in the extra field. However, i'm thinking as time goes by, it might get more difficult to obtain and use the data accordingly so that's the downfall with this approach.
I'm sure there's a better approach to this so if anyone can help me out, it'd be greatly appreciated. Thank you.
