Poll Database Schema

Question

What is the best database schema for polls? Is one-to-many relationship good for this? I'm thinking about having two tables:

poll_questions
    int id
    varchar body
    datetime created_at
    datetime updated_at

poll_answers
    int id
    varchar body
    int votes default 0
    int question_id (foreign key to poll_questions.id)
    datetime created_at
    datetime updated_at

Then there would also be third table for tracking who voted for an answer so users are able to vote only once:

poll_voting_history
    int id
    int question_id (foreign key to poll_questions.id)
    int answer_id (foreign key to poll_answers.id)
    int user_id (foreign key to the id in the users table)
    datetime created_at
    datetime updated_at

What are your thoughts? Am I thinking about it right?

are your users for this particular schema submitting their own custom answers? or from a pre-generated set of answers? — cwiggo, Oct 22 '14 at 14:28
You can add an `order` column in poll_answers if you want answers to be ordered — WoLfPwNeR, Feb 10 '17 at 21:44

score 13 · Accepted Answer · edited May 23 '17 at 12:34

13

The schema looks good, and yes, you'd need to track the user votes as well.

edited May 23 '17 at 12:34

Community

1
1

answered Feb 15 '10 at 08:42

cherouvim

31,725
15
104
153

score 5 · Answer 2 · answered May 29 '10 at 08:54

5

Note: the "votes" column of the poll_answers table isn't necessary. Votes can be determined by querying the Poll_voting_history table.

answered May 29 '10 at 08:54

pmilly

51
1
1

1

**votes** column is basically calculated field.After poll ended then we count the votes from **Poll_voting_history** table and store them in votes column in **poll_answers** table.If we remove **votes** column and show specific Poll to user then each time we hit the database to count the votes again and again (performance issues ) so this is not a good idea to remove **votes** column. Making a Join in thirdd table to determined votes then cost will be apply. – Muhammad Faizan Fareed May 25 '19 at 06:38

score 2 · Answer 3 · answered Dec 02 '12 at 21:34

2

I think that question_id in poll_voting_history is not necessary as well, since each answer points to a question, so based on a answer_id you can get the question_id that it belongs to.

answered Dec 02 '12 at 21:34

Mehdi Karamosly

5,388
2
32
50

you are absolutely right. I think we use them because some time we need to display Poll/Question and their voting history then we can use poll_id/question_id in poll_voting_history to show their history in aggregate or a simple way. We can easily refer this vote belong/referring to this Question/Poll. – Muhammad Faizan Fareed May 25 '19 at 06:55

score 1 · Answer 4 · answered Aug 08 '19 at 04:17

1

I would like to add the status of poll_questions and poll_answers as well, just in case if we need to activate or disable the current poll question during given date and time.

answered Aug 08 '19 at 04:17

Waseem Tahir

11
4

Poll Database Schema

4 Answers4

Linked