I'm currently in the process of developing a megasite and have surprisingly been stumped. I'm looking for the best way for users to rate other member's information.

I'm thinking about creating another table in my MYSQL Database that will hold all of the ratings, but I want to limit people to only 1 VOTE per member. The database will have to check for duplicates before allowing that member to add their rating.

The only problem is, if I add each vote as an individual record in the table and don't tally up the votes until displayed on the ASP page, then I won't be able to have users SEARCH and SORT by rating when searching the database, simply because the total tally isn't calculated, because the rating is held in multiple rows, each row representing a vote from another user.

So I'm not sure if this is the best way to go about doing it because it later limits my search function.

If anyone else has some other ideas as to how to create a proper rating system, please let me know! I'm open to all ideas.

Thanks a lot!

Why not use REPLACE instead of INSERT and a unique index with the member ID. This way you'll only ever have 1 value.

You could run a scheduled job to recalculate the ratings every x mins or even fire this job after every member rating.