Re: Conditional Relationships ?

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

I am not clever enough to offer a solution though:-((

"Henry Craven" <GospodynNiemandt_at_nyet.net> wrote in message news:<qgKP9.8072$aV5.20299_at_news-server.bigpond.net.au>...
> 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" <skass_at_drew.edu> wrote in message
> news:aum48k$5e$1_at_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
Received on Tue Dec 31 2002 - 16:40:13 CET