How to update users total points in mysql

Question

I have a website where users submit posts that can be upvoted or downvoted.

At the moment a user's total earned points from all of his posts is not saved.

What is the best/common way to do this? How does a site like Reddit or StackOverflow do it?

Should I be adding and subtracting from the total user points every time someone votes on a post.

Answer 1

You could cache the result of the sum of votes per user over all posts, and invalidate the cache for each user when the user earns a new vote, or you could store the total count in the database and make sure that any upvote or downvote runs as a transaction that also updates the total.

Whether you do this using cache, or in MySQL using an extra table (I don't suggest putting it in your users table) depends on how big your site is and what you want to do with the votes.