| 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
dombrooks wrote:
>
> 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
> =======
> ID Question
> --- -------------
> 1 What is the shortest distance between 2 points (Euclidean)?
> 2 What is the shortest distance between 2 points (non-Euclidean)?
>
> Answer
> =====
> ID Answer
> --- -----------
> 1 Straight line
> 2 Curve
>
>
> 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.
>
To be precise we're not reusing answers, answers primary key is both a question_number column and an answer_number column. The question_number column is a foreign key back to the question_number column in questions.
So answers really looks like
Answer
> 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.....
I appreciate your help! Received on Fri Jan 05 2007 - 09:27:41 CST
 |
 |