[ic] setting up a 'site poll' question

[Kevin Walsh]

> 
> Just one question, I am going to create a few different "polls" to place
> on my site, and before I get it all done one way, im just wondering what
> the pros/cons are for doing the results as a [log] to a file or creating
> a new table and having the form update that table.
> 
> I think ill just do a log, but I would like some input... 
>
It would depend upon whether your poll will be restricted to
logged-in users only, or will be a free-for-all.

If only logged-in people are allowed to vote then you may as well
have a poll table with columns something like this:

poll_votes:

    P| username     CHAR(20)
     | poll_id      SMALLINT
       value        TINYINT

If you define a unique primary key on the username and poll_id
columns then each user will only be allowed a single entry in
the table for each poll.  They could be allowed to change their
mind, but not enter multiple votes.

You could have a second poll table to define the allowed values
for each poll you run:

poll_option:

    P| poll_id      SMALLINT
     | value        TINYINT
       description  VARCHAR(50)

Again, with a unique primary key on the poll_id and value.

Lastly, you'd probably want a table to give each poll a name and
some sort of a description:

poll_description:

    P| poll_id      SMALLINT
       name         VARCHAR(50)
       description  VARCHAR(255)

This table could also contain poll start/end dates along with
whatever other details you think may be useful.

Once you have a system like that set up, you could run all manner
of fancy SQL queries to analyse and display the poll data you have
collected.  It would also be trivial to generate and display pie/bar
charts and things like that.

In theory, the following SQL should show a list of option descriptions
alongside the number of votes for that option.  Sorted in order of
popularity:

    SELECT  COUNT(v.value) AS votes, o.description
    FROM    poll_option AS o LEFT JOIN poll_votes AS v USING (poll_id, value),
            poll_description AS d
    WHERE   d.poll_id = o.poll_id
    AND     d.name = 'somepoll'
    GROUP BY o.value
    ORDER BY votes DESC, o.description

Of course, if the poll is a free-for-all and you're not too worried
about data analysis or duplicate votes then simply log the ballot
to a file, or in any way you see fit.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/