Declarative constraints in practical terms

From: dawn <dawnwolthuis_at_gmail.com>
Date: 21 Feb 2006 13:02:11 -0800
Message-ID: <1140555731.823399.160020_at_g44g2000cwa.googlegroups.com>



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):
  1. declarative constraints: SQL declarations enforced by a DBMS engine
  2. 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.
  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.
  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.
  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.

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? 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. --dawn Received on Tue Feb 21 2006 - 22:02:11 CET

Original text of this message