I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities associated with them.
For example, let there be a set of 100 users say U1,U2,...,U100
I need to insert their daily scores into my database.
Consider the total score obtained for a user U1 for period June 30 - July 6, is as follows
June 30 - 99 July 1 - 100 July 2 - 102 July 3 - 102 July 4 - 105 July 5 - 105 July 6 - 107
The database should keep daily scores of each users ,like
For user U1,
July 1- 1pt (100-99) July 2- 2pt (102-100) July 3- 0pt (102-102) July 4- 3pt (105-102) July 5- 0pt (105-105) July 6- 2pt (107-105)
Similarly the database should hold daily details of the full set of user's.
And on a later phase , I envision to take aggregate reports out of these data like total points scored on each day,week,month,etc; and to compare it with older data.
I need to start things from the scratch.I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ? Since I need to process about a million insertion daily,what all things should be taken care of ?
Does MySQL fits my requirement,If so what storage engine should be used ? Initially I envision to create a user table with a foreign key user id and monthly score tables with dates as fields.And later on I got suggestion to write things into a csv/excel first and then to load them to table after a particular period.
Does file insertion makes things more favorable in this regard.
Or should I try for some other databases,NoSQL methods ?
I am summarizing my requirements,I need to have a database of a million user's whose points are to be updated every day as separate entries. This will be done regularly such that there should a field per day for each using showing daily points,which could be aggregated in weekly/monthly/yearly basis. I am confused on the database design as well issue which could happen ever after deployment. A million or more DB operations are done every day.How the server and other things are to be considered in this case.
Any help will be highly appreciated.Thanks in advance.