Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts

    I need a little advice on table layout for a polling system.

    I abandoned a project probably a year ago now, but I took a fresh look at it today and got excited about it again so I'm looking to get around the issue I had when I quit so I can get this project moving again! Basically I'm setting up a polling system so I was intending on recording the IP address of each vote but I couldn't figure out if that would work well because IP addresses change constantly, so now I'm planning on simplifying it and just requiring that you must be a user and thus record the userid for each vote.

    Anyways, I'm setting up a votes table and I'm planning on it recording a voteid (automatically & unique), choiceid (given to each poll option so I can tally the number of votes per option), and userid and then when verifying a vote I will check if there is an entry in the votes table with the current userid, and choiceid, and if there is an entry that matches then it will tell the user that they already voted, et cetera. I guess I'm looking for feedback, and whether or not this makes sense and if it is efficient. Also isn't there a way to, instead of having a voteid, have a multi column primary key?

    Thanks CF!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Create table poll(
    Choiceid int,
    Userid int,
    Primary key (choiceid,userid)
    ) engine innodb;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Awesome, thank you! Would it be redundant to keep a tally of total votes in the polls table rather than COUNTing the votes that correspond to a poll? I'm wondering if the redundancy would be worth saving the server some work.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Just index the vote (that is, if you have a field called "vote" that records which way the person voted, index that column). Then doing
    Code:
    SELECT COUNT(*) FROM poll WHERE vote = 'never'
    will get the count from the index and should be so fast you will never care about improving it.

    In any case, I would never bother with a separate count unless you expect more than, say, 100,000 votes per poll.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •