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:
Code:
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
Code:
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.