Dynamism and Database Referential Integrity

[Steve Holden]

"David Bolen" <db3l at fitlinxx.com> wrote...
> kzaragoza at mediaone.net (Kris J. Zaragoza) writes:
>
> > When you have multiple applications accessing one database, however,
> > you don't want a bug in one app causing all the others to fail because
> > of bad data.  Here is where the various features of modern relational
> > databases really start to shine.  Referential integrity along with
> > other types of constraints help keep data consistent.  Transactional
> > controls likewise ensure that applications only ever see properly
> > saved, consistent data.
>
> As not primarily a database person, whenever this comes up I always
> want to put the constraint logic (e.g., validation of data, handling
> erroneous data, and so on) off up in the application and not down in
> the database.  Having shared routines used by all users of the data
> ensures that all applications have the same protection.
>
You are clearly an amateur, since any professional knows that the database
goes at the top <wink>, with "business rules" underneath it then
"application logic" and "interface handling" (sometimed known as
"presentation logic"). This is the classic distributed-application task
partitioning that became popular in the 1990's as client/server took off.

I'd draw you a diagram, but I don't use fixed-pitch fonts to composemail, so
you wouldn't be able to make any sense of it if I did. Pretty much like the
writing, really <0.5 wink>.

> Of course, this is really just moving the behavior up or down the
> spectrum between database engine and application (and it's probably
> the same impulse I have to write simpler SQL and more application
> logic).
>
We'll get to this later. In client/server contexts the problem with simpler
SQL and more application logic is the increased stress this can place on the
network. Once wide-area links come into play (until we get to the promised
land of cheap and plentiful bandwidth) this can be significant, and
sometimes makes it worthwhile to spend time working out how to perform an
operation just in SQL.

> I see transactions slightly differently as constraints since I don't
> see any effective way to enforce transactions without support from the
> database engine as it is the only arbiter of what all the connections
> "see" at any moment in time.
>
Unless you have psychic client hardware <wink>.

> Is there a key differentiator between where you put the enforcement of
> such integrity rules - is it strictly performance (such as can be the
> case by letting the SQL do more of the work on behalf of the
> application)?

The main reasons why the integrity rules are maintained on the database is
probably more to do with systems engineering than anything else. If the
engine maintains the rules, when you change them you aren't faced with
rolling out new application logic to seven, or seven hundred, or seven
thousand, desktop client machines and synchronising the installation so that
everything switched over at once!

This is the same reason that the various thin-client proposals were made
(network PC, etc.) - reducing the complexity of system maintenance.
Microsoft's response to this perceived threat to their dominance on the
desktop was to emulate X Window (in conceptual rather than compatible ways)
with MTS, but nobody's perfect :-)

Obviously, performance can feature as mentioned above, but a large
corporation with the ability to charge its customers for its lack of common
sense could just overcome that by throwing money at the problem and buying
more power and bandwidth.

Once the rules *are* centralised you can simplify the task even further by
using triggers to automate the enforcement of semantic integrity in the
presence of clueless users with the ability to prod and poke at the data.
This does have the problem that you lock yourself in to a database vendor
until the 99 SQL standard is fully implemented (say ten years from now).
Personally my preferred solution is

    a) Eliminating the cluelessness, then if that fails
    b) Eliminating the clueless

Which is why I don't work in a large corporation, since they can't survive
without clueless people. Hence Oracle's fabulous popularity. I am *not*
claiming that all people who work in large corporations are clueless.

avoiding-the-pointy-haired-for-twenty-years-ly y'rs  - steve
--
http://www.holdenweb.com/