Re: Conditional Relationships ?

From: Henry Craven <GospodynNiemandt_at_nyet.net>
Date: Sun, 29 Dec 2002 22:00:54 GMT
Message-ID: <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 Sun Dec 29 2002 - 23:00:54 CET

Original text of this message