Re: Conditional Relationships ?

Happy New Year!

Thanks for the full post and constraint SQL. I get the approach.

However, its a shame you can't remove any order item stuff (e.g. delivery) from the Order entity and make the Order Items entity a complete account of the order as, I believe, Steve Kass suggested, for it is this part of your logical model that, imho, is causing all the grief.

At a wild guess, it looks like the Orders entity originally included everything, then someone decided it would be a good idea to track line items individually. This should have converted into to just the reference order info (account no, date etc) in the Orders entity and dynamic items like lines and delivery charges being moved to Order Items. As it is, you have bits of the order value scattered over two entities.

At the moment you have one entity - Invoice - that is actually attempting to be two different ones with very different functionality - a full order invoice and a part order invoice - each having a relationship with one entity that excludes the other from having a relationship with a related row in another entity. By this, I mean that a full order invoice cannot exist for an order where a part order invoice has already been raised, and vice versa.(I conclude that Invoices must be two entities because they have different attributes - Orders and OrderItems expressed as mutually exclusive relationships - and therefore cannot be the same thing)

You also have both Order and Order Item entities involved with invoices. It could be argued that the Invoices-Orders relationship is redundant as it should be calculated from a complete list of Order Items if they were available (Steve Kass again).

I would look into removing these above anomalies from the model by putting all invoicable elements of an order into the OrderItems entity allowing an Invoice to have only one relationship with an Order - via the OrderItems entity. It would then be a very simple model:

CREATE TABLE Orders(
Order VARCHAR(12) NOT NULL PRIMARY KEY);

CREATE TABLE Invoices(
Invoice VARCHAR(12) NOT NULL PRIMARY KEY);

CREATE TABLE OrderItems(
OrderItem VARCHAR(12) NOT NULL,
Order VARCHAR(12) NOT NULL,
Invoice VARCHAR(12),

FOREIGN KEY (Invoice) REFERENCES Invoices (Invoice),
FOREIGN KEY (Order) REFERENCES Orders (Order),
PRIMARY KEY (OrderItem,Order));

Alternatively, if the number of NULLS in the OrderItems.Invoice field is a concern, then the intersection table mentioned before becomes a viable table as its referential integrity can now be enforced without resort to complex triggers:

CREATE TABLE OrderItems(
OrderItem VARCHAR(12) NOT NULL,
Order VARCHAR(12) NOT NULL,
FOREIGN KEY (Order) REFERENCES Orders (Order), PRIMARY KEY (OrderItem,Order));

CREATE TABLE OrderItems_Invoice(
Invoice VARCHAR(12) NOT NULL,
Order VARCHAR(12) NOT NULL,
OrderItem VARCHAR(12) NOT NULL,

FOREIGN KEY (Invoice) REFERENCES Invoices (Invoice),
FOREIGN KEY (Order,OrderItem) REFERENCES OrderItems (Order,OrderItem),
PRIMARY KEY (Invoice,Order,OrderItem));

Regards

Neil
PS As I know I am no relational expert, feel free to totally disregard the above as the New Year's Day-befuddled ramblings of a keen but naive novice:-)

"Henry Craven" <GospodynNiemandt_at_nyet.net> wrote in message news:<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 - 13:37:30 CET