I want to implement something similar to Stack Exchange's reputation system. What would be the better practice: using an SQL aggregate function to recalculate every time it changes, or storing reputations in a table and manually altering it every time reputation goes up or comes down? How does Stack Exchange's implementation work?
So do we do this in SQL? Well, not really. We query to see if you're at the daily rep cap via SQL and make some decisions based on that, but we don't determine what reputation value you should have from that...it'd be a little crazy. If you want a detailed answer, keep reading.
First, credit goes to the entire team here, I didn't write the original rep code, or even all of the current code. The last major revision was from SuperDalgas and I, but there are a lot of hands that go into making this system tick.
It's a somewhat complicated question. First, let's visit how a rep recalc works.
We grab everything involved in your history and crawl over it doing a variety of things:
That's just the votes, we run over them and figure out what should be what, what totals each day has, etc. Now the way we store votes has 4 vital components (with regards to rep):
While this is great for storing the votes in a concise way, it suuuuuuuucks for determining reputation, because you have to do a Union of several sets of events (e.g. a Post deleted here counts but not here, unless it's a spam/offensive vote which always counts, etc.)
Once the votes are totaled up then we need to record a denormalized history of everything that affects a user:
These are generated by looking at the results of the votes from the previous step, and recorded in order (chronologically) for the user as a ledger of all events affecting their rep (these RepHistory entries are what's shown on the reputation tab in the profile).
All of the above is for a recalc only. The process itself is atomic and non-commit-dependent. We track all of this through in-memory objects so we can fully simulate a recalc and all fixed votes without actually running it, this helps immensely with debugging (I highly recommend this for anyone considering a reputation system - I have built several developer debug views just for this purpose).
Then, there's the vast majority of reputation change cases, a standard delta:
When you do anything that changes your rep, or someone else does it, we need to evaluate what's happened. A variety of things may happen at this point to determine the delta and record history, the simplest example is an upvote on a non-capped user:
Every other case is more complicated. For example an upvote reversal:
Then there's the more extreme cases where we need to walk votes for everyone involved, let's say a post is deleted (or undeleted, this includes migrations):
There are another dozen or so cases off the top of my head, but there's no reason to go into detail on every single one, you get the idea. If there's some crazy corner case (there are many) that you're curious about in particular, comment.