Implementing A Ranking System

Question

I've seen several question on how to secure and prevent abuse of ranking systems (like staring movies, products, etc) but nothing on actually implementing it. To simplify this question, security is not a concern to me, the people accessing this system are all trusted, and abuse of the ranking system if it were to happen is trivial and easier to revert than cause. Anyways, I'm curious how to store the votes.

One thought is to have a votes table, that logs each vote, and then either immediately, at scheduled times, or on every load of the product (this seems inefficient, but maybe not) the votes are tallied and a double between 0 and 5 is updated into the product's entry in the product table.

Alternatively, I store in the products table a total score and a number of votes, and just divide that out when I display, and add the vote to total and increment number when someone votes.

Or is there a better way to do it that I haven't though of? I'd kind of like to just have a 'rating' field in the product table, but can't think of a way to update votes without some additional data.

Again, data integrity is important, but by no means necessary, any thoughts?

Answer 1

you mean, you'll store the votes seperately in a table and then update the respective ranking of product in product's table with a defined strategy? That seems like an inefficient way of storing it. Maybe there is a background to that reason; but why would you not want to store all votes in one table and keep making references of those votes to respective product. This gives you a real time count.

On UI you'll calculate a average of all the votings to a near integer to show. That would suffice, isn't it? Or am I missing something?

Answer 2

I would keep a "score" with your products but would also keep a vote table to see who voted for what. And when somebody votes, Insert vote, update product score.

This allows quick sorting and you also have a table to be able to recalculate the scores from and to stop people double-voting.

There is no need to wait to write the vote and update the scores. That will introduce problems and if it's acting like a traditional system (lots more reads than writes), gives you no benefits.

Answer 3

I agree with Oli. In addition, you can cache your score. So you update the product score in the cache and your application always picks up the cache value. Thus even on a page refresh, you would get the latest score without hitting the database.