Re: Conditional Relationships ?

From: Henry Craven <GospodynNiemandt_at_nyet.net>
Date: Wed, 01 Jan 2003 02:44:15 GMT
Message-ID: <3CsQ9.17726$aV5.45892_at_news-server.bigpond.net.au>

perhaps I should have Posted Erland's solution in full.

Quote:

---

If there are two different columns for Order and Order_Item, then this table-level constraint would do the trick:

   CONSTRAINT ckt_invint_orderref CHECK (

       Order IS NULL AND Order_item IS NOT NULL OR
       Order IS NOT NULL AND Order_item IS NULL)

And the regular foreign-key constraints to the Orders and Order_items table.

---

While at i'ts simplest this does not appear to prevent separate Invoices being raised for the Order alone , and then for Order items of that order if you use an Order_ID and the Order_Items_ID - as you point out - , the method can be applied by using additional Checks

So:
CONSTRAINT ckt_invint_orderref CHECK (

• Invoice is based on the full order
• and has not been previously invoiced

( Invoice.Order_ID IS NOT NULL
AND
  Invoice.Order_Items_ID IS NULL
AND
  Invoice.Order_ID NOT IN
 ( SELECT invoice.order_id
   FROM invoice
   WHERE invoice.cancelled = 0 ))

 OR

• Invoice is based upon Partial Order Line Items ONLY
• and the Order was not previously invoiced as a SOLE Invoice

( Invoice.Order_ID IS NOT NULL
AND
  Invoice.Order_Items_ID IS NOT NULL
AND
  Invoice.Order_ID NOT IN
 ( SELECT invoice.order_id
   FROM invoice
   WHERE invoice.cancelled = 0

      AND invoice.order_items_id IS NULL ))

)

Note: this is Air-Code and I haven't checked this yet.

H.

"Neil Burnett" <neil_at_efc.be> wrote in message news:ea0243b5.0212310740.20f1e76d_at_posting.google.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" <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
> >
> > Thanks again
> > H.

---
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 Wed Jan 01 2003 - 03:44:15 CET