I need to design a questionnaire database. The questionnaire supports different question types like Single choice(select box), multi-select(checkbox) and matrix type of question. I want to know which is the best way to design to support all question types, in particular the matrix question type.

An Example of Matrix question type would be:

1. How much do you like the foods

        &n bsp  Very-Much&nbsp Ok&nbsp Neutral&nbsp So-So&nbsp Bad

a. Burger
b. Salad


Should I design this as 2 different questions and group them together so that analysis becomes easier, or should I design it as one unique question with sub-questions (maybe, I don't know).

And also, Very-Much, Ok, Neutral etc - would those be part of the question or answer ?

what is the level of varience?

Can you be more specific ? I did not understand.

Thanks

How much do the questions you expect to handle differ from each other. Is there a dominanat question type?

Due to the vastness of your project it would be a good idea ato start defining what it will not be doing (make the coffee, correct your algibra homework, deal with multi-type multivarient answers in spanish etc)

What we are after is a range of functions that are quite explicit. The more that can be defined in terms of something else the better.

Hi there,

Thanks for the reply. The questionnaire itself supports different question types. And the "matrix" question could also require multivarient answers.

For most part of it, it will all be radio buttons or all check boxes. But it could be a combination like some checkbox, some text box, etc. Example:

When did you eat this last ? How much do you like

        &n bsp;   3 months ago  Explain

a. Burger  [checkbox]  [textbox]
b. Taco    [checkbox]  [textbox]

Does this help ?

QUESTIONS
question_id
question_text

QUESTION_PARTS
question_part_id
question_id
answer_type
part_text

ANSWERS
answer_id
user_id
question_part_id
answer_text

With this schema each question can have as many parts as you like. For answers that require a text answer the option is there, otehrwise you can just use integers to indicate wither the first, second, third etc radio button was clicked, or a bitmask if there are several check boxes.

the answer_type field can be used to indicate whether the answer is a text, integer or bitmask type (just use 1 2 and 3 to indicate this and hard code that into your front end)

The user_id field indicates which user answered the question.

The end result is the allowing of as many answer parts as you like, it a relatively simple schema so MySQL should be able to handle it OK even with large numbers of users and questions, and it allows as many types of questions and answer types as you care to define in your front end.

Anybody got any better ideas? I doubt it :P

You seem to be thinking pritty much as I was there. Although the Question ID in the Answer table can be omitted without loss.

questionId is not in the answer table, question_part_id is, and it is very necessary, as the annswer is linked not only to the user that made it but also the question part that it is relating to.

but should have said: You seem to be thinking pritty much as I was there. Although the answer_id in the Answer table can be omitted without loss.

sorry