Re: constraining one correct answer but multiple invalid answers

dombrooks wrote:
>
> 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.

Probably not. After thinking about it a little more driving home, to me at least, I think what one might design would include questions, answers, tests, test_results, and answer_keys.

People who create tests change their minds about what the correct answer is/was. They also change their minds about which answers they will accept as correct. The rule that "only one is accepted as correct" doesn't work in the real world. Test gives eventually accept multiple answers.

The idea of keeping which answer is correct at the answer level doesn't seem realistic to me.

There should be some way built in of rescoring a test_results after the answer key is adjusted.

> I certainly don't think that a FBI is a lipstick-pig scenario.

It perhaps would be a workable alternative to a bad design but I think the design is going to lead to trouble. It seems simplistic at best to me.

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

What the OP presented doesn't have enough information for me to give any real opinion about how many potential design flaws might be present.

Solving stuff with technical tricky things like function based indexes and triggers is a tempting choice for many DBA's but I am not convinced at this point that the OP has a good handle on the overall design.

Just my opinion. Received on Thu Jan 04 2007 - 21:00:29 CST