Re: constraining one correct answer but multiple invalid answers

From: <jcharpak_at_att.net>
Date: 5 Jan 2007 07:51:29 -0800
Message-ID: <1168012288.917412.119230@q40g2000cwq.googlegroups.com>

jcharpak_at_att.net wrote:
> 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
> =====
> QUESTION_ID ID Answer
> ------------------- -- -----------
> 1 1 Straight line
> 1 2 Curve
> 2 1 Straight line
> 2 2 Curve
> 2 3 Dolphins

>


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

whoops, I guess I have to join in the "followup to my own post" club.

Answers currently really looks like the following:

 Answer

---

 QUESTION_ID ID  Answer          Correct_yn
  -------------------  --   -----------          ----------------
  1                    1   Straight line   Y
  1                    2   Curve             N
  2                    1   Straight line    N
  2                    2   Curve             Y
  2                    3   Dolphins         N

Just to reiterate, what I am trying to accomplish is to prevent the user who is entering the question and answer data from choosing both "Curve" and "Dolphins" as both "correct" answers for question 2. Similarly, the user should not be allowed to define both "Straight line" and "curve" as correct answers for question one Received on Fri Jan 05 2007 - 09:51:29 CST