Database design of Field that is calculated using other database values

Question

For a rating system that is dependent on factors such as votes, how do they store the reputation? Eg. Stackoverflow.

1. Do they calculate it everytime the page loads? eg. searching all posts and adding and multiplying?
2. Store it in a reputation field on the user table and increment it everytime a vote is being casted?

Which is the most efficient way to implement something like this?

Answer 1

Number 2 - why link it to page loads?

Answer 2

There is a way to figure this out for all cases, not just this one.

First, you know your data and how it's used better than anyone. You have to apply that knowledge in your designs. This is why almost every database question is correctly answered "depends", just like this one.

You're trading time for other time.

You can either spend time maintaining the calculated value with each change. Option 2.

Or

Calculate every time you need to return the value to a report/page/service call. Option 1.

If you do LOTS of queries against the value, but it changes only so often... you really just want to cache that value. You would store the calculation rather than requery constantly.

If it changes very quickly, but you report on it once a day... all the overhead it takes to maintain that value and the contention (read: scalability limiting) it introduces is more onerous than the once per day calculation.

In the example, you have a well defined, finite set of actions which can affect a user's rep. Each of those enumerated functions could easily call a function which takes a user_ID and quantity and update that column/attribute. Those actions happen more infrequently than the number is reported; and since reads exceed writes...

What is important to you? It also depends on what is important to you.

Let's say in the case of SO, they would like to precalculate the number but the penalty for reporting a number that's slightly incorrect is low. (Do you think Jon Skeet is sweating the +2 for a recent upvote?) But the updates are really slowing the website down... just for example. Since page load speed is what the business values above everything else, you may break the above guidelines.

Again, only you can decide what is truth for your business.