Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!s34g2000cwa.googlegroups.com!not-for-mail From: "dombrooks" Newsgroups: comp.databases.oracle.server Subject: Re: constraining one correct answer but multiple invalid answers Date: 4 Jan 2007 12:59:32 -0800 Organization: http://groups.google.com Lines: 105 Message-ID: <1167944372.744703.215170@s34g2000cwa.googlegroups.com> References: <1167861324.059314.237270@q40g2000cwq.googlegroups.com> <1167909856.686315.299890@42g2000cwt.googlegroups.com> <1167915207.692806.186380@51g2000cwl.googlegroups.com> <1167928166.739424.40130@v33g2000cwv.googlegroups.com> <1167933687.220573.81950@q40g2000cwq.googlegroups.com> NNTP-Posting-Host: 81.159.163.28 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1167944379 8202 127.0.0.1 (4 Jan 2007 20:59:39 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 4 Jan 2007 20:59:39 +0000 (UTC) In-Reply-To: <1167933687.220573.81950@q40g2000cwq.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; YPC 3.2.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322; Media Center PC 4.0),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: s34g2000cwa.googlegroups.com; posting-host=81.159.163.28; posting-account=saJ8dA0AAAB2Z1YDokHBJhGGzUvkTfaA Xref: news.f.de.plusline.net comp.databases.oracle.server:191739 hpuxrac wrote: > jcharpak@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. 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.....