poll stuff

OK, Taras and I were talking about writing polling software.  We
seemed to have widely differing ideas and then he got disconnected,
so here's what I was thinking.

Needs several tables:

poll - information about each poll
----------------------------------

poll_id         Unique ID
owner           Whoever started the poll (this might be nick_id or
                staff_id or whatever)
subject         Subject of the poll, e.g. "Is Tom gay?"
start           Start date
duration        How long it lasts, e.g. in days

Primary key: poll_id


answer - different available answers for each poll
--------------------------------------------------
answer_id       Unique ID
poll_id         Which poll this belongs to
short_text      Short text of the answer, e.g. "yes" or "no"
full_text       Full explanation, e.g. "Vote yes if you think that
                Tom is gay" or "Vote no if you think Tom is not gay"

Primary key: answer_id
Unique: poll_id+short_text (no poll can have two answers the same)

uservote - votes that users have made
-------------------------------------
vote_id         Unique ID
nick_id         User's unique ID from services nick table
poll_id         Poll this vote relates to
answer_id       Which answer from the above table they chose

Primary key: vote_id
Unique: nick_id+poll_id (no poll can have two votes from the same
user)


With the above structure it should be easy to do the necessary
things:

- List all the polls

  SELECT poll_id, subject FROM poll

- List available answers for a given poll

  SELECT answer_id, short_text, full_text FROM answer
  WHERE poll_id=$poll_id

- Add a new possible answer

  INSERT INTO answer (answer_id, poll_id, short_text, full_text)
  VALUES (NULL, $poll_id, "Yes", "Vote yes if you think that Tom is
  gay")

- Vote

  INSERT INTO uservote (vote_id, nick_id, poll_id, answer_id) VALUES
  (NULL, $nick_id, $poll_id, $answer_id)

  Unique index on nick_id+poll_id prevents one user from voting
  twice.
  
- List of votes a given user has participated in

  SELECT poll.subject, answer.short_text FROM poll, answer, uservote
  WHERE poll.poll_id=answer.poll_id && poll.poll_id=uservote.poll_id
  && uservote.nick_id=$nick_id

  while (get some rows) {
        print "In vote \"". $row[0] . "\" you voted:\n";
        print $row[1] . "\n";
  }

- Display the current totals for a given poll:

  print "Results for today's poll: Is Tom gay?:\n";
  
  SELECT answer_id, short_text FROM answer WHERE poll_id=$poll_id

  while (get answer_row) {
        $answer_id = $answer_row[0];
        $txt = $answer_row[1];

        SELECT COUNT(vote_id) FROM uservote WHERE
        answer_id=$answer_id && poll_id=$poll_id

        print $txt . ": ";
        if (get vote_row) {
                print $vote_row[0] . "\n";
        } else {
                print "0\n";
        }
   }

I don't really see how the above functionality can easily be
provided by any simpler system.  Any way you look at it you need to
separate poll info from actual votes.  And unless you want to make
arbitrary limits on how many different answers a poll can have, I
think you need a third table to keep available answers in.  What do
you think?

Given that services has 29 tables, dealing with 3 tables is really
looking pretty simple to me. :)

-- 
Andy Smith,
Janitor, The Blitzed IRC Network

David Habanec: I want alt.fan.habanec.  That would be fun.
Jeff Dawson:   You mean lonely.
  -- uk.telecom

pgpo1lXAB7XsL.pgp
Description: PGP signature