Re: 8i max columns

I am ending this.

I have done this before successfully but is seems that you are more interested in debating or getting me to give you the complete solution (mutating tables is not an issue as the trigger would validate against domain data in another table). So I am dropping it. If someone else wishes to help you they may.

Daniel A. Morgan

.

Jonathan Lewis wrote:

> See below.
>
> --
> 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 <3ACF38BD.AD072718_at_exesolutions.com>...
>
> >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.
> >
>
> Foreign key is fine for ensuring legal values,
> but one of my examples was that question 95
> could be left blank if question 90 had answer 'A'
>
> Triggers won't work in general because of
> mutating table if you need to cross reference
> answers at input time. Plus they could lead
> to the carpal tunnel problem you mention below.
>
> >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 was just discussing a hypothetical case - I wouldn't
> dream of asking you to do my job. Of course you can make
> the system more flexible by adding another foreign key,
> but then that's another few bytes per question per
> questionnaire - you could be growing your dataset in a
> way that leaves the most efficient access path an
> inefficient access path.
>
> Flexibility is a nice concept, but simple and discardable
> is sometimes much more cost-effective. And simple
> often means more usable and more efficient anyway.
>
> >
> >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.
> >
>
> I'm not trying to argue a case that any questionnaire
> system should used one row per questionnaire - only
> that it isn't automatically the wrong way to do it. To
> answer your example, though, I would consider it
> perfectly reasonable for the data loading mechanism
> (which could well be the most labour intensive part
> of the system anyway) to handle the problem of counting
> answers, and store that in the row.
>
> But if not, an autonomous function that read a row
> based on its id and looped through its columns
> counting answers would take about 15 lines and
> 15 minutes to write.
Received on Sat Apr 07 2001 - 19:20:43 CDT