issue 778

[Robert Brenstein]

>On 5/23/05 11:12 PM, "Peter Salomon" <peter.salomon at gmx.net> wrote:
>
>Hi Peter,
>
>I CC to list because this is general db design issue.
>
>
>>  well, maybe i chose the wrong concept. and i agree - my methods ARE long...
>>  :-)  if you like, perhaps you could help thinking about another solution.
>>
>>  the application is a kind of knowledge test. we have about 200 questions a
>>  client must answer. each clients answer is 
>>stored in a table as bolean result:
>>  1 means answer correct, 0 answer is wrong.
>
>>  my 200 questions are parts of specific topics, so i want to know the
>>  percentage of correct and wrong answers belonging to each topic.
>
>If your next test will have 500 or 1000 questions, you will do 500 columns
>in table? Not good.
>
>Columns should be stable.
>Rows can grow.
>
>So you can Rotate your solution, and put questions and answers into rows of
>table.

Peter,

Ruslan is right, your approach is not optimal. It 
would be the right way in SPSS for example, but 
with a Valentina, you can do better and gain more 
flexibility. Besides, Valentina does not run 
efficiently with so many fields (columns) in a 
table.

>  > if correct answers are more than 70% client 
>is good, if they are less than 30%
>>  - well - client could be better.
>>
>>  so i made in valentina: one table in which all client´s answers are stored,
>>  e.g.
>>
>>          fldanswer1, fldanswer2, fldanswer3,....fldanswer200
>>  rcclient1
>>  rcclient2
>>  rcclient3
>>

The way I set up the databases for questionnaires of this sort is:

+++ table1: topics:
fields: topic_code, topic_title, topic_text, topic_priority

if topic_code is a number then you may not need 
topic_priority. Otherwise, topic_code can be 
arbitrary number and topic_priority can be used 
for ordering

if questionnaire has no grouping of questions, then this table is not used.

+++ table2: questions
fields: question_number, question_code, 
question_text, question_version, topic_ref, 
question_responses

I do not use recId so I can have questions 2a, 
2b, 2c and change the order at any time

If I need same questions in multiple languages, I 
just have question_english, question_spanish 
instead of question_text

topic_ref is a ref to topic_code
question_number is used for ordering questions 
and tracking progress. question_code contains 
question number is seen by subjects.
question_responses is not needed if you have only true-false responses.

+++ table3: subjects
fields: subject_code, subject_name, subject_group

Or whatever info you store on subjects. If you do 
not to store any info on them, then you may not 
need this table and just use subject_code instead 
of subject_ref below.

+++ table4: answers
fields: subject_ref, question_ref, answer_value

subject_ref is ref to subject_code
question_ref is ref to question_number
answer_value is the answer, as a number or text
answer_value is nullable so I can check whether subject answered all questions

+++ table5: feedback
fields: feedback_code, feedback_text, feedback_condition, feedback_group

Stuff to use for displaying responses for 
subjects at the end and/or during the 
questionnaire. feedback_condition can be 
percentage or an expression (if you development 
environment allows execution of expressions in 
variables).

NOTE: if 'group' fields are visible to users, 
then you also need their definitions each in own 
table, just like topics and use greoup_ref in 
other tables.



>  > just to have an example:
>>  to topic1 belongs answer1, answer2, answer3
>>
>>  so i used the expression to get the arithmetic middle of the fields:
>>  (fldanswer1 + fldanswer2 + fldanswer3) / 3 * 100
>>
>>  the same methodfield compares now the expression to get a string result, as
>>  "good" or "bad" (middle range is not of interest here)
>>
>>  i used the method you know already:
>>  "IF (fldanswer1 + fldanswer2 + fldanswer3) / 3 * 100) < 30, 'bad',
>  > IF(fldanswer1 + fldanswer2 + fldanswer3) / 3 
>* 100) < 70, 'middle', 'good'))
>

The above structure might sound overly 
complicated at first glance but it is more 
flexible and can give you any statistics you need 
for a single subject or the whole pool of 
subjects without much hassle, whole questionnaire 
or just a single topic. And it affords a single 
program to deal with different types of questions 
without any question-specific programming.

You won't be able to use the calculation fields 
(methods) but have to do these calcs in your 
program. But they are trivial. You can also 
easily track progress through questionnaire and 
provide feedback regardless whether you run as a 
standalone program or through web interface.

Moreover, this structure can also be used if you 
have questions that are conditional on answers to 
other questions. You just add an extra field 
specifying condition either to topic (for a group 
of questions) and/or to a specific question. And 
validation (which any self-respecting 
questionnaire should include) or distractor items 
can be handled the same.

Moreover2, you can set up a simple and nifty 
questionnaire editor that you can use to edit, 
add, move, remove, etc your questions, including 
checking the performance and loading of each 
question.

Robert Brenstein