Keeping Track of User Points (Like SO)

Question

I want to be able to keep track of user points earned on my website. It isn't really like SO but the point system is similar in that I want each user to have a total and then I want to keep track of the transactions that got them to that total.

Should I keep a user total in the User table or should I just pull all the transactions that affect the User in questions point total, sum them and show the point total?

Seems like the latter is more work than needs to be done just to get the total. But then again I cringe at the idea of keeping the same data(more or less) in two different places.

What's the right way to design this?

EDIT: Took the advice. Using both and recalcs. I added a RecalcDate column, and if its over a day old it gets recalced. The total also get recalculated everytime a user does something that should affect their point total.

Answer 1

Both

You need to have a way of recalculating totals when things go wrong, say you add a new feature, or someone learns to exploit the system. You can keep a current total on the user table and a record of transactions to recalculate that total when needed...not every time you need the value to display.

You're not storing duplicate data so much as the audit history to fall back on, the only duplicate is one number in one column on the User table...the alternative is a user exploits the system, there's no way to roll it back. The same thing happened in the early days of SO, but they had the history and could recalculate totals without a sweat.

Answer 2

You should probably do a mix of both.

Keep a running total on the User table and also keep a log of each transaction that affects the user total, that way you don't need to do a sum of all the records, but you'll have them just in case.

The numbers may get out of sync, which is why you might need to do a recalc every now and then. (StackOverflow calls it a recalc, where they go through and update your reputation to what you should have).