MySQL Rating/Voting system (accurratly ordering by best rated taking into account number of votes)

Question

Let's say I have a MySQL table that is something like this:

software table:

id int
name text
votes int
rating int

Where votes would be the number of times someone has voted for that item and rating would be the average of those votes.

Example data:

id: 0
name: FooBar!
votes: 5
rating: 3

Now another user comes along and rates this a 1. How would I caculate the new rating? would be be better to just make another table and enter a new row every time someone votes like

id int
software_id int
score int
user_id int

and then calculate the re-calculate the average rating every time? I would think it would take a toll on the server.

Second question:

I'd like to be able to order the software by the highest rated but I don't want a program with an average 5 star rating but only 1 vote to rank higher than a program with an average of 4 with 893 votes. How would I accomplish this?

Answer 1

Question 1a : How to calculate the new average rating:

vote count : 5
average rating: 3

new vote with rating of one

             5*3+1
 new rating= ----- = 16/6 = 8/3 = 2.666.. 
               6

You must take the "weight" (1/6 of all votes) of the new vote and the old average (5 occurences out of 6) into account .. the old average 3 is counted 5 times, the new rating once => 5*3 +1= 16. Then you divide by the total of 6 votes received.

Question 1b: Should you store votes?

really depends if you expect to be gamedor not

I suggest you do it.