[AccessD] Cascade-delete

Gustav,

Two points:

>Further, I tell my clients that my responsibility is off if they deal with
the backend directly; never had a problem with that.

Your users actually ADMIT when they screw up?  I wish I had such users.
Mine say "No waaaaay I deleted that record.  It just disappeared!  The
DATABASE is losing records!".

>which is where a decent backup is pulled from the shelf.

With a database, unlike an office install, you can't just restore a backup.
If the missing data was deleted a month ago, you have to FIND the data.  If
you just restore the database from a month ago you delete all data entered
since.

Furthermore, this doesn't mean just going way back to where you are sure it
exists and retrieving a copy.  You *must* find the very last point that it
existed in the database, or you risk missing any updates a user may have
applied that day.

Once you have found that last instance of the data, you must then install
the BE to a different directory, build queries to append *just the missing
data* back in to the database, in the correct order to allow referential
integrity to do it's job.

Now, imagine that users have been deleting data "occasionally" over the last
month.  You don't know the data is missing until the VP of Accounting runs
his balancing act and discovers that the numbers don't match any more.  You
are now faced with trying to recover pieces deleted one at a time, over a
month's time.  And the VP of accounting (and the IRS) does not want to hear
"just ignore the errors".

This "restore the backup" can easily be many many hours (days / weeks) of
work.  It can cost so much money (your time) that they decide to just fire
you and get someone who knows what they are doing, to fix your f***up and
prevent such errors to begin with in the future.

Please don't allow our list members to believe that they can "just restore a
backup" to fix what they screwed up by not doing things correctly.

<SOAPBOX ON>
Cascade deletes is a powerful tool.  You wouldn't put a Mack truck in the
hands of a teenager with his first car.  Cascade delete does NOT belong in
the hands of a user you hired last week to enter forms in your database.
Nor in the hands of the "Southeast Sales Executive" down the hall building
queries to analyze his sales.

If you have it on, TURN IT OFF.  Build queries to delete records for your
users.  Be willing to do your job right, or look for another line of work.
<SOAPBOX OFF>

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Sunday, February 02, 2003 6:27 AM
To: John W. Colby
Subject: Re: [AccessD] Cascade-delete (was: Estimating Help)


Hi John

> Some of us are just luckier than others I guess.  Never had a user rip the
> guts out of your database by trying to delete a client that they "weren't
> doing business with" at the moment.

If you had enforced referential integrity between the client table and
the child tables of that with cascade-delete set to off, the user would
only have deleted the true "dead" clients.

You know that - at least now I'm sure - so what is your point?

Further, I tell my clients that my responsibility is off if they deal
with the backend directly; never had a problem with that.

And, by it's nature, should this happen that you or I (the developer)
hadn't set up RI right and the user did something like this by
mistake, it wouldn't be much different from any other possible damage
to the database which is where a decent backup is pulled from the
shelf.

I guess your intention is to warn the less experienced developer from
applying RI with cascade-delete set to on. This warning is right -
actually it is not easy to quickly browse the RI settings of your
database schema and check the settings from within Access. But at the
time when you feel confident with database schemas and RI, I feel it's
a very nice and strong tool.

/gustav

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com