Search code examples
databasedatabase-designrelational-databaserelational-model

Unique kind of questionnaire - Database design

For a research experiment I need to design a web application that implements a particular kind of questionnaire, the results of which will serve to derive some statistics and draw some conclusions.

In short, the questionnaire has to work as follows

1-> All answers are in a scale from absolutely false to absolutely correct or an open answer.

2-> Each set of questions corresponds to a given word (for example BIRD or FISH) and a description (a small sentence).

3-> Within one set, all questions can take the form of either a small sentence (text) or an image (to be categorized) and each set may contain an arbitrary number of questions. I need to be able to store the questions that correspond to one word as both text and images and be able to choose between them.

4-> To each set of question correspond 5 different kinds of help. In the questionnaire type A the user may choose one at will. In questionnaire type B all kinds are shown.

5-> The user has to respond to all the questions once (and they have to appear in a random order). Then, if type A, he has to choose a kind of help or refuse to get help and possibly modify his answer. Or, if type B, see all kinds of help one by one (in a random order) and possibly modify his answer.

6-> For each question, for each type of questionnaire I have to know if an answer was modified, which kind of help caused the user to modify and (if type B) whether this kind of help appeared 1st, 2nd, 3rd etc.

I realize those may not be the most complicated demands, but I am new to this and rather confused. My relations up to now look something like the following

  • QUESTIONNAIRE(id, help_choice, type, phase)
  • QUESTION_CATEG(id,type, name, description)
  • IMAGE(#qcat_id, filepath)
  • TEXT(#qcat_id, content)
  • INCLUDES(#questionnaire_id, #qcat_id)
  • HELP(#id, #qcat_id, content)
  • ANSWER(#questionnaire_id, #qcat_id, response, was_modified, help_taken, help_order).

With help_taken being able to take special values to denote no-help and help_choice being able to take special values to denote that all help was shown.

What is bothering me is the different types of questions. I don't really like (and I don't it will work) the way I have made the distinction between a text type and an image type question for a given question category. Knowing that for a given category (say BIRD) i may have both types (image and text), I have included a 'type' attribute in QUESTION_CATEG. But I feel like I am repeating information.

Do you have any hints as to how this might be fixed. Or even ideas for a completely different approach. Any help is welcome.

Solution

  • This seems to work.

    1. Q_CATEG(id, name, order, description, included)

    2. QUESTION(id, q_categ_id, type, content, order)

    3. AVAIL_ANSWER(id, question_id, content, order)

    4. HELP_CATEG(id, order, name, description)

    5. HELP(help_categ_id, q_categ_id, order, content)

    6. QUESTIONNAIRE(id, type, phase, start, end)

    7. GIVEN_ANSWER(questionnaire_id, question_id, answer_id, modified_answer_id, reason_answer_id, help_categ_id, help_order)