Suggestions on how to handle ratings

I've got an architecture question here that I'm struggling with.

Site design would have a user karma/rating/points system quite similar to how it's done here on stackoverflow. Things like comments, new posts, etc would give a user's ratings a boost of some sort. Similarly, things like up-votes on certain content would also provide a boost.

My question is how would you suggest this be implemented in the architecture.

My initial thought is that I'm going to be interested at some point in knowing exactly where any user's points came from, so simply having a field under each user that was incremented wouldn't provide that data I need.

So my model that I have in my head which provides full access to this data would be that each of these events (post, comment, upvote) logged and the user rating derived from those logs. The issue here is that I know this model doesn't scale well in the long term since calculating a user's rating would turn into an SQL query nightmare.

So there's some sort of middle ground here. Anybody g开发者_高级运维ot a suggestion what direction I might should go on this, or some good reading. I'm not even sure what to search for.

Who says you have to store the data only once?

I would guess that the model you have in your head is the 'right' one, so I'd stick with it; computing values over a lot of data - that can be dealt with.

Baes on what you're said, in addition to storing specific points against specific entities (like questions, answers, etc) you could also store a "running total" (score) that was incremented as points were assigned (I guess this would be an Int column in the "User" table in your database).

Going further you could have asynchronous processes that periodically checked the incremented value against the entities whose point-values contributed to the "score". This check might run against all the appropriate data, or just data within a certain time-band (i.e: since the last check).

Another approach is along the same lines but on an industrial scale; have you heard of OLTP and OLAP?

• OLTP (Online Transactional Processing) refers to a class of systems that facilitate and manage transaction-oriented applications.
• OLAP (Online Analytical Processing) is an approach to quickly answer multi-dimensional analytical queries.

It sounds like the model in your head is built around the expected "transactional" nature of your system, but you've also identified some requirements that would be better suited to a more analytical approach.

There's no reason why you can't do both, the big question would be how. The 'running total' solution does this but only for a very specific case. It might be worth looking at the wider system (and where you want to take it) and maybe "investing" in a more comprehensive partitioned system now.

If you want to have a history of all rating changes you need to log it somehow. You can make a file that will log them all and parse it, that way you can keep a lengthy history. If you want to go though SQL keep the last n changes in detail and hard code the rating before the changes.

If you can use something like 'Redis', it will help a lot. The idea is - write in redis and mysql both but when reading read only from redis. But, you need to pre-decide all the keys you are going to need in redis.

For example: For counting comments you can have keys like Comments:<userId>:count, which you can keep increasing on every comment posted by . Similary, you can have keys like Posts:<userId>:count etc. Basically, you are (in a way) caching all count(*) kind of queries in the redis server.

If at some point of time you need to change the karma/rating methods then you can use mysql data to re-generate the redis keys according to the need.