This is a partial response to Eric's previous e-mail.

Where should constraints be specified?

Take a services framework that includes an interface for "web services" such
as SOAP-wrapped XML that comes in from some place external to our system and
also a user interface for people doing data entry.  This is an appropriate
design for Orders, for example.

In this case, we could break down the high level services as follows:

Web Service "entry service"
GUI

(those two being the analogous interfaces to what used to be called "batch"
and "interactive" computing)
These services might then both be clients to the same

Data Validation Service

This would apply all business rules and constraints related to the data
entered through one means or the other.  Ignoring workflow types of services
for now, we would add in at least

Data CRUD Services to Create, Read, Update, and/or Delete data stored in a
database

When we use DBMS's, the Data Validation Service and CRUD Services are
tightly coupled.  This gives an advantage of a "lock box" (as Al Gore might
say) for our data. It is not only impossible for a user to write data with
an improper type, for example, it is also impossible for an IT professional
without DBA access to do so.

However, if you look at software applications, it is common, even required,
to have a GUI employ such business rules, for example, in giving users a
list of valid codes from which to choose, or even sometimes in finishing the
data entry based on prior entries (as with Excel) or based on valid entries.
Additionally, once a set of data is passed to the Validation Service, prior
to heading to the CRUD Services (whether Validation and CRUD are tightly or
loosely coupled), these business rules are employed again.

Isn't it rather common, for example, for there to be a drop down box locking
in the user to a valid set of choices, with the specification for this drop
down box in one place/language and then these choices again validated when
changes are made to the database?  We are then taking our industry's data
(specs/code) and duplicating it, thus making it difficult to maintain while
keeping integrity in this data.

My point is that with the way that software is written today, quite often
data constraints are coded in multiple places, even multiple languages.
There are apparently many people as well as exceptional conditions that
might prompt multiple locks on a door, but this need not be the norm, I
would think.

If the front-end services are written in Java, for example, then I would
prefer that the GUI and the Validation Services make use of the exact same
constraint specs and logic to validate the data against the constraints.
Even if we tap into the specifications that are placed in a DBMS, taking the
logic of the DBMS that is used to apply the constraints and using that up
front in the GUI isn't happening in current software applications, in
general, for at least a few reasons I can think of:

1) The DBMS's logic is applied when CRUD services are launched, while the
logic in the GUI is not ready to launch such services, so there is not an
easy means (or perhaps any means) for the application software developer to
tap into the DBMS's logic.  This is true for type constraints as well as ref
int constraints where the GUI might need to come back to a user and ask the
equivalent of  "Are you sure you want to delete this folder and all of its
contents?" when a user requests that a parent be removed, for example.

2) The application developer cannot easily see the specs and logic applied
by the DBMS to know whether that is the same as what they believe they need
for this application.  So they do not even make the decision on whether the
constraints they are applying are tighter in this case than what the
database spec permits.  It is easier for the application developer to treat
the DBMS constraints as a black box where they try not to bump into the
limits, but they don't think of them as related.

3) Reading a database to get constraints when trying to respond quickly to
the end-user is sometimes considered by the application developer to be out
of the question compared to employing perhaps byte code that runs in an app
server JVM.

One solid design could be to have all of the constraints specified and the
validations applied in a Validation Service that is able to reuse the same
constraint specifications that the GUI uses and that the CRUD routines use,
all specified outside of the DBMS.  We could code and run all such services
in a JVM on an app server (rather than the database server).  In this case,
employing a file system that might not be considered a DBMS would be
acceptable provided that we still have the door locked.  This can be done by
permitting CRUD services on the stored data only via the designated
services.

The constraint specs and logic need to be moved right up to the front of the
application -- to the GUI.  I know some have advocated for the database
language to deliver the applications, but that tends to tightly couple way
too much of the code, rather than employing a good design pattern, such as a
services pattern to the application.

I'm just thinking out loud and I'm sure something I wrote was not brilliant,
but help me out here -- isn't this a real issue in application software
development written today?  I'm suggesting something rather radical --
decouple the constraint specification and validation logic from the
database.  Does anyone agree?

Cheers!  --dawn