This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database.
So recently I started designing an application where the main goal is the following:
- There are users
- The users can create reports (they some get points for creating one)
- They can provide additional data (additional points)
- They can view others reports
- They can unlock additional data about other peoples records, using their own points
The point of my question is: How to store and/or calculate the users current points?
1. First method
The schema, and points-calculating method I first thought of looks like this:
The users (can) have reports, the reports (can) have report details, which are connected to a report detail type, which contains the information about how much points it costs to unlock, or to create a detail from a given type. The user also (can) have transactions which contains the unlocks by the user.
From this schema i could create a query which returns, how much score the user have created, and how much he/she has spent with transactions. I could the substract the two, and i have the current score.
The problem with this: If the score for one report detail type gets modified the score for all users will be modified. Which i don't want to.
2. Second method
The second idea i had is to simply store the users points in a column in the users table, and whenever he/she tries to create/unlock something, update it. The schema could be identical, but there would be an extra column in the users table. For some reason it feels really unprofessional, and it also feels like, that i would store data, where it doesn't belong.
I would really appreciate any ideas, on which one of these would be better and why! If I'm started thinking about the problem in a completely wrong manner, I also would like to know about a better solution! Thanks for any help!