Re: Anybody for Automated Comparative Normal Form?
Date: Fri, 19 Nov 2004 18:15:18 GMT
Message-Id: <9rp172-kes.ln1_at_pluto.downsfam.net>
Tony Andrews wrote:
>> This is correct but incomplete. The goal actually is to reduce the
> element
>> <violates constraint> to something that can be specified without
> using
>> code. Put another way, you are not allowed to automate a column by >> defining a SQL statement or expression for it. The only tools
> available
>> are the fundamental operations of addition, multiplication and so
> forth
>> (also functions like coalesce, sin, etc.), aggregation functions, and
> pulls
>> (where you fetch price from the items table). >> >> Values of arbitrary complexity can be generated through composition. >> Extended_price = price * qty, and line_discount = extended_price * >> line_pct, and line_final = extended_price - line_discount. Then the
> order
>> header table has Line_total = SUM(details.line_final). Then the
> customers
>> table has Orders_total = SUM(orders.order_final) and Invoice_total = >> SUM(invoices.invoice_final), and Total_exposure = Orders_total + >> invoice_total. Now you define a constraint that total_exposure may
> not
>> exceed credit_limit.
>
> Ah, well I am not at all sure that that goal is achievable (or
> desirable, but that is a matter of opinion!) You are stipulating a
> simplified subset of SQL: are joins allowed? If not, how do you get
^^^^^^^^^^^^^^^^
not quite. I am stipulating that you define the data without any SQL. What kind of SQL is generated is left undefined in the context of this conversation.
I'm not sure about your question, "are joins allowed?" They are allowed in views of course. See my other thread from yesterday on symmetric and asymmetric relationships.
> the SUM(orders.order_final) for the customers table - by following
> foreign key trails? If so, what do you do when there is more than one
> trail?
For automating aggregration, every case i have ever seen of trail ambiguity turns out to be the result of insufficient normalization. Two different links between an order header and order detail is definitely questionable, as is two links between order and customer.
The most unfixable are those where 2NF is regularly violated. If you cannot fix the 2NF violations, you are better off not using tools that really want a normalized database, or at least not for those particular tables.
>
> I would not want to dispense with the full set-processing and query
> optimising power of the DBMS in this manner.
Neither would I. That's why I only dispensed with the stuff that's hard to specify, document, code, debug, test, package, deliver, install, and maintain.
--
Kenneth Downs
java.lang.String.tcpip.usenet.posting.response.sigblock.setSig("After
finally finding the right object abd method to set the sig block, I forgot
what I wanted to say!");
Received on Fri Nov 19 2004 - 19:15:18 CET