Thumbs up / Thumbs Down

[elgoog]

I am looking at making a Thumbs up / Thumbs Down type System

 

Only registered members should be able to vote, However i want to limit the voting to one vote per item.

 

There will be thousands of items in the table and hopefully lots of votes for each and i want to make sure the script and database stays as lightweight as possible

 

I would really appreciate some help with the logic and methodology to implement this type of system. Or any resources if you know of any.

 

 

Thanks in Advance

[neogemima]

There are probably many ways to implement this in your database with tables.  Probably the most straight forward way would be to create a table for each item to vote on.  That table could have a login name as a column, once someone votes, it inputs their login name and thumbs up or down for their vote.  If they try and vote twice, have a script that searches for their login name on that table, if it exists, send back an error and do not input another vote to the table.  That would be a basic way of doing it, but it would involve a lot of tables.

[elgoog]

Think i might have a solution. Not sure if it is the best way

 

users Table

UserID

Username

 

items Table

ItemID

ItemName

 

thumbs Table

ThumbID

items_ItemID

ThumbUpCount

ThumbDownCount

 

 

UserThumbs Table

thumbs_ThumbID

users_UserID

 

 

 

On Hitting Thumbs up or Thumbs Down it will check if a record for that user and ThumbID in the UserThumbs table. If not in there will increase the relevant count by 1. Next time they try voting, it will not let them as there is something in the UserThumbs Table

 

Not sure if this is the best approach

 

I am looking at how places like HotUkDeals implement their system as it really needs to be a scaleable solution.

[mikesta707]

instead of making a user thumbs table, why not just add a users_id column to the thumbs table. Everytime someone votes on something, you make an entry on that table. If they try to vote again, you check to see that there isnt an entry with the specific item id and their user_id and if there is you don't let them vote.

 

honestly you could just put the item_id and item_name as columns in the thumbs table also.

[Ken2k7]

I'm not sure you need both a ThumbUpCount and ThumbDownCount. I would use 1 for up, 0 for none and -1 for down. Seeing as a user can only vote once, it's not necessary to waste 2 columns just for one entry. Unless I missed the point. Also, add the userid into the thumbs table and change thumbs to UserThumbs because it makes more sense. I think you can do everything in that setup the same way.

 

Contrary to what mikesta707 says, don't put item_id and item_name to thumbs. That just adding more data than you need. It's just going to bloat up space.

[elgoog]

Thanks for the reply

 

I was simplyfying some of the tables, the item table has a lot more columns

 

The thought process on not having individual rows in one table, is so i could have a count in the field. And to query and get that number would hopefully be less resourceful that counting the rows. As the userid - vote check would only need to be done if someone tries to vote. Where as with 10 items diesplaying on a page. Having to loop through and do a count to display totals i was thinking may be intensive on the database server

[elgoog]

@ken2k7. The one column for the count rather than the two makes sense. Thanks

[Daniel0]

This is probably how I would lay it out:

 

users

user_id

etc.

 

items

item_id

etc.

 

item_votes

vote_id

user_id

item_id

value

 

So item_votes.value would simply be either 1 or 0, which respectively represent thumb up and thumb down. Then you could do something like this:

 

SELECT SUM(value) / COUNT(*) AS score FROM item_votes WHERE item_id = 123;

 

That should give you a number between 0 and 1. So if score was then 0.47 then 47% liked it (and 53% didn't).

[Ken2k7]

Thanks for the reply

 

I was simplyfying some of the tables, the item table has a lot more columns

 

The thought process on not having individual rows in one table, is so i could have a count in the field. And to query and get that number would hopefully be less resourceful that counting the rows. As the userid - vote check would only need to be done if someone tries to vote. Where as with 10 items diesplaying on a page. Having to loop through and do a count to display totals i was thinking may be intensive on the database server

In that case, go for it. In such voting, unless your site is super huge, I don't think you have anything to worry about. But it's also best to keep my set up if you ever want to poll votes from one user. If your site is huge, you can run a nightly CRON job. It's better than having to keep track of just totals because you wouldn't know where that number came from. In my set up, you at least know that. Same for Daniel0's set up.  =] Though, Daniel0, what if it's dumbs down? If it's only 1 or 0, then you can only go up.

[Daniel0]

Though, Daniel0, what if it's dumbs down? If it's only 1 or 0, then you can only go up.

 

Nope, not in the way I laid it out.

 

Say you got three "thumbs":

2 up = 2*1 = 2

1 down = 1*0 = 0

 

2 + 0 = 2, so it's got like 2 "points", but there were 3 votes. So 2/3 = .67, i.e. 67% liked it (67% gave thumbs up, the rest down). Had all of the three votes been thumb down, then it would be 0/3=0%, and had all of them given thumbs up then it would be 3/3=100%.

[Ken2k7]

Oh... stupid me. hehe..

[elgoog]

Thanks for the advice.