Re: constraining one correct answer but multiple invalid answers

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. Received on Thu Jan 04 2007 - 12:01:27 CST