Re: Declarative constraints in practical terms

mAsterdam wrote:
> dawn wrote:
> > This is related to a recent exchange related to declarative
> > constraints. I know I've worked on it before, but I am still perplexed
> > on this one. Here are two common options for handling constraints
> > (business rules):
> >
> > a) declarative constraints: SQL declarations enforced by a DBMS engine
> > b) metadata + code: metadata specifications plus custom-build
> > validation/constraint functions or services written in a general
> > purpose language
> >
> > Some differences I can see are
> >
> > 1) Declarative constraints are coded in the SQL sublanguage while with
> > metadata + code, the metadata declarations are simpler, being in the
> > form of name=value pairs (e.g. maxValue=100, valTable=MyTable) but
> > proprietary code is written.
> >
> > 2) The functions combining the data and constraints in the declarative
> > case are in well-tested (over time, at least) engines written by a
> > database vendor, otherwise often less generic, more specific, functions
> > written by whomever whatever team is writing validation routines for
> > software services. .
> >
> > 3) The functions for validation of data can be used anywhere within an
> > application if packaged outside of the dbms, otherwise most of them
> > need to be coded at least twice -- once for the dbms and once for use
> > in a UI or web service.
>
> Validation within the scope of the database run time serves to protects
> the data. Validation outside serves to support the datacapture process.

If an organization writes database services for all applications to call to access the database, that would similarly provide database protection. I'll grant that if someone is trying to do harm, there are perhaps different ways of violating such security.

> This is another difference than decalarative vs. procedural.

In either case, the organization can enforce use of database services. The approach to doing so is different.

> > 4) The declarative constraints use the RM, so they work with the
> > restrictions of the RM, including 1NF. Because they are written in
> > SQL, they use a 3VL whereas using metadata + code, 2VL and non-1NF are
> > the norm.
>
> Neither declarativeness (lisp, prolog) nor the use of
> constraints in automata originated (again, prolog) in the RM.
>
> > 5) In the case of declarative constraints, they are necessarily
> > employed by any application writing to the database. In the case of
> > metadata + code, each organization must determine whether and how to
> > technically enforce business rules for all applications or enforce them
> > through standards and QA approaches.
>
> One programmer error can ruin your data. No QA can prevent that.

One (dba) programmer can damage data in either case. In the one case more damage can be done by those writing the database services while in the other by those writing database constraints. I don't know if one of these is harder to get right, harder to debug, or harder to include test cases for in regression testing. Do sites often put their SQL constraint test cases into the same QA test harness as their OO or procedural code?

> > 6) If there are local constraints (constraints for one application and
> > not another) then these are either declared in SQL as local constraints
> > (something I've read about, but never used) or, often, coded only in
> > the individual applications using metadata + code. So, local
> > constraints are often handled the same but in one case local and global
> > constraints are coded in the same language, typically by the same team.
> >
> > I lean toward not duplicating constraints, coding and maintaining them
> > in multiple places and languages, but I understand that someone else
> > might choose the other strategy. Whatever choice, it doesn't look
> > obvious to me that declarative constraints are better as I gather it
> > appears to many others.
>
> I feel that there are two issues mixed here:
> decalative vs. procedural and
> inside vs. outside the scope of the dbms.

Yes, I'll buy that. They are related, however.

> > This relates to the fact that the RM is not sufficient for writing
> > software (as mentioned in my current blog entry that I'll again boldly
> > advertise as being at http://www.tincat-group.com/mewsings ) and coding
> > constraints using the RM doesn't seem like it can get you all the way
> > there. So if you go that route, you end up duplicating your work, both
> > up front and for all maintenance.
> >
> > Is there a way to get the best of both worlds on this one?
>
> Not that I know of - but that is quite annoying.
> There realy should be an easy way to reuse validations
> for another purpose, no?

Yes. Constraints are often specified in a language used by the DBMS and not used for the rest of the application. Additionally, the engine that interprets these constraints is needed for them to be applied consistently.

> > This issue
> > is really bothering me, so thanks in advance for any help you can give
> > me to gain a better understanding and apologies for bringing it up
> > again.
>
> Until it is clear there is no reason not to bring it up again and again.

Thanks for understanding. Cheers! --dawn Received on Wed Feb 22 2006 - 02:43:56 CET