Questionnaire database design - which way is better?

Question

I have ONE long html page, several sets of questions divided into small sections (approx. 15 sub-sections in one page), the total of questions are about 100 questions: varies from input, multiple choice, checkboxes, radio buttons, textarea, and file upload. One question could contain many answers which obtained either from group of checkboxes, group of select list, group of multi-select, or all of them combined into one answer. I thought I would use this database design below but found out lately that it isn't the good approach after all.

1. One customer could only have one set of question: one customer per 100 questions.
2. For the old approach I don't keep question in the database but assign as constant in PHP coding instead. Problem is I have to compare the question in PHP to get it synchronize with the answer in the database. If one question had been altered/deleted/moved from PHP, I would definitely get lost to match it with the answer in Questionnaire database. Better solution?
3. Could I be able to keep multiple answers obtained from multiple elements in form into one field as one answer? How could I retrieve this field and display it again for customer viewing on form?
4. Which option down below should I go for?

OPTION 1: Old Approach (1 table)

TABLE: Questionnaire

• ID (PK)
• CustomerID
• Status
• A1
• A2
• A3
• .
• .
• .
• A100

OPTION 2: New Approach (2 tables)

TABLE: Question

• QID (PK)
• Question (varchar)

TABLE: Answer

• AID (PK)
• CustomerID
• QID (int)
• Answer (varchar)

Or OPTION 3?

Answer 1

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.

Answer 2

You need a few tables,

1 - The questions ( question id, input type, visible, question type, question text, expected answers....)

2 - The Answers ( question id, user id, activity id, answer....)

3 - The users ( user id, user name......)

4 - A table to hold a question/answer activity (activity id, data/time, user id)

You may also like to have a table that specifies the questions that should be applied for each activity - either grouped by user or maybe a question collection. The foreign/primary keys will be the columns that have the same name in multiple tables and should be indexed.

If you use this structure, you should be able to add a question or user or change an answer without having to change your schema or presentation code - make sure that the presentation code is dynamically created at run time - you just need to add a record in the appropriate place.

This approach may take longer to develop initially than a hard coded approach, but will much simpler to maintain as you only will need to change data to change behavior.

(A tip, to create your presentation layer, you will need a query that gets the appropriate questions to be displayed, then loop through this result set and call a method to render to question on the screen, the methods to chose being appropriate to the presentation of that question [text box, radio group, etc])

Answer 3

If you want to support questionnaire revisions, the following might be what you are looking for.

• Each questionnaire has one or more revisions.
• Each revision consists of a set of questions
• A question might be a free text question or belong to several choices
• The answers of a user are stored as an interview.
• An interview belongs to a revision. A revision must be immutable. Thereby, we know what question the answer belongs to even if the questionnaire has changed.