Re: constraining one correct answer but multiple invalid answers

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.

> or break the subtypes into multiple tables.

Which is sort of like adding a correct_answer_number column to the questions table. Received on Thu Jan 04 2007 - 10:29:26 CST