Re: Arbitrary Constraints

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 26 Oct 2004 19:38:20 -0400
Message-ID: <g5nmlc.ge7.ln_at_mercury.downsfam.net>


Dawn M. Wolthuis wrote:

>>
>> An arbitrary constraint is one that appears, from the point of view of
>> normal forms, to pop up out of nowhere after the fact. Consider that you
>> put all of this time into normalizing tables, and then somebody says, "Oh
>> yes, each customer has a credit limit, and open orders cannot exceed the
>> credit limit." This appears as an arbitrary relationship between columns
>> that cannot be dealt with in normalizing them. A filter comes into play
>> one way or another.

> 
> Are "constraints" the same thing as "business rules"?  Have do constraints
> relate to application logic?  If you remove all such constraints from
> "application software" and make these constraints available to both the
> database and the UI, what would be left for the application software to
> handle?  This is not a question for which I have an answer, but am
> definitely interested.

A constraint does not necessarily map 1:1 to a business rule, but it might happen sometimes. Basically I like to throw all the business rules as stated by the users into a bucket and try to fish out, in order:

  1. Ruthlessly normalized tables
  2. Automation built upon Step 1
  3. Arbitrary constraints built upon Step 2

I say "ruthlessly" normalized to stress the fact that most systems are not normalized in even the simplest ways. Look for the primary keys and the foreign keys, it takes care of much more than people think.

Next is automation. A complete description will have to wait, but there is column automation, extended_price = price * qty, and table automation, such as a cascade to a history or journal table. (There is an implicit constraint that direct assignments to automated data are prohibited).

I have never met a problem I could not solve with the above two except for the "arbitrary" constraints. The customer says, "We strictly enforce credit limits." This translates into a relationship between two numbers that exist at the customer level, the customer's credit limit and the customer's combined open orders and invoices. I call this "arbitrary" because it cannot be derived out of the structure of the tables.

The big question is, is there some structure beneath these "arbitrary" constraints that is eluding my vision?

In my world, the database is responsible for enforcing all business rules, including the automation. It is often a service the user to have the client also know about rules, but the client is there for I/O, sometimes with humans, sometimes with other machines.

> 

>> What I would like to do now is determine if the most commonly encountered
>> "must have" constraints out there in fact have commonalities to them so
>> that they can be handled with a finite number of types, macros, or
>> utilities, and so their apparent arbitrariness can be categorized and
>> "normalized" as it were.
>
> A good idea to at least have some design patterns or best practices.

In this case it is for a tool, so the answer will determine what the tool can do. If no structure can be found, I have to punt and allow generalized constraints and then just keep developing and see what emerges from practice.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Wed Oct 27 2004 - 01:38:20 CEST

Original text of this message