hello everyone,
i'm trying to find an optimied way of implementing a user highscore/points system based on different action the user performs. the system is like this: every user is performing different actions (posting an article, uploading some photos etc.). all this actions have their corresponding tables, so i know which user did which action. for each action a number of points is given.
now i have to create a classification to show top monthly/annual users. here comes the tricky part. since i already have corresponding tables for each of the actions the user makes, i don't need to create a different table for this. the idea i have is to build a table for storing points for each user, like this.
id
id_user
date_started
last_update
points
Now, each time a user does an action i calculate the points earned for that action and i add to the points already existing in this table, also updating the date when she last earned points. date_started column is used to know when the user started to make points. This is useful for creating the monthly or annual classification.
Ok, so this is what i have in mind. But there's a "tiny" problem. If i have, let's say 10K users, for each of them i need to have a corresponding entry in my points table, each month. So in a year my table would have 120K rows...which i don't think it's so good.
This is why i'm asking for your help. Any ideas of how could i improve this schema? Thank you very much :)