| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: constraining one correct answer but multiple invalid answers
hpuxrac wrote:
> jcharpak_at_att.net wrote:
> > hpuxrac wrote:
> > > dombrooks wrote:
> > > > You could create a function-based index index which has two arguments -
> > > > the question and id and the yes/no flag.
> > > > This would then return the question id when the when the flag is 'Y'
> > > > but NULL otherwise.
> > > >
> > > > Then create a unique function-based index using this function passing
> > > > in the question id column and the yes/no flag column.
> > > >
> > > > Null entries are not entered into the index and an entry is only made
> > > > containing the question id when the flag is yes.
> > >
> > > To me at least your suggestion seems to fit into the category of
> > > putting lipstick on a pig. There's something fundamentally wrong with
> > > the current ERD design as noted by the OP.
> > >
> > > To produce scalable applications you have to get the design part right
> > > in the first place.
> > >
> > > In the case cited by the OP they have a supertype with 2 subtypes. You
> > > either support that in a relational system by using some kind of type
> > > identifier column in one table
> >
> > This is the correct_yn column in answers. it indicates the type of
> > answer (correct/incorrect). It still doesn't prevent the user from
> > defining multiple correct answers.
>
> Then something is wrong in this design. It might be a good idea to
> spend some time with a good book on ERD design principles and make sure
> that you understand supertypes subtypes and relational database
> implementation dependencies.
>
> To me, the whole concept of a "correct answer" is troublesome. There
> may be questions and answers but a "correct answer?". Perhaps you need
> to introduce the concept of a test_item which might include enough
> relevant context to postulate a correct answer given a specific
> scenario.
>
> What's the shortest distance between 2 points? Is it a straight line
> or a curve in space? Depends on if you are talking Euclidean or
> non-Euclidean.
>
>
> >
> > > or break the subtypes into multiple tables.
> >
> > Which is sort of like adding a correct_answer_number column to the
> > questions table.
>
> Not at all similar. Supporting subtypes involves 2 alternatives which
> are mutually exclusive.
hpuxrac - I'm not clear on why you thinking there's a design issue
here.
You might well be right, maybe you're not explaining yourself properly.
I certainly don't think that a FBI is a lipstick-pig scenario.
There are two issues.
Firstly whether there is a design issue over the
question-answer-correct answer *thing*.
Secondly, how to implement the unique constraint based on the
application usage - the update mechanism - not nice, compromising the
design but something that has to be done in reality all the time
Question
Answer
Question_Answer
Qid Aid CorrectYN
---- ----- ---------------
1 1 Y 1 2 N 2 1 N 2 2 Y
I'd question how often you'd want to reuse answers in the first place, but let's say you decided it was a good idea.
IF it worked - which it doesn't - the combination of function-based index and deferred unique constraint based on that would work for iterative updates done in a single transaction. What would work is some sort of trigger populating a column similar to the FBI index plus deferred unique constraint on that would also do the job.
Anyway I can't see how - with whatever design you come up with - that triggers either at db level (and it's a bit heavy to lump all triggers under the headline of inhibiting scalability) or form level are avoidable so there's not much left to think about..... Received on Thu Jan 04 2007 - 14:59:32 CST
 |
 |