Creating a rating system for my users.

I have a small site with a couple thousand users.  The users can currently upload up to 5 pictures of themselves.  I would like to create a rating system to allow users the ability to rate other users pictures and based on the ratings of the possible 5 images, come up with a single rating for that user. This type of thing is a little advanced from anything I've done in the past.  I would appreciate any help.

I will be displaying the images on the home page using some way to display the images fairly and evenly and the images can also be rated directly in the user's profile.

Heres what I'm heading for in a nut shell.

- Users can have up to 5 pictures (they may only have 1, or 2, or 3 etc)
- Each Image will be rated individually, using some way of displaying the images evenly and fair (not sure how yet)
- Based on the ratings from the images, and how many times the users images have been rated the user will have a calculated overall rating
- If the user decides to add or change a picture, this should not effect the past ratings.
- The rating will be on a scale of 1-10.

Basically Im trying to create something similar to or other large ratings systems, on a small scale for my niche site.  

I'm using Coldfusion 8 with a MySql 5 backend. I really appreciate anyones help.

Thanks for your time.
Who is Participating?
Scott BennettManager TechnologyCommented:
you should create a table like:

TableName: ImageRatings
      RatingID (PK auto incrementing number)
      ImageID (I'm assuming your already have an images table this would be an FK to that)
      UserID (this would be an FK to the user who rated the image)
      Rating (this will be an Int column for holding the 1-10 rating)

Then you make a simple form with radio buttons or something for the user to rate the picture. When that for gets submitted you do an insert or update if they have already voted for that image.

You can do your reporting by running queries against that table, and joins to your images table and your users table.

for example here is a query that will return the images along with their vote counts, and avg rating ordered the Highest average rating and highest number of votes to lowest

<cfquery name="GetHighestAvgRating" datasource="#Request.IWORK#">
SELECT ImageID, Count(RatingID) as VoteCount, AVG(Rating) as AvgRating
FROM ImageRatings
ORDER BY AVG(Rating) desc, Count(RatingID) desc
Since you have a small site, you might want a simpler approach. Here is an alternative...

You can add 3 columns to your existing image table:

Where RatingTotal holds the cumulative total of all ratings and RatingCount is the total number of votes. Rating is simply RatingTotal/RatingCount.

As an example, if someone votes "8" for photoID 34 your update query would look like this:

UPDATE imagetable
SET RatingCount = RatingCount + 8
, RatingTotal = RatingTotal + 1
, Rating = (RatingCount + 8)/(RatingTotal + 1)
WHERE photoID = 34

This way all of your rating are updated in real time with 1 query.

Some considerations would be:
Do you want to log all of the details of each vote? (or do you just want the totals)
Do you have to be logged in to vote?

Scott BennettManager TechnologyCommented:
dan's approach would be simpler and would keep your database smaller and would work fine for now, but it really depends on what your needs are, and how much control you want to have over your data. In the long term you may want to add more functionality to your image rating application and storing the data as I suggest will give you more options.
  If you track each vote individually you will be able to restrict users from easily placing multiple votes for the same image, preventing them from skewing the numbers in their favor. You can see which users are placing the most votes on your site, and many other things that may be of interest to you.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

to SBennett's point:
I agree that logging all of the data MAY be useful, but if you allow guests to vote, then it wouldn't make a difference for either approach as far as restricting votes unless you wanted to restrict votes at the IP level. You would most likely write a cookie that either directly signified that a vote has been cast or that links to a database and will then link to all votes cast for that guest. In either case clearing one's cookies will allow them to vote as many times as they want.
Scott BennettManager TechnologyCommented:
I never said it was bullet proof, all they would have to do is create a bunch of fake accounts to make multiple votes. of course there are ways to make it too much of a pain to create multiple accounts like having to have a uniqe validated email address and using cookies/ip tracking to see if someone is creating excessive amounts of accounts from the same location in a short period of time, using CAPTCHA on your registration screen, etc, etc, but you don't really have to worry about that too much until you get bigger and grow a big target on your back.

My mian point is really that storing the Ratings stats directly in the Image table will work fine if you don't expect to ever make any enhancements to the image rating system and all you will ever need is the data in those three columns. If you expect your site to grow, or you expect to make further enhancments to the image rating system , or you want to enforce rules that restrict users from manipulating the rating process, you probably want to go with the data structure that give you the most options.

MFredinAuthor Commented:
Thanks so much for the help guys.  Both are great solutions.  

SBennett I like your solution since it gives me alot more options and it gives me some room to grow.
I like Dan's solution from the simplicity standing.

The only thing is a user doesn't have to be a member to vote on an image.  I guess I could just take out the userID field and change it to userIP and record the user's IP address instead.  How does that work tho?  What if there are alot of users behind 1 IP and I checked to see if that IP has already voted on that image. Then it wouldn't be correct.  What do you do in a situation like that?  It doesn't have to be 100% fool proof but it's not fair if 1 user can keep voting on 1 image.

But also consider that with a more robust site, you wouldn't want to have to calculate ratings in real time. Especially for pulling up the 10 most popular (for instance), so having a field in the image table that gets updated in real time (or on a scheduled basis) might also be a good long term solution. And you can always add in logging down the road.

So either way may lead to a good long term solution. If I were programming the site,  I would probably start with my approach, but also use a table structured as SBennett suggested to log the individual votes.

Scott BennettManager TechnologyCommented:
I would use leave the userid field there and make it so it can be null, and just allow multiple votes for null userids and add an IP address column and a timestamp column in ImageRatings table as well. then you can set your logic so that when a user submits a vote

if the user is logged in:
    Check to see if there are any votes from that userid for this imageid or if there are any votes from
    that IP within a certain time frame (you decide how long). If you find a record don't ad another one.

else if the user is not logged in
   Check to see if there are any votes from that IP within a certain time frame. If you find a record don't
   ad another one.

Scott BennettManager TechnologyCommented:
True, if you get really big you will want to keep a log of each vote but keep running totals of the ratings stats for each image in the image table for performance.

I would suggest, if/when you get to that point, writing a stored procedure that handles all the logic for adding a vote and recalculating the stats for the image all in one transaction.
I would leave the UserID blank if it is a guest vote, but you should put a unique field, maybe a new field called GuestID which is a FK to a guest table that simply has:

GuestID -  Identity Field
IP Address

You would then store this GuestID as a cookie for this guest user and you would then be able to tell which items he has already voted on. This way you are restricting only that one guest user and not the entire IP. The IP storage should really only be used for information and possibly to use in the future to restrict problem IPs.
Scott BennettManager TechnologyCommented:
This is good. I would also make is so if a person logs in, you use that guestid cookie to check to see if they have voted on any images from that computer within a certain timeframe (maybe an hour), and then associate those votes with the userid.
MFredinAuthor Commented:
Both solutions have good points.  How about a combo of both?  Something like this...

When a user votes, check against the logged votes on the image to find out if this user has voted on this image before or not.

If the user has voted on the image, update the previous voting log with the new value on the ImageRatings table.
If the user hasn't voted, insert a new log for this image into the ImageRatings table.

After the ImageRatings table is updated/inserted, update the rating field on the Images table for the image just voted on.  

This way I have a log to track who has voted on the image, and the image has a real-time rating associated with it that can be easily queried.

Does this make sense for what I'm trying to do while remaining scalable for future expansion?
I think you are well on your way to an excellent solution.
Scott BennettManager TechnologyCommented:
that sounds fine but if you are going to do this process with two coldfusion queries you should use a named cflock to make sure only one vote process can occur for one image at a time like:

<cflock name="ImageRate#ImageID#">
<!--- put your two update queries here --->
<cfcatch type="Any"></cfcatch>

or better yet use <cftransaction>
Also, I would add (NOLOCK) to all database reads from the image table. This will ensure no deadlocking issues and should also result in faster searches.

SELECT columns
WHERE condition
Scott BennettManager TechnologyCommented:
Is there anything we left out?  A couple of programmers with too much time on our hands trying to get the last word in can be quite the cornucopia of knowledge =)
Scott BennettManager TechnologyCommented:
by the way don't forget that NOLOCK thing... I run a very high traffic site and when I discoverd (nolock) and placed that in a few long running queries... you wouldn't believe the difference it made in performance some pages loaded 1000 times fatser.
MFredinAuthor Commented:
Great!  The table structure and logic makes sense to me.

Here's a map of my tables then to double check I'm on the same page:

user_id (pk)

rating - updated each time a rating gets logged in the IMAGE_RATINGS table for this image

user_id(fk) - allow null
guest_id(fk) - allow null

guest_id (pk)
insert_timestamp - timestamp of creation

I'm a little lost on writing the cookie and using it to check against my timestamp and using it to restrict multiple unique votes without being logged in.  If you guys wouldn't mind helping me out on that I will up the points.  Otherwise if it needs to be asked as a seperate question I can do that too.
MFredinAuthor Commented:
Ooops, I don't really need rating_count on the IMAGE_RATINGS table.
Scott BennettManager TechnologyCommented:
basically you check to see if a cookie exists and if not you create a guest record and set a cookie. (using <cfcookie>)

Then when they vote you check to see if they are logged in, if so use userid, if not use guestid to check if they have already voted on this image.

You will want to check the time stamp when you create a guest record to see how many guest records have been generated with that IP within a certain time frame. because peopl can clear their cookies and vote again. if you see a lot of guestids comming from the same ip withing a short period of time you should restrict them or keep assigining them the same ID
Everything looks right to me, but RatingCount doesn't seem to apply to the IMAGE_RATINGS table, I believe you can remove that column.

The guest cookie can be written like this:
<CFCOOKIE name="guestID" value="#GuestIDvaluefromdatabase#" expires="never">

The UserID will be written similarly

Make sure this page is served to the user (no CFLOCATION), otherwise it will not be written correctly.

When looking up the user you first check for the cookies:
<CFIF ISDEFINED("cookie.userID")>
  lookup query using #cookie.userID#
<CFELSEIF ISDEFINED("cookie.guestID")>
  lookup query using #cookie.guestID#

SBennett, your 4:08 post was 100% right. :)
MFredinAuthor Commented:
Awesome! Thanks so much guys! Can't wait to get working on this now that I'm confident it will work!  

Use a named cflock with my rating queries - CHECK
User NOLOCK in the queries  - CHECK

Thanks again!
No problem. Good luck!
MFredinAuthor Commented:
I'm finding no way to increase the points on this question.  Do you know where I can do that?
I believe that 500 is the maximum for 1 question. Generally what you would do is open an additional question directed at a particular expert, such as "Points for SBennett" and assign points through these additional questions.
MFredinAuthor Commented:
Could you resplit the points on this question evenly?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.