| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: 8i max columns
>> >hold an unlimited number of questionnaires with an unlimited number of
>> >questions.
But a table with a single row per questionnaire is also able to hold an unlimited number of questionnaires. (I think we have to be a little careful with the word 'questionnaire' here - I think we are both using it in the sense of a single question paper sent to an individual involved in a survey/exam).
My solution gets a little more complex if the survey has more than 999 questions, of course, but all extreme cases should be taken on their own merits anyway.
Let's go back to your solution and raise some potential issues (purely to highlight that this type of thing is a special case, not to suggest that my suggestion is always right and yours is always wrong).
Questions 1 to 24 have answers in the range A to D
Questions 25 to 100 have answers in the range A to E
questions 15 to 20 may be left blank,
question 70 to 80 may be left blank
questions 95 to 100 may be blank if answer 90 is A
How do you impose efficient validation if you have one answer = one row ?
I note that your model suggests just one survey ( or questionnaire type, perhaps) per table, unless you have a second table which relates survey IDs to the questionnaire numbers used on that survey. If you are allowing multiple surveys in the single table (and I would be quite inclined to have one table per survey under the one row = one questionnaire model) then the validation problem becomes even more acute.
Then take the question about matching 17 answers - if you don't join the table to itself 17 times, the alternative would be something like:
select questionnaire_no
from q_table
where
(answer_no = 1 and answer_value = 'a')
or (answer_no = 7 and answer_value = 'c')
.....
or (answer_no = 99 and answer_value = 'a')
group by questionnaire_no
I suspect that your model's solution to this query always has to be a full tablescan --- which may be appropriate in some cases, but could be a big overhead in others, especially if you think you are going to have a generic 'answers to all questionnaire on all surveys' as opposed to one table per survey.
You might also ask where you get the end-user tool from that can spot that you have 17 question-related predicates and therefore generate the final HAVING predicate ?
You might also consider sizing:
One paper of 100 questions in my table =
paper number - 5 + 1 bytes say
100 x (1 + 1) for answers
5 bytes row overheads
211 bytes per paper
You solution has 100 rows per paper:
5 + 1 bytes paper number
2 +1 bytes per question number
1 +1 bytes per answer
5 bytes row overheads
16 bytes per row
1,600 bytes per paper.
The fact that the difference is a factor of 8 could be irrelevant, of course, depending on the number of participants; the number of surveys kept in the same table, the indexing strategy adopted, and the cost of creating, or finding, tools to pose questions to the database.
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html Daniel A. Morgan wrote in message <3ACD531F.D50D18F6_at_exesolutions.com>...Received on Fri Apr 06 2001 - 04:28:02 CDT
>Of course not that would be ridiculous. You have a single table with a
WHERE
>clause accessed using the LIKE or IN or EXISTS operator.
>
>Questionnaire_no field can hold an infinite number of questionnaires and
the
>Question_no field an infinite number of questions. Why would you need 17
tables
>for 17 responses? One table with three columns handles it all.
>
>Daniel A. Morgan
>
>
 |
 |