It really depends on the traffic of the site, and you should probably don't bother yourself with such decisions unless:
- You've identified an actual bottleneck
- You've identified an actual bottleneck on a very similar scenario in the past
Premature optimization is the buzzword, and Donald Knuth describes it1 better than I could possibly:
Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.
The simplest approach is to have a
vote table, where you log all votes. Typical fields would be
voteTimestamp, and you could calculate the totals on each request. But, that will probably become a performance issue very soon, especially with a high traffic site as StackOverflow2.
In that case, my approach would be to run a scheduled background process3 that calculates totals and stores them in a different table (or the question table), and possibly even in a document storage database4. Or, even in a memory cache5, if it makes sense.
There are other ways to cache calculated totals, these are the simplest ones (I think).
As for the user information, they are expected to change less often, so you could probably get away with caching them, without any special database realm approach.
reads are quite faster than
writes and in common low traffic scenarios you'd be just fine by just sensibly caching your views. There is no definitive approach, mix and match as you identify bottlenecks.
1 Warning! PDF link.
2 We can't really isolate CodeReview, it's based on the common platform, if something is a bottleneck on StackOverflow, by default it is (or will be) on CodeReview.
3 Cron is your best friend!
4 Assuming the core database is relational.
5 Memcached is your other best friend!