constraining one correct answer but multiple invalid answers
jcha...@att.net
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?
hpuxrac
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.
hpuxrac
Sorry I should have added don't even consider hacking up some kind of
trigger based validation. That type of approach produces applications
that don't scale, don't maintain well, and is a place you just don't
want to be in.
Fix the design with the choices noted in my prior reply.
Have fun.
dombrooks
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.
dombrooks
RTQ - Read (and remember) the question
hpuxrac
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 or break the subtypes into multiple
tables. That's the only way to do it without kludging together
something that has inherent scalability limitations built into it.
jcha...@att.net
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.
hpuxrac
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.
dombrooks
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.
I certainly don't think that a FBI is a lipstick-pig scenario.
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.....
hpuxrac
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.
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.
hast...@hotmail.com
As I get it, your main problem is that the invariants do not hold
till commit, right ?
Did you consider adding to the question table some fields
describing whether and why the question is valid or not ?
Validation could be performed by the database and its result
stored in these fields. Client application could then either display
the question validity state while the user is editing, or check for
validity it at commit time, or even commit invalid questions and
let the user correct them later - and perhaps they will like that a
lot....
--- Raoul
jcha...@att.net
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!
jcha...@att.net
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