Ratings and Averages

[cmgmyr]

I'm making a "Hot or Not" site for one of my clients and I was just thinking about the rating system. How would the best way to handle a LOT of ratings and averages?

 

Option 1:

List everything in one big table. (id, userid, rating) then when a query is made it has to scan through all of this and get the average.

 

Option 2:

Use same table as above but add a "master" ratings table. (userid, total_rating, total_count) So that when I only have 1 row to query instead of 10,000+...

 

The reason why I want to keep the one big table is to keep track of who voted and what they rated but I know if I just kept this one only eventually it will slow everything down. I'm kind of leaning toward option 2, but that means that I would have to do and insert/update twice.

 

So...do you forsee any problems with using option 2? Do you have any other suggestions?

 

Thanks,

-Chris

[cmgmyr]

*bump* anyone?

[utexas_pjm]

Option 1.  With the use of indexes querying a 10k row table will be painless.  If the table gets to be too large you can create summary tables to query off of.

 

Best,

 

Patrick

[redking]

I disagree with patrick, querying 10,000 rows and adding them seems to be a lot of unnecessary processing, especially if you plan on having alot of users. I would go with option number 2 and only query the 10,000 row table to check if people have voted before.

[cyrixware]

Option 2 is much more easier than the other 1.

[Liquid Fire]

I have created a limit thing to hot or not for springbreakvillage.com.  The way i have it is there is one tabel the hold submissions(or whatever si being voted on).  I have another table that then hold all the rating for all submissions linked by the submission id.  so if i want to find out how many times a submission has been voted on and the average votes, i just do:

 

<?php
$query = "SELECT COUNT(id), ADD(rating) FROM ratings WHERE submission_id = '{$submission_id}' GROUP BY submission_id"
list($total_votes, $total_rating) = array_values($db->GetRow($query);
$average = ($total_votes/$total_rating);
?>

 

This seems to be the best way i could come up with.  You can also track the user_id inside the ratings table to make sure the person can onyl vote once on a submission and stuff like that.  Thsi also allows you to even break down and seem how many rating of 10, or 9, or 8, or 7, and so on a submission has received.

[utexas_pjm]

<?php
$query = "SELECT (COUNT(id) / ADD(rating)) as avg FROM ratings WHERE submission_id = '{$submission_id}' GROUP BY submission_id"
list($average) = array_values($db->GetRow($query);
?>

 

...will save you a clock cycle or three.

 

Best,

 

Patrick

[Liquid Fire]

<?php
$query = "SELECT (COUNT(id) / ADD(rating)) as avg FROM ratings WHERE submission_id = '{$submission_id}' GROUP BY submission_id"
list($average) = array_values($db->GetRow($query);
?>

 

...will save you a clock cycle or three.

 

Best,

 

Patrick

 

true but from my clients needs, they wanted to shwo the total number of votes for a submission so i needed to so it this way.

[448191]

true but from my clients needs, they wanted to shwo the total number of votes for a submission so i needed to so it this way.

 

I don't see how that justifies fetching 'raw' data from a database, instead of keeping generation of the needed data in the database... The rule of thumb is: if the database can do it, let it.