Poll system DB design

[theSCIENTIST]

Hi folks, I'm beginning to develop a polling system for a web site, the coding is no problem, but my question is in relation to the database design.

What would work best:

1. One DB, one table, each row is a poll.
2. One DB, each table is a poll.
3. Each DB is a poll.

To help you to help me, let me tell you a bit about this system, it will be used only by the site registered users, so each vote can be traceable and done once only from that user.

Then I also want users to be able to create polls with the question and answers, data of who creates the poll is also collected.

I'm inclined to adopt option N.1, that would work faster, but each row (poll) would have to have lots of colums, not only for the creator, but also for the question, answers, votes, who voted and when and all the rest of it.

If I'm not going for option N.1, it would mean that tables and/or databases have to be dynamically created, and even worse, several calls to several DB's and/or tables have to be made.

Do you guys think going for option N.1 is the best or possible at all?

[spufi]

Table 1: Poll table

Poll number
Poll description
Who made the poll
number of options.

Table 2: Options table

Poll number
Option
Result.

You can use the poll number from the first table to load in the second one. and select all of the poll options in the second table based on the number of optiins in the first table. As you grab the options you can grab the result of that option. Mind you this is off the top of my head.
:)

[toicontien]

How about something similar, but slightly different from No. 2. I've got it written in psuedo-SQL to make things short.

polls (id NN, pollname, begin, end, vote_end, creator)
p-key(id)
f-key(creator) references users(uid)

pollinfo (id NN, topic, num_options, options, total_votes)
f-key(id) references polls(id)

participated_users (userid NN, voted_for, pollid NN, date_voted)
f-key(userid) references users(uid)
f-key(pollid) references polls(id)

NOTES: pollinfo(num_options) - This could be optional, but it's an easy way to store the number of options in each poll.

pollinfo(options) - Stores the options and number of votes for each option in the poll: option=#|option=#|... That way it would be easy to read the line of text in and use PHP explode() or Perl split() functions to store the options and number of votes in an associative array, or two parallel integer-indexed arrays.

participated_users(voted_for) - Contains the option text that the user voted for.

EDIT: I like spufi's idea. Create another table that handles the options and results. It'd be best to combine both our suggestions.

[AdamBrill]

Originally posted by spufi
Table 1: Poll table

Poll number
Poll description
Who made the poll
number of options.

Table 2: Options table

Poll number
Option
Result.That's looks pretty good except that every table should have an auto-increment id, and you didn't put it in the second one. The first table would be ok assuming that Poll number is the auto-increment id for the table. Also, the column names shouldn't have spaces... They should be more like poll_number or something similar. ;)

[AdamBrill]

Originally posted by toicontien
pollinfo(options) - Stores the options and number of votes for each option in the poll: option=#|option=#|... That way it would be easy to read the line of text in and use PHP explode() or Perl split() functions to store the options and number of votes in an associative array, or two parallel integer-indexed arrays.You should never put multiple values in one cell(such as option=#|option=#|). Spufi's way is much better since it has each option having it's own row...

[spufi]

Originally posted by AdamBrill
That's looks pretty good except that every table should have an auto-increment id, and you didn't put it in the second one. The first table would be ok assuming that Poll number is the auto-increment id for the table. Also, the column names shouldn't have spaces... They should be more like poll_number or something similar. ;)

I was using generic names and I would use more technical ones when writing up the code. I also wasn't 100% happy about the second table, and I thought about having the first field being option number and then having poll number being an alternate index.

[AdamBrill]

I figured that was what you were doing with the names, but I wanted to mention it so that theSCIENTIST would know. ;)

Also, if you would add an option number column like you said, that would be much better. :)