[RESOLVED] Some doubts regarding the creation of "Rating" system

[akhileshbc]

Hi guys

I'm about adding a "Rating" option to articles displayed. ie, users could rate them out of 5 stars. I did some thinking about how to implement and came up with two ideas:

Idea1
I'll add 2 new fields in tblArticle. ie, total_user_votes and total_num_of_votes.
The 5 stars that the user could vote has a value like this:

star1 = 0.2
star2 = 0.4
star3 = 0.6
star4 = 0.8
star5 = 1

When a user votes an article with any of this values, the total_user_votes field will get incremented with the value of the star(as listed above) and the total_num_of_votes field get incremented by a 1.

So, next time when I display this article, I will display "Rating = X%", where

X =  (total_user_votes / total_num_of_votes) * 100

Along with that, I would maintain another table(say tblVotes) which holds two fields: one for user_id (the member who had casted the vote) and the article_id(to which article he had voted for).

The advantage of this is,

• I don't have to store each user's vote(each vote) in separate table and sum it up, count it on each query and do the calculation(as you see in the Idea2 mentioned below).
• The seconds table that stores the user_id and article_id would allow me to prevent the vote made by the same user on the same article.

Idea2
I'll create a new table (say tblVotes) with the following fields: user_vote, user_id, article_id
So, during the displaying part, I would do a query to sum up the user_vote and the total count of records, for those records that match the currently requested article's id.

What do you think ? Which one seems to be the best ? I think the second one is in a normalized form.

[penagate]

How not to sort by average rating

[penagate]

Apart from that, I like your first idea.

[penagate]

Also: Bayesian Average

[akhileshbc]

Thanks

How not to sort by average rating

I didn't understood clearly. So, I'm trying to read it again and again. But I think, this is what it's used in Youtube comment system and Stackoverflow's replies. That is, the comment/post is pushed to the top based on number of +ve and -ve votes it gets.

Isn't it ?

[SambaNeko]

While it's an interesting read, it doesn't seem like the first link provided is helpful to you unless you want to switch to a binary (in the non-technical sense of positive vote/negative vote) rating system.

And here's a different page that I thought had an easier explanation of the calculation for the Baynesian average.

[akhileshbc]

Thanks

br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)

For the above equation, each terms would be:

avg_num_votes = total_num_of_votes_of_all_articles / total_num_of_articles

avg_rating = total_votes_received_of_all_articles / total_num_of_votes_of_all_articles

this_num_votes = total_num_of_votes_for_this_article
this_rating = total_votes_received_for_this_article

Am I correct ?

But isn't it CPU intensive ? I mean, there's some calculations to be made.

[penagate]

While it's an interesting read, it doesn't seem like the first link provided is helpful to you unless you want to switch to a binary (in the non-technical sense of positive vote/negative vote) rating system.

Yes. I should have mentioned that.

But isn't it CPU intensive ? I mean, there's some calculations to be made.

Addition, multiplication, and division are some of the most basic things a CPU can do. If you consider them "intensive", then you probably ought not to be writing code in an interpreted scripting language. In fact, you probably wouldn't be using an operating system, either.

[akhileshbc]

Addition, multiplication, and division are some of the most basic things a CPU can do. If you consider them "intensive", then you probably ought not to be writing code in an interpreted scripting language. In fact, you probably wouldn't be using an operating system, either.

ok

Table structure:

tblArticles
-----------

  article_id
  article_name
  user_votes
  num_of_votes

SELECT 
    article_id , article_name, user_votes, num_of_votes,
    (
      SELECT SUM(user_votes) FROM tblArticles
    ) AS total_user_votes ,
    (
      SELECT SUM(num_of_votes) FROM tblArticles 
    ) AS total_num_of_votes
  FROM tblArticles
  WHERE article_id = '123';

And now in PHP code, perform the calculation based on the previous equation.

Do you have some suggestions or corrections ?

Thanks