Re: constraining one correct answer but multiple invalid answers

jcharpak_at_att.net wrote:
> Pretend there's a table called questions. It has two columns,
> question_number, and question_text. Question_number is the primary key
> and is artificial. Next, pretend there's a table called answers. It
> contains four columns: question_number (a foreign key to questions),
> answer_number, answer_text, and correct_yn which will contain either a
> 'Y' or 'N'. Answer_number will not be unique across all questions
> although it will be unique within a question. The primary key of
> answers is a composite key of question_number and answer_number. You
> can pretend there's a check constraint on correct_yn if you'd like.
>
> Each question can have any number of incorrect answers, but only one
> correct answer.
>
> I'm trying to come up with some kind of constraint or trigger that will
> enforce this restriction. One solution is to add a
> correct_answer_number column to questions and create a composite
> foreign key back to answers. For various reasons this isn't as easy as
> I've made it sound.
>
> Another possibility is to create a pre-insert/update/delete row level
> trigger to store which questions had their answers
> inserted/updated/deleted in a pl/sql table and then enforce the
> "constraint" in a statement level trigger. The problem here is that if
> the correct_yn column is updated one row at a time, statement level
> triggers become, in essence, row level triggers. This makes the order
> of update important. If I set a preexisting "y" to an "n" and then
> subsequently change a different "n" to a "y" there is no problem.
> However if a "y" already exists but I first change an "n" to a "y" and
> then change the first "y" to an "n", even though at the end of the
> transaction the data is valid, it isn't valid at the statement level.
> Considering the data is being entered in an Oracle Forms master-detail
> layout, the chances of users checking and unchecking the correct_yn
> fields of multiple answers in one transaction are quite high.
>
> I suppose I could loop though the records in a pre-commit trigger in
> the form and enforce the validation that way. However, that means the
> validation is at the application level and not at the database level.
>
> Does anyone have opinions on what would the best route to take? Are
> there alternatives I haven't considered?

If I understand your design, you have an entity answer.

The answer entity is a supertype. There are 2 subtypes of answer a correct answer and incorrect answer.

To support this type of design you have 2 choices. You can use a single table for the supertype and include some column "type_answer" that identifies for each row which subtype it belongs to. ( Then optionally other columns for any additional attributes any subtype has that doesn't belong in the supertype ).

If you don't use a single table then you have to create multiple tables for each subtype.

This is just basic ERD design 101 stuff.

There are advantages and disadvantages to either of these choices. Received on Wed Jan 03 2007 - 16:26:25 CST