Re: Anybody for Automated Comparative Normal Form?

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Fri, 19 Nov 2004 08:50:37 -0500
Message-ID: <daa172-3a.ln1@pluto.downsfam.net>

Tony Andrews wrote:

> Kenneth Downs wrote:

>> This brings the problem back to what I have called "arbitrary

> constraints"

>> in recent weeks, but which should probably be called "comparative
>> constraints", because this is what they keep turning into.  It should

> be

>> possible to demonstrate that by using automation, one can reduce any
>> arbitrary constraint to a comparison constraint.

>
> You can demonstrate that it is so like this:
> 1) Take any desired integrity constraint of whatever complexity
> 2) Define a query to find violations of this constraint:
> SELECT ... FROM ... WHERE <violates constraint>
> 3) The constraint can be expressed as requiring that:
> SELECT COUNT(*) FROM (SELECT ... FROM ... WHERE <violates
> constraint>) = 0
> 4) Using triggers or some other mechanism (e.g. materialized view),
> automate that the result of the query in (2) is stored in some column C
> of some table T
> 5) Add a constraint to T: CHECK(C = 0)

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.

It is extremely important in this model not to confuse the *implementation* of these definitions with the *form* of these definitions. The value I have found in this form is that the definition of all items against real tables means:

1. "People Understand Tables Just Fine" I can show a spec to a civilian and discuss it intelligently.
2. The system is self-documenting, down to the most complex detail.
3. The system can be specified entirely in data, with no "helpful" programmers writing weird ad-hoc update scripts. This means I don't even have to write server-side coding guidelines, because no programmer is writing server-side code.
4. The system is provably and testably correct or incorrect. The insistence on small definitions built up through composition allows for easy automation of both unit and integration tests. See point 3. also, no "helpful" programmer can break this part.

Through composition we seek to eliminate <violates constraint> and reduce all arbitrary constraints to comparison constraints, where the individual elements themselves have been built up through the definition of simple operations on highly normalized tables.

-- 
Kenneth Downs
<?php echo "sig block.  i'm hip"; ?>

Received on Fri Nov 19 2004 - 07:50:37 CST