I am building an application in which users will have a score in which they can receive it (+10 XP) when they do certain activities, but they can also lose it when using that score to make "purchases".
What I need is to find the best way to store this. At first I had thought of creating a column in the user table to save the score, so when the user earned points I would select this column, add the points earned to the previous score and update the column with the new data. The same thing would be done if the user lost points.
However, I believe that this is not the best possible way, as I believe that there could be conflicts if that same user tried to do an operation "at the same time" and this would lead to a misuse of these points, which cannot happen. The user could send two requests almost in parallel and that they would cause the score to be debited only once.
Thinking about it, I thought that the best way would be to create a separate table just for these scoring transactions, where if the user earned points I would add a new data to that table containing for example +10 in the "points" field and when the user lost I would add new data to the table containing -21 for example. Thus, I could get the user's current score by selecting all data and passing
SUM (points), which would return the exact amount of points the user has.
However, I don't know if this is really the best way to do what I want, since there would be a lot of "unnecessary" information being stored and mainly I would have to make a select on large amounts of data. Anyway, I would like to find out if there is a better way!
To be clear I am attaching a picture of what I intended to do: