Re: Conditional Relationships ?

"Henry Craven" <GospodynNiemandt_at_nyet.net> wrote in message news:5xCQ9.20259$aV5.53648_at_news-server.bigpond.net.au...
> "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 - )

Your Business Model doesn't need to suffer if you make the changes at the
logical and physical levels only. The tables that your applications are
currently using become views that supply exactly the same information by
querying the restructured tables.

I have used this approach many times when trying to improve the model of one
of my early attempts at design. The main task is converting the existing
data as no, or very few, changes are needed to applications. As time permits, I refactor the applications to use the new logical model. This
allows an immediate benefit of data integrity and the longer term benefits
(certainly in 100% of my cases) of simpler application code and improved
performance without compromising the business model or rules.

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

OK, but you still seem to be storing calculated data in a base table as you
describe below. IMHO, again, I think that calculated data is fine in the
Business Model, permissable in the physical model, but a definite no-no in
the logical model.

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

I work in the real world, but can't imagine what disasters I could create
without understanding the academic theory behind relational databases, or
the opportunity to read academic input on lists like this.

A bigger distinction is that between taking a programmer's viewpoint and a
data-modeller's viewpoint. Conditional relationships (the origin of this
thread) are in the land of the program. What they are saying is that the
existence of a relationship is dependent upon the *value* of an attribute,
not the entity itself. An if-then-else construct is needed to evaluate every
row before knowing which relationship the entity uses. Whilst not a problem
with programs dealing with a row at a time, this is a performance killer for
reports. I speak from painful experience:-)

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

This is two different things in your business model and is the reason you
have raised the question of conditional relationships. The solutions suggested by several responders are attempting to make the Invoice a single
thing.

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

This is called a pro-forma invoice and is generally treated very differently
to a sales invoice.

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

I didn't want to move the discussion to a different schema, but as you have,
I can think of many reasons where it might be sound practice to separate the
genders in a logical model. For example, when modelling attributes that only
one gender can possess - bust size or ... well, whatever. Or relationships
that only one gender can posess such as mother-of or father-of. To put these
into one table (in the logical model at least) allows for impossible - or
highly unusual, please don't take me to task on the possibilites - situations to be incorrectly recorded. If your business model doesn't call
for these attributes or relationships, then a simple M/F attribute in one
table is fine. Likewise, if your business model doesn't call for invoices to
have different attributes or relationships a single table is fine. Yours,
however, does have different relationships.

I don't think its necessary to regard the table as the only possible manifestation of an entity from the business model. I don't see a problem
with there being an Invoices (or People) entity in the business model represented by a union of tables or a view in the logical model.

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

That really depends on whether they have different attributes or relationships or 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)

As you explained higher up, you are invoicing the quoted amount, not the
order value. I mistakenly thought the order value was a calculated value
whereas it is in fact simply a attribute showing the amount quoted. I would
rename it in the Orders table.

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

If you change the schema of your database at any point in its useful life,
then you are -imho - modelling.

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

I am no Codd-spert, so I will leave this one alone.

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

Oooh! That hurt:-)

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

Now you have established the quoted amount as being a fundamental but optional attribute of Orders, I can imagine that the you have two types of
order - a quotation and one with actual items. This is ideal territory for
the super-sub entity solution also mentioned on this discussion in the other
newsgroup.

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

Any RI problems can always be solved by using procedural triggers.That's the
seductive beauty of procedural code. On that basis why not just put everything into one big table with attributes defining what each row is. The Orders and Invoices tables become one big
table with a flag saying whether its an order or an invoice. No need to
bother with the messy relational stuff then - just sort it out with procedural code.
Your newly created OrderInvoice table then has a non-mandatory one-to-many
relationship with OrderItems. Simplicity in the extreme. (Note to experts: I am joking. On the other hand, I do know a real world
database company that did this with people and organisations with disasterous and possibly litigious results)

> Thanks for the input.

My pleasure. The main reason for taking the time to be involved in the discussion is for *me* to fully understand the problem and practice how I
should tackle similar problems in my own real world. Pure selfishness. Suffice to say, a conditional relationships may exist in my business models,
but they will certainly be nowhere near my logical model. That is my choice - just as what you do with your real world is yours.

I have to go back to the real world and some work tomorrow, so that's all from me. I guess I am the newsgroup equivalent of a Sunday driver!

Regards, and I wish you with all sincerity the best of outcomes with your
chosen solution.

Neil Received on Sun Jan 05 2003 - 19:35:49 CET