Best way to do rating system

jasondavis

On my social networking site users can rate profiles, I use the ajax rating script from

http://www.masugadesign.com/the-lab/scripts/unobtrusive-ajax-star-rating-bar/

I have modified it to work on users profile so every user has 1 and it list who rated the profile in 1 column in mysql using explode and implode.

This is unfortunate because I canot show users who rated them, well I can with explode but I cannot show what that person rated them.

But overall for performance would listing all id's that rated in a single row seperated by , 1,2,3,4 be faster or slowere then making a new table for everytime a user rates another user which would make hundreds of thousands of rows?

thanks for advice

cgraz

It's generally not a good idea to have multiple values in a single field.

If you want to keep track of who rated who (and the score), you could create a table to store this information as you said. Hundreds of thousands of records is not a problem for a good relational database like MySQL or PgSQL.

If speed is a concern, one suggestion is to create two new fields next to the items you are rating.

Example Table:
ItemID itemname Ratings TimesRated

When you do an insert, insert into both tables. One holding who rated who and what, then in the item table add the rating to the total and increment TimesRated by 1.

update <items_table>
set Ratings = Ratings + $newRating
, TimesRated = TimesRated + 1
where ItemID = $id

Then on your select query (or in PHP if you'd like), Ratings/TimesRated = Average Rating. I assume you'll be showing ratings more than the listing of who rated who, so this may help alleviate some strain.

However, there is also the trade-off of having to do two inserts and two selects (if showing both things). So this will all really depend on your situation. Hopefully this gives you another option to consider.

jasondavis

Is this what you are saying

-------------------------------------
Rating table 1 contains 
- who sent the rating to who
- the score they gave that user


toUserID         fromUserID      rateing_score_from_this_user
 23                         34                            5
 1                           47                            7
 26                         34                            3

-----------------------------------
-----------------------------------
Rating table 2 contains 
-whos rating score it is
-how many times they have been rated
-there total rating score

userID   times_rated  rateing_score
 23                2                 5
 1                  4                 7
 26                5                 3

cgraz

Yes. So if userID 23 was rated once more and given a rating of 4, you'd have

23/3/9

in table2.

You could also grab this from table1; table 2 is just if you want to potentially have a speed boost if you plan on displaying the ratings more often than the listing of who rated who. The drawback, once again, being the two inserts vs. just one. If you're on MySQL 5 you could even make the second insert using a trigger I'm sure.