Questionnaire Database Structure

[idire]

Not sure if this in the right place.

 

If I wanted to make a system that allowed users to create questionnaires, what would be the most efficient way of storing the questions / answers

 

If i wanted the system to only have one questionnaire, I would just make a table contain the precise number of columns for questions, and the same for answers.

 

But how would I have the mysql database structure for a questionnaire that could have any number of questions? each with a different set/type of answer.

 

I thought about using an array for an answer and storing a text string, however I dont think this would be efficient for outputting answers.

 

Opinions?

Thanks

[corbin]

table qaire:

 

quiz_id

quiz_name

quiz_owner

 

 

table questions:

quiz_id

question_id

question_body

 

table answers:

quiz_id

question_id

answer_id

answer_body

 

 

 

Would be the basic gist of what I would do.

[idire]

Here is what i came up with?

 

Is storing the username for each question the solution for making sure a user doesnt answer twice?

 

and also is there a need to store questionnaireid on the answer if its linked via the question?

 

[corbin]

When you create your users table, make a user_id column that's an auto incrementing primary key.  Then, in the questionnaire table, use the user_id column, not user name.

 

 

I really don't think question_type should be varchar, and I don't think question_options should be text.  But I don't know exactly what is going to go into those.

 

"and also is there a need to store questionnaireid on the answer if its linked via the question?"

 

 

If each answer is linked to one and only 1 question, and each question is linked to 1 and only 1 questionnaire, you do not need to store the questionnaire ID in the answer table.

 

 

 

 

 

"Is storing the username for each question the solution for making sure a user doesnt answer twice?"

 

 

No!!!

 

 

Think about what that would do.

 

 

Oh wait, I just realized something.

 

 

 

You misunderstood my post earlier.  I meant this:

table qaire:

 

quiz_id stores the questionnaire id

quiz_name  stores the questionnaire name

quiz_owner  stores the questionnaire creator ID

 

 

table questions:

quiz_id  stores the id of the questionnaire to which this question belond

question_id  stores the question id (auto incrementing primary key)

question_body stores the question text ("What color is the sky?")

 

table answers:

quiz_id just realized this is useless and I'm not sure why I put this here.

question_id  Maps the answer to a question

answer_id  unique answer ID (auto incrementing primary key)

answer_body  answer content ("Blue")

 

 

 

Would be the basic gist of what I would do.

 

What people actually answer would be stored in a different table:

 

 

user_answers:

user_id

question_id

answer_id

 

 

 

Then to see if someone had completed a questionnaire, you would just check for their answers in that table.

[idire]

how would you then link the tables?

 

question_type would be multiple choice / or text field / integer option, its for telling php how to generate the questionaire html

 

question_options are the multiple choice options that php will use to generate the html options for questionnaire.

 

Here is my new diagram

 

[corbin]

 

 

 

"question_type would be multiple choice / or text field / integer option, its for telling php how to generate the questionaire html"

 

Oh.  It would be more efficient to have a numeric column.

 

 

"question_options are the multiple choice options that php will use to generate the html options for questionnaire."

 

 

Multiple choice answers go in the answer table.  Well that was my plan anyway.

 

 

 

 

To be honest, I thought the questionnaire was going to be multiple choice only.

 

 

The user_answer table would need to look like:

 

 

CREATE TABLE user_answer (

    user_id INT,

    question_id INT,

    answer_id INT,

    answer_body TEXT,

    answer_int INT

) ENGINE=InnoDB;

 

 

Then the answer would have to relate back to question type.

[idire]

I was going to use the question table to generate the questionnaire, then store the answers in the answer table, so i'd need to multiple choice options in the question table in order to make the html questionnaire.

 

and you have removed the user_answer table again right?

 

 

The user_answer table would need to look like:

 

 

CREATE TABLE user_answer (

    user_id INT,

    question_id INT,

    answer_id INT,

    answer_body TEXT,

    answer_int INT

) ENGINE=InnoDB;

 

 

Then the answer would have to relate back to question type.

 

What would be stored in answer_int? Is it the alternative to answer_body depending if the answer is a number or text?

 

Thanks for the help so far.

[idire]

[corbin]

"I was going to use the question table to generate the questionnaire, then store the answers in the answer table, so i'd need to multiple choice options in the question table in order to make the html questionnaire."

 

 

Keep the multiple choice answers in the answer table.  Storing arrays in a text field is a database normalization no-no.

 

 

 

"and you have removed the user_answer table again right?"

 

no

 

 

"What would be stored in answer_int? Is it the alternative to answer_body depending if the answer is a number or text?

 

Thanks for the help so far."

 

Yup and no problem.

[idire]

Why is storing an array a problem?

 

The image on the post above is my current structure, its supposed to allow for questions where the answer can be multiple choice, a number or a string.

 

This is to allow for open/closed ended questions.