Mysql – desing the database for survey app with various types of answers

Definitely do not hard code your questionnaire. Use a relational database or xml files. I propose the following tables

• Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.

• Section: The sections a questionnaire is made up. Number of the section, section title, description.

• Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).

• Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.

• Respondent: The persons answering the questions. Personal data, user number.

• Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).

• Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).

• Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.

You need to make a distinction between the possible answers and the selected answers.

The Option table needs to be two tables. The Option table should be 1:M to Question and should include the possible answers for that question.

Then you need to make a new intersection entity, call it Selected_Option which sits between User and Option.

If your question gives the user an opportunity to fill in a value as an answer (i.e. "OTHER:...") then this value would be stored in the Selected_Option table. Otherwise the value chosen by the user would be the value found in Option.

EDIT:

Based on OP's clarification of requirements: What you need is not like a typical questionnaire model in the following ways:

• Your questions all have the same sets of answers (columns)
• Some of your answers (columns) are grouped together.
• Blocks of questions are grouped together.

Taking your form snapshot as a guide, I've divided up the elements of your form into entities which I've colour coded:

This could be accomodated by the following logical ERD:

Note that I've colour coded the entities in the ERD to correspond to the snapshot of your sample form to show the correlation.

One of the assumptions in this model is that each block has only one set of quesitons (i.e. one QUESTION_GROUP) which corresponds to the left-hand column in the block. This is a bit of a simplifying assumption.