Design question

[talenx]

Hi all,
I was wondering if I may bounce a couple ideas off ya'll, I have a project that I am going to implement shortly but before I continue I wanted to know if there would be a more effecate method of storing a retrieving data.

The basic thought is:
I need to be able to provide a question answer interface that will have the following functions:
1. The user has the abilities to create their own questions; hence they will also define what kind of answer that question has.
2. The answers need to be stored in a method that will provide a fairly easy method of data retrieval.

I have come up with a couple options unfortunately they both have down sides.
1 creates a generic table that has data columns for every possible answer. Example col1_int, col2_int, col1_bit col2_bit...
The table has a reference to the question id in the question table.
Option 2:
Store the answer result in an xlm string in an answers table with again a reference to the question table via question id.

The downfall the option 1 obviously is flexibility I can't possible know every possible answer type. Not to method decision tree answers (answer that have additional question based on that answer (example: what kind of animals do you own? dog, cat bird. if dog and cat were selected there would be a series of questions that would posted per animal)

The downfall to option 2 is how to retrieve the stored xml string then retrieve it in a relational fashion (so it makes since to a BI tool.)

Sorry for the long post. But I just wanted to see if anyone has experience with this type of data model.

Any thoughts
Thanks
Talenx

 

[vongrunt]

Wise man once said "normalize until it hurts, denormalize until it works".

Especially since you mentioned decision trees are possibility, I'd suggest you to design DB that way. No repeatable columns (col1, col2, ...) and no "database in database" (XML).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Crowley16]

why not have a lookup table of question/answer types and just let users enter thier own type in there if not already entered.


--------------------
Procrastinate Now!

 

[rac2]

I. The tables that you need for storing the questions and possible answers are one side of the problem. They can include storing the datatype and formatting rules for the possible answers. For example, Question A is open-ended and short, it has one answer which will be stored as a string with a maximum length, the question statement is "When did you stop beating your dog?". Question B is a multiple choice question, it has 5 options, a single answer is allowed. The statement of option 1 is "Never", option 2 is "Last Week", etc.; the one answer will be stored as an integer code, the integer for option 1 is 1. Question C is a check-any-that-apply, it has 12 options, these are stored in a one-to-many table. Question D is a True or False, etc. Also store the way to deal with each question when it is not answered.

II. The If Question A is answered "2" then answer Question B issue is a matter of a structure consisting of items. This structure is something that must be stored as a thing in its own right. For example, Contingent Set T consists of Questions B and C. The rule for this set is Always ask B, ask C if the answer to B is option 3.

III. All of that must be structured into a GUI for people who will be building the questionnaire.

IV. Another GUI must be constructed for admininstering the questionnaire and accepting the responses and plugging in Not Answered values, all of that defined by much of the information represented in I. and II.

V. A completed questionnaire can be store in tables representing the questionnaire that was administered to Joe Blow on a date in a location. Each questionnaire will have stored all of the questions, always the same questions, and all of the answers. Questions which allow multiple answers will have multiple rows for a question on a completed questionnaire.

It might be sensible to store optional open-ended, long-winded answers in a table of BLOBs or TEXT datatype, although VARCHAR(255) ought to cover most things. As for BIT versus INT and DECIMAL you could have three columns for every answer, two NULL and one with the value. That is easy enough to retrieve and present.

VI. Presentation then is a third GUI which draws upon the representation of the questionnaire and upon the stored responses. The representation from I. and II. tells how to present the responses stored in V.

VII. That would be for the purpose of displaying one completed questionnaire. The final piece is the generation of reports which summarize all of the completed questionnaires with tabulations, percentages, and lists of open-ended answers.

Simple, no.