Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: neil@efc.be (Neil Burnett) Newsgroups: comp.databases.theory Subject: Re: Conditional Relationships ? Date: 31 Dec 2002 07:40:13 -0800 Organization: http://groups.google.com/ Lines: 112 Message-ID: References: NNTP-Posting-Host: 80.197.86.221 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1041349213 14099 127.0.0.1 (31 Dec 2002 15:40:13 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 31 Dec 2002 15:40:13 GMT Xref: newsfeed1.easynews.com comp.databases.theory:24209 X-Received-Date: Tue, 31 Dec 2002 10:39:50 MST (news.easynews.com) I can't see how this solves your problem. It is still possible to have an invoice for a full order existing as well as an invoice for order items off that order: Invoice Order OrderItem ------------------------ a1 o1 null a2 null oi1 OrderItem Order --------------- oi1 o1 I am not clever enough to offer a solution though:-(( "Henry Craven" wrote in message news:... > Thanks for the response Steve. > > The "Real World" reality of the business rules are a little more complicated > and simplest to say that the invoice totals vary depending upon whether > they are based on the Order, or sum of the Order_Items. Values. > > Needless to say, I have no chance of changing the corporate Method > to suite my "Ideal" data model. > > Erland Sommarskog in comp.databases.ms-sqlserver > > seems to hav hit on the simplest solution Allow nulls on the FKs > and appky a conditional ( either / or ) Rule > > ( got caught myself having to multi-post instead of cross post > - It happens ) > > Thanks again > H. > > > "Steve Kass" wrote in message > news:aum48k$5e$1@slb6.atl.mindspring.net... > > Henry, > > > > Maybe I'm missing something, but I would think if an > > invoice can cover just part of an order, then you just want to > > relate the invoices to the Order_Items table (which presumably > > is in turn related to the Orders table). > > > > You should be able to write procedures or triggers to make > > it easy to associate an invoice with an entire order (so the user > > doesn't have to specify the individual items), and you can also > > see whether an order is fully invoiced by searching for > > order items for that order that are not invoiced. > > > > I suspect I'm missing some reason why relating an invoice > > to all the items of an order (which I'm suggesting for a full-order > > invoice) is not the same as relating an invoice directly to an order, > > so let me know. > > > > [Apologies for sending separate replies to the two groups - for > > some unknown reason Mozilla just told me I can't send to two > > news servers at once, though it's hard to believe I've never done > > that successfully before...] > > > > Steve Kass > > Drew University > > > > > > Henry Craven wrote: > > > > >I'm curious as to how one would go about setting up > > >and enforcing a Conditional Relationship. > > > > > >An example would be : > > > > > >Orders Table > > >Order_Items table > > >Invoices Table > > >Invoices_Intersect Table > > > > > >The Invoices_Intersect has Foreign Keys for > > >Invoices > > >Orders > > >Order_Items > > > > > >The relationship needs to be enforced so that the Invoice > > >relates to EITHER the Order ( as a Whole ) 1 -2- 1 > > >OR the Order_Items ( Order part Shipped ) n -2- 1 > > >BUT NOT both. > > > > > >TIA > > >-- > > >Henry Craven > > > > > > > > > > > >--- > > >Outgoing mail is certified Virus Free. > > >Checked by AVG anti-virus system (http://www.grisoft.com). > > >Version: 6.0.431 / Virus Database: 242 - Release Date: 17-Dec-2002 > > > > > > > > > > > > > > > > > > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.431 / Virus Database: 242 - Release Date: 17-Dec-2002