MySQL Question regarding efficiency.

[dagnasty]

If I have a voting system, am anticipating thousands of polls, and each poll has a unique poll number in the database, and members (logged in) are only allowed 1 vote per poll. Would putting a votedby column for each poll.

Such as:

pollname      pollnumber  ................        votedby
so and so..  45345        ................        john34,bill44,joe,brandon366,

and code such as:(psuedo)

if $myusername."," is located in votedby
{
don't allow voting
}else {
allow voting
}


Be the most efficient way of keeping track of the users who voted on it and preventing them from doing it again?

How would you do it?

[corbin]

i would build the table like

poll_id - user - voted_option
23        Corbin    blue
27        Fred      greeen


and so on... but thats not very efficient at all... I cant think of any other way to do it that would be more efficient though...

[dagnasty]

What they voted for need not be logged as it wont be displayed anywhere and isn't relevant.

The poll's row where "votedby" is going to be pulled from the database to show the poll anyway, so would it be more efficient to do a "find $username with comma after it in the votedby string" or a

select * where user = $username and pollnumber = $pollnumber

?

[tomfmason]

The way that I would do it(not sure if it is more efficient or not )

I would create two tables one polls, with all the poll data , and the other votes
I would then have in the votes table

vote_id
Poll_id
username or user_id

Then before I posted a vote I would search the table like this

[code=php:0]"SELECT COUNT(*) as `vote_match` FROM `votes` WHERE `poll_id` = '$poll_id' AND `username` = '$username'"[/code]

Hope that helps,
Tom