How do you store and display if a user has voted or not on something?

Question

I'm working on a voting site and I'm wondering how I should handle votes.

For example on SO when you vote for a question (or answer) your vote is stored, and each time I go back on the page I can see that I already voted for this question because the up/down button are colored.

How do you do that? I mean I've several ideas but I'm wondering if it won't be an heavy load for the database.

Here is my ideas:

• Write an helper which will check for every question if a voted has been casted

  That's means that the number of queries will depends on the number of items displayed on the page (usually ~20)

• Loop on my items get the ids and for each page write a query which will returns if a vote has been casted or NULL

  Looks ok because only one query doesn't matter how much items on the page but may be break some MVC/Domain Model design, dunno.

• When User log in (or a guest for whom an anonymous user is created) retrieve all votes, store them in session, if a new vote is casted, just add it to the session.

  Looks nice because no queries is needed at all except the first one, however, this one and, depending on the number of votes casted (maybe a bunch for each user) can increase the size of the session for each users and potentially make the authentification slow.

How do you do? Any other ideas?

Answer 1

For eg : Lets assume you have a table to store votes and the user who cast it.

Lets assume you keep votes in user_votes when a vote is cast with a table structure something like the below one.

id of type int autoincrement
user_id type int, Foreign key representing users table
question_id type of int, Foreign key representing questions table

Now as the user will be logged in , when you are doing a fetch for the questions do a left join with the user_id in the user_votes table.

Something like

SELECT q.id, q.question, uv.id 
   FROM questions AS q 
   LEFT JOIN user_votes AS uv ON 
      uv.question_id = q.id AND 
      uv.user_id = <logged_in_user_id> 
   WHERE <Your criteria>

From the view you can check whether the id is present. If so mark voted, else not.

You may need to change your fields of the questions table and all. I am assuming you store questions in questions table and users in user table so and so. All having the primary key id .

Thanks