db optimization - have a total field or query table?
- by Dorian Fife
I have an app where users get points for actions they perform - either 1 point for an easy action or 2 for a difficult one.
I wish to display to the user the total number of points he got in my app and the points obtained this week (since Monday at midnight).
I have a table that records all actions, along with their time and number of points.
I have two alternatives and I'm not sure which is better:
Every time the user sees the report perform a query and sum the points the user got
Add two fields to each user that records the number of points obtained so far (total and weekly). The weekly points value will be set to 0 every Monday at midnight.
The first option is easier, but I'm afraid that as I'll get many users and actions queries will take a long time.
The second option bares the risk of inconsistency between the table of actions and the summary values.
I'm very interested in what you think is the best alternative here.
Thanks,
Dorian