Re: SQL Constraints (was "losing out 'cuz 'o grammar")

[Charles Reitzel <creitzel@mediaone.net>]

On Wed, 31 Jan 2001 19:37:56 Ken North wrote:
>> > Rules, due to their invocation at a points in time, 
>> > make inroads into the "route" (e.g. SQL triggers).  
>> > Declarative rules *use* grammar for their
>> > definition and depend on "grammar valid" data on 
>> > which to operate.
>> > 
>> > SQL didn't get basic triggers until 2.0.
>[snip
>> > Heck, most people never use triggers.  They just put it
>> > somewhere in the application code.
>>
>> Well ... I'd say that 'constraints' are 'closer'  than 
>> 'triggers'.

I use triggers. Most people don't, tho.  As a consultant, many is the time I
have introduced NOT NULL into a database schema.  Paul is probably right in
implying that Constraints are the standard part.  I haven't read the RFC, I
just work w/ whatever Oracle or Sybase give me ;~)

To continue Rick J.'s analogy, constraints describe facts about committed
data, are time-independent and, therefore, are part of the "map" and not the
"route".

>In the SQL world, there is often a division of labor 
>between database designer and application programmer. 
>The designer is the one who creates schemas, expressing
>constraints and specifying triggers. Skilled designers 
>look to declarative rules and constraints first, and then 
>use triggers if constraints are insufficient for what 
>needs to be done.

Well put.  Primary/Foreign key, unique key, non-null constraints are all
basic, portable(!) and do not incur undue "interference" w/ application
logic.  Sometimes it is necessary to denormalize for performance, and
database triggers can mitigate the associated "update anomolies", I think
they are called.  In essence, we are still in the static view of the data,
however.

>Using application code to enforce rules, instead of 
>declaring constraints that are managed by the container 
>(database), leads to versioning problems.  With 
>container-managed constraints, the rules are applied 
>uniformly across all clients connecting to the container. 
>With rules coded in application objects, you have to 
>co-ordinate application updates so the rules are 
>consistent from one program to the next. Different 
>programming languages, different namespaces, different 
>versions of classlibs -- it can be nasty trying to stay 
>in sync.

True enough.  But you can go too far the other way. 

What I try to avoid is writing full procedural programs in proprietary
languages.  In an environment where portability is not a concern (in house
vs. commercial) this is ok and you see a great deal of the application being
written as stored procedures.  To the extent this represents separation of
presentation from business rules, this is a good thing.  

When you split the source base into parts across two (or more) development
groups, coordination can get tricky.  The UI folks end up having to write
tricky routines to work around the particular, er, semantics of stored
procedures that are out of sync.  You also end up having endless variations
of similar routines, each called in one place.  You also can end up w/ a lot
of dead code because it isn't always easy to cross-reference the calls.

Your description of the problem is right on.  IMO, the solution is decent
engineering management practices and not technology, per se.  Ultimately, it
comes down defining a decent programmatic interface for that system and
sticking to it.

>In the XML world, we can now exploit types and constraints, 
>but one question is whether developers will learn from the 
>SQL experience, i.e., declarative, container-managed logic
>solves some of the proliferation/fragmentation problems 
>that occur with ad hoc application logic.

Schemas and database stored procedures do help.  But they (or anyplace the
logic happens to be kept) are just as susceptible to ad hoc updates.  Again,
it comes down to engineering management.  

The weight of the world does not rest on the schema language designers'
shoulders.  Somebody still has to write the app.

take it easy,
Charles Reitzel