Re: Conditional Relationships ?

"Neil Burnett" <neil_at_efc.be> wrote in message news:ea0243b5.0301010437.4c73c3a3_at_posting.google.com...
> 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.

As I've stated previously, I'll leave it up to the Gods to force reality to fit some Contrived Model based on what reality "Should Be". For my part, I just take what *is* and use what resources are available to deal with it. ( - Just shoveling shit in the Agean Stables - )

>
> 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.

Not in the least, again, as stated the business rules are complex, as is the real world model of the business, and the Handling of Orders vis-a-vis Invoices via differening methods is both logical and necessary.

• Perhaps this is why developers with real world experience are more in demand and command greater remuneration that University Graduates who know the theory, can sprout the models, but do not really understand the real world - Theory and models are just that. they need to be moulded to fit the business needs, not (necessarily) the other way around. -

>
> 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)

Not realy, An invoice is an invoice. it serves to do exactly what it purports to do. Bill for part or all of an order.

Take Invoices:
There are three possible scenarios:
The invoice is based on the Total Order Sales Price. The invoice is based on the Sum of the Order Items and Anciliary Charges. The invoice is based on *some of* the Order Items and Anciliary Charges

Take Orders.
An order can be raised, And Billed where the Order Items are as yet Unknown (No order items) and not delivered. - this is - ( Quoted Price ) .. at some time line items will be added that may ( or may not ) add up to the quoted price. ( lesser OR greater )

OR, ...An order can be raised and Line items Added, and these are then Billed based on the Value of the Line Items. (Shipped)

Whilst different *types* of orders requiring different Invoicing Relationships
they are not different entities, and it would be as pointless as putting contacts
into separate tables based on gender.

And should I then Create *other* Order and Order_Item tables for each of the Other Invoicing Scenarios ??? ...I think not.

>
> 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).

*should* be calculated from a complete list of Order Items ( not exactly) *if* they were available (exactly)

>
> 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:

But I'm NOT modeling -
I'm dealing with the real World Business Practices and Data.in a Live Database / Application.

Even Codd discovered that he had to extend the *Model* beyond third normal form in the *real* world.

Thankfuly SQL server allows me sufficient tools and flexibility to be able to deal with it. ( With help from the experts. )

>
> 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:-)

As explined, Order_Items can be null, so the above would not fit.

In any event, even on a purely thoretical level I was interested in how one might handle the RI. ( the database Modeling theory I already have down pat.)

Thanks for the input.

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: 18-Dec-2002