WEB how would you design a database for poll data?

Discussion in 'OT Technology' started by White Stormy, Feb 11, 2010.

  1. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I'm a database noob, but this is what I think..

    Assuming that some polls will have a different number of possible answers than others..

    (obviously pseudo code-ish)
    Code:
    [B]Poll Question Table[/B]
    int pollid | date start | date end | string question
    
    [B]Poll Answer Table[/B]
    int pollid | int answertotal | string answer
    
    if users have to be logged in, you don't have to worry about logging IPs, but how would you store which users have answered which polls? something like this?
    Code:
    [B]User Answer Table[/B]
    int pollid | int userid
    
    And if you wanted to just log IPs.. that would be pretty much the same?
    Code:
    [B]IP Answer Table[/B]
    int pollid | int ip
    
    If you only had one poll open at a time, would you need something more in that table than just one column of userid/IP? No need to store pollid every time on the offchance that poor coding mixes results from multiple polls if it should only be storing results for one poll at a time, right? that would be a lot of extra data for something that would take all of 5 minutes to test before going live.

    just trying to learn this stuff. tia
     
  2. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,650
    Likes Received:
    15
    Location:
    Atlanta
    This is how i would do it:

    Code:
    [B]pollQuestions
    [/B]* pollID (int, auto-increment)
    pollQuestion (varchar(200))
    
    [B]pollTimes[/B]
    *pollID (int)
    starttime(int)
    endtime(int)
    
    [B]pollAnswers[/B]
    *pollID (int)
    pollAnswer(varchar(500))
    
    [B]pollsTaken
    [/B]*pollID (int)
    IP (varchar(14))
    
    If I were you, i wouldn't worry about if the user is logged in or not, i'd worry about their IP. It's pointless to only log one or the other. Also, if you make the start/end times as timestamps, it'll make things much easier in the end. There are also several date -> timestamp functions that can help you out.
     
  3. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    :coold: thanks

    well logging username would allow the users to see their poll history. do you think there would be a better way to support that than a table of userids and pollids? I was thinking about two different situations where might log IP for one or userid for the other.

    I don't really understand the performance of databases.. haven't seen any real data on it. Do you think it would be better to just use the pollTimes timestamps or to include an isActive (0/1?) column in pollQuestions or pollTimes to determine if a poll is active?
     
  4. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,650
    Likes Received:
    15
    Location:
    Atlanta
    You could create another table for what users have taken what polls.

    As for the start/stop, you might want to change the polltimes table to include an isActive column as well. That way, you can query for something like:

    Code:
    $query = "SELECT pollID FROM pollTimes WHERE endTime <= " . time() . " AND isActive = 1;";
    
    Or, if for some reason, you need to shut down a poll before it's initial timeline is up, you can change the query to "isActive = 0;".
     
  5. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    :bowdown: that makes sense. thanks, dude
     
  6. Tom Ford

    Tom Ford OT Supporter

    Joined:
    Oct 31, 2004
    Messages:
    45,243
    Likes Received:
    0
    Location:
    Boston
  7. Supergeek

    Supergeek New Member

    Joined:
    Jan 23, 2007
    Messages:
    1,855
    Likes Received:
    0
    Location:
    Colorado
    It depends if you want to select from an enum list of poll options (A: are you male, female, transgender), multiple choice (B: which candy bars do you like), or fill in the blank (C: what year did you have your penis removed).

    table polls
    id, title, description, polltype (see A, B, C above), startdate, enddate, active (to allow to disable manually)

    table polloptions (multiple answer options for A and B polls, or not needed for C polls)
    id, pollid, choice

    table users
    id, username, password

    table answers (one answer per poll per user)
    id, pollid, userid, answer (an encoded string for A or B polls, or just the text of their answer for C)

    If you don't track by one of either user or IP, your poll *will* get abused.
     

Share This Page