Jeff Boyce wrote:
I see an advantage using a more multi-tier approach, in which the data is
just data, the user-interface is only that, and the business rules occupy a
middle-ground? What are your thoughts?

I think business rules would ideally be enforced throughout the
application.

Take fpr example two related data elements:
employee_earnings_start_date and employee_earnings_end_date. That
start_date occurs before end_date is a 'law of nature' type business
rule. I would enforce this rule in front end form, in any function that
acts on both dates, in the data layer and in the database. If I had a
component with a public/top level function that took the two dates as
arguments, then I would raise an error if the end_date parameter value
was before the start_date parameter value. I view the database as
'public', reflecting the fact it can be accessed from a variety of
sources (e.g. Excel is a popular tool for accessing Access data),
subject to permissions.

Some business rules are too complex to be implemented as SQL
constraints e.g. SQL isn't very good at involved mathematical
calculations. Some business rules can be implemented as SQL constraints
but they are impractical e.g. a OLTP application with a high volume or
INSERT/UPDATE actions that cannot wait for constraints to be checked;
it is not possible to defer constraints in Jet, therefore constraints
get checked at the end of every SQL statement (and even earlier if more
than one table is involved!) and it is unworkable to temporarily DROP
constraints in a multi-user environment.

Something from the aforementioned article
(http://www.dbazine.com/ofinterest/oi...les/celko27/): "There should
be one and only one trusted source for the business rules." I agree but
with the emphasis on the word 'trusted'. If this were taken literally
(i.e. a business rule should only be enforced in one place) it would be
contradicted by the later statement, "the overhead of going back and
forth between the application and the [trusted source] can kill a
system."

I think the constraints should be as 'close' to the data as possible
i.e. a column (field) validation rule then a row (record) validation
rule then a table-level constraint. If these are is not possible or are
impractical, my next preference would be to write SQL procedures to
control edits to data while removing permissions from the base tables.

In SQL Server, I'd consider VIEWs using WITH CHECK OPTION or INSTEAD OF
triggers. For those things too complex for SQL there is the possibility
of using CLR to write database constraints...but that's another SQL
product. If data constraints cannot be enforced on the current SQL
platform, maybe its time for a new SQL platform?

Jamie.

--