Re: 8i max columns

My answers interspersed below:

Daniel A. Morgan

Jonathan Lewis wrote:

> >> >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

You could enforce this kind of thing in several ways. The most flexible being a before-insert trigger. But using my suggested example, and a few more fields would make it far more flexible, you could create a unique index covering the question_no and value fields. And use a foreign key to validate the possible answers to the questions.

> How do you impose efficient validation if you have one
> answer = one row ?

Foreign key or trigger. Either would do the job.

> 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.

It does not suggest just one survey per table. It suggests that I am trying to offer help here ... not do your job for you. Add another field called survey_no and you can handle an infinite number of surveys with an infinite number of questionnaires.

I am trying to provide some guidance in relationship to your original question. If you want it great. If not discard it. Either way I am not going to provide you with the perfect answer for your job. What I am trying to do is to indicate that a survey with 2000 questions can be easily handled if you think vertically and is a near nightmare if handled horizontally.

You seem totally focused on stuffing the data into a table. Have you considered trying to get it back out?

Write a select statement to find those people that answered more than 200 questions but less than 500 using your method and you'll have carpal tunnel syndrome before you are done. Use the vertical orientation and it is a few lines of very simple SQL. Received on Sat Apr 07 2001 - 10:56:45 CDT