What is the best approach to store question data into a database?

Question

There is a wizard that can contain over 150 questions or just 10. Each question can be different from the question asked before. For example, it is possible that one question requires you to answer a "Yes / No" answer, but the next one can contain a multiple choice list with four options. There also should be an opportunity to fill in a completely open answer to the question asked in this wizard. The wizard consist of multiple sections.

The results of this wizard should also be evaluated. For example: "Did you ever forget to put on your shoes when going to work?" "Yes/No" If the user answered yes, we should evaluate this and tell him that he should put his shoes at the door, so he won't ever forget it again. (example)

The results of this wizard have to be saved into a database, but doing this in one table and just adding all data to this table looks quiet unhandy to me. Is there anyone here that has a clean solution so that for example if we ever have to rebuild a wizard like this, we can just use the same database?

Answer 1

You definitely shouldn't try to put it all in one big table. My design would look something like this:

Wizard

Id, Name, Preface, AnyOtherInfoYouMightWantToStore

Question

Id, WizardId, Question, SortNumber

Alternative

Id, QuestionId, TypeId (radio, checkbox, free-text, multiline freetext...), Name, SortNumber

Reply

Id, UserId (I imagine you'd have some user account solution), QuestionId, AlternativeId

Roughly like that. So that a wizard can contain many questions, and a question can contain many alternatives (yes + no radiobuttons would be one record each in Alternative table, but you could also do more complex things, such as a handful of checkboxes and, at the bottom, a "Other" textfield)

Answer 2

Barry Williams maintains a free library of data models, which includes models for four different questionnaires. These range from the simple to the complex.

His complex model looks like the place for you to start. But you'll probably want to extend it to include a Yes/No question type (rather than treating them as a special case of multiple choice), You will also need to add an entity for storing follow-up text.

It also sounds like you need some form of pageflow or other control structure, for managing the evaluation and response process. Opinion differs over whether such control information belongs in the database; to a certain extent this is a matter of taste, although the language you will use to write the wizard will have a bearing.

Answer 3

I suggest you read about database normalization on Wikipedia or another website - there are plenty of resources on this.

In a nutshell, you would have a table for questionnaires (Questionnaire) and a table for questions (Question) and a table for respondents (Respondent) and a table for answers (Answer).

You would then create questionnaires that constitute questions by linking the two tables. Respondents would be linked to records in the answer table, which would be linked to the records in the question table.

Answer 4

Also see a similar question/answer: here.

Answer 5

you can also find "Duane Hookum's At Your Survey" Access Database here:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

it's a (mostly) normalized database that you can look at for more information on how to create a survey database.