so I’m working on polls right now. I want to let users on my site create polls, which everyone can vote on. I have a few questions on how to set this up though.
How should I set up my tables? Right now my site has 3 tables. ‘users’ contains everything for users who make polls. ‘polls’ should contain every single poll, the question the answers, and the layout. ‘votes’ are the votes people cast on the polls.
polls:
poll_id unique id for every poll
user_id id of the user who created the poll
question the question of the poll
ans1-ans10 the possible answers for the poll
answers the amount of answers this poll has
style the layout style for this poll
votes:
vote_id unique id for every vote
poll_id id of the poll this vote is cast on
answer the answer that has been voted on
user_ip the IP of the voter, to try and keep the vote-abuse to a minimum
Is this a good way of setting up the tables?
How should I keep vote-abuse to a minimum? I want every user to only vote once. Ofcourse the options are limited, and there are ways around it all, but what’s the best way to deal with this?
All in all I’d say it’s not a bad design. The only concern I would have is the fact that you’re trying to store the possible answers in the table for the polls. If every poll always has exactly ten answers, then it’s fine, but since you have a field to hold the number of answers, I’m guessing this is not the case. If the number of possible answers varies, then your tables are not normalized. I’d two one of two things:
You could create a table called possible_answers:
id int auto_increment not null
answer text
Then create a second table to link an answer to a particular poll:
poll int
answer int
This option would be good if your polls have a lot of common answers like “yes” or “no.”
If the answers across all your polls tend to be different most of the time, then you don’t need the added complexity of option 1. Just make an answers table:
id int auto_increment not null
poll int
answer text
As for vote-abuse… your idea is not bad. There are a couple things to consider, though. When a user has a broadband connection, their ip address will change periodically (unless they paid for a static ip address). This doesn’t happen often and I don’t think it should be a concern, but there you go… Also, AOL subscribers are a pain to deal with in cases like this because every http request they make comes from a different ip address, so they could literally fill out one of your polls all day. This may or may not be of concern to you, but it’s something to think about.
As for alternative solutions, you could keep track of a user’s polls in your session data. Then the user would have to shut down his browser before he could fill it out again. That’s not a good solution, but it could help in situations like the one I mentioned above (the AOL users). You could also make use of cookies and your database to keep track of what polls users have filled out…
Some sort of combination of what you’re doing now and added layers of protection are probably your best bet.
Thanks! It does help, alot!
I never even thought about storing answers in a seperate table. Makes sense…
So let’s say this is my updated table polls:
poll_id unique id for every poll
user_id id of the user who created the poll
question the question of the poll
answers the amount of answers this poll has
style the layout style for this poll
and then here is my new table answers:
answer_id unique id for every answer
poll_id id of the poll this answer is in
answer the answer, text
answer_pos position of the answer in the poll
How would that look?
I guess that would make the PHP/Mysql code:
$answers = mysql_query("SELECT * FROM answers WHERE poll_id = $poll_id");
Nice job Maqrkk. I’d say that’s almost perfect. Since you have a field to order your answers, I’d add it to your query (which you were probably going to do anyway):
$answers = mysql_query("SELECT * FROM answers WHERE poll_id = $poll_id ORDER BY answer_pos;");
Additionally, I’d drop the ‘answers’ column of your polls table. If you want to know how many answers a poll has, simply do this:
$result = mysql_query("SELECT COUNT(answer_id) as num_answers FROM answers WHERE poll_id=$poll_id;");
$row = mysql_fetch_assoc($result);
$number_of_answers = $row['num_answers'];
With that done, your database is fairly well normalized (ie, no unnecessary or redundant data). I find that database normalization goes hand-in-hand with table joins. Most of the developers I’ve worked with who couldn’t grasp normalization were in that boat because they didn’t understand table joins. However, once you’ve mastered them, you’ll start to understand the real power behind a sql database.
Another cool trick is to use foreign key constraints. You can set a foreign key constraint on the poll_id column of your answers table that references the poll_id column of the polls table. Then, if you change the id of a poll, the mysql database will automatically update the answers that are linked to that poll. Also, if you delete a poll, the database will automatically delete any answers that were linked to that poll. It saves you a query, and helps to ensure that your databases don’t get junked up. This only works on InnoDB engines though (which I prefer).
Thanks for the responses!
Yes, I think I’ll drop ‘answers’ in the ‘polls’ table now. Seems obsolete.
I’m quite new to working with databases though, so the latter chatter doesn’t really make much sense to me… sorry!
