Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!51g2000cwl.googlegroups.com!not-for-mail From: "hpuxrac" Newsgroups: comp.databases.oracle.server Subject: Re: constraining one correct answer but multiple invalid answers Date: 4 Jan 2007 04:53:27 -0800 Organization: http://groups.google.com Lines: 26 Message-ID: <1167915207.692806.186380@51g2000cwl.googlegroups.com> References: <1167861324.059314.237270@q40g2000cwq.googlegroups.com> <1167909856.686315.299890@42g2000cwt.googlegroups.com> NNTP-Posting-Host: 208.44.49.5 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1167915213 3073 127.0.0.1 (4 Jan 2007 12:53:33 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 4 Jan 2007 12:53:33 +0000 (UTC) In-Reply-To: <1167909856.686315.299890@42g2000cwt.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.0 PROXY Complaints-To: groups-abuse@google.com Injection-Info: 51g2000cwl.googlegroups.com; posting-host=208.44.49.5; posting-account=Dz_3bQ0AAAC5LZNB2NPEJz1sl_a8qcDY Xref: news.f.de.plusline.net comp.databases.oracle.server:191684 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 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.