Poll system DB design 1

[theScien]

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?

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

I would go with a variant of scenario 1.

One DB, one table to hold the poll questions, and a related table to hold the possible answers (I assume this is a multiple-choice poll) to poll questions.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Right, so 2 related tables, maybe one for the polls, and other for related data.

Would you please elaborate a bit more, perhaps with a draft of both tables?
Thx.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

I'll gladly discuss the problem further. But no, I'm not going to create the tables for you.

Actually, my original question is incomplete.

Define a "poll" to be a set of one or more questions. Define a "question" to be one specific questions that is part of a poll. Define a "possible" to be one possible response to a single question in a particular poll.

Then polls would be stored in a table. A record in the "polls" table would have a unique id, a name for the poll, the user that created it, etc. If this software is also to be used for quizzes, then this table should also store the correct answer for each question.

All the questions for all polls would be stored in the "questions" table. This table would have columns to: uniquely identify each question, define which poll the question is a part of, the actual text of the question to be asked, the sort order of the question within a poll, how the possible answers for this question will be numbered when displayed (integer numbers, letters, etc.), etc.

All possibles would be stored in the "possibles" table. A row in this table will have a unique id, the id of the question to which this possible answer applies, the sort order for this possible within this question, the actual possible answer text, etc.

"possibles" relates to "questions", which relate to "polls".


User answers would then be stored in a pair of tables: one, "poll_response_users", to store the unique id of this set of responses, the userid of the person who took the poll and the unique identifier of the poll to which these responses apply. The other, "poll_response_answers", would store, one per record, the answer given and the poll question to which this answer applies.

"poll_response_answers" relates to "poll_response_users" and to "questions". "poll_response_answers" relates to "polls" and to whatever you're using for your user database.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Thanks sleipnir214, no, I didn't ask you to create the tables for me, just to draft them so we can visually discuss the methods to be employed.

Anyway, your explanation is actually quite good, it uses more tables then what I was expecting to use, but it could be the way forward, I was more enclined for less tables using this approach:

tblPOLLmain
-----------
[fldID], [fldPOLLname], [fldPOLLcreator], [fldPOLLquestion], [fldPOLLanswer1], [fldPOLLanswer2], [fldPOLLanswer3]

tblPOLLvotes
------------
[fldID], [fldPOLLid], [fldUSERname], [fldUSERvote]

RELATION: tblPOLLvotes.fldPOLLid links to tblPOLLmain.fldID

As you can see 2 tables and the polls would work, the only problem is the the ammount of answers each poll can have would have to be predefined, in the case above they are 3, but I could also dinamically create more columns, that would affect all polls in the table, I think I'll predefine the ammount of answers, most polls I've seen only have 3 to 8 answers anyway.

Does anyone see any other problem with this approach?

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

Blackwell's Law of Strategic Laziness states, "It always requires less effort and fewer resources to do it right the first time."

In this case, less is not more. Use the related tables.

Suppose that you do it your way, and you need to find the question where "Mickey Mouse" is a possible answer to some question. You have a user that put a poll up there, but he can't remember the wording of his question.

To query your table structure, the query would look like:

SELECT * from questions WHERE answer1 = 'Mickey Mouse' OR answer2 = 'Mickey Mouse' or.....

Do it my way, and the query is:

SELECT * from answers where possible_answer = 'Mickey Mouse'

Much simpler.

And here's the real kicker....

Suppose you build your single table to provide spots for 4 possible answers, and you write all your lookup queries based on that. Then one of your users wants to do a poll that requires 5 possible answers. You then add another column for the additional answer, and [/u]you will have to modify your scripts, too, to add mor "OR" clauses to your "WHERE" clauses in your queries.[/u] Do it my way, and you will have to modify neither table structures nor code, regardless of the number of possible answers to any question.

Also, every time you add columns to tables, your question display code, question storage code, and question modification code must either be changed to account for the new table, or you're going to have to play complicated games looping through a row return.

Also, your query (with all the "OR" clauses) is very inefficient at querying the table.

Also, your table structure is guaranteed to waste storage space with all those empty columns. And the amount of space you waste is guaranteed to increase over time.

Now you could, I suppose, arbitrarily tell your users that they simply can never ask questions with more than 4 answers. Then yes, the simple table structure would work. But why not provide the most valuable tool possible to your users?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!