[AccessD] Missing records

Charlotte Foust cfoust at infostatsystems.com
Fri Feb 3 10:19:31 CST 2006


I have to agree with you, Gustav.  Cascading deletes are definitely not
for DIY users but properly implemented, they save a lot of code!

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, February 03, 2006 8:01 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Missing records


Hi John

Oh no, why are you beating this horse again?

Cascade delete is a very powerful and useful feature.
However - just like fire - implement it only when you know what you are
doing.

Your example with invoices is bad. You never delete an invoice, and if
you do (it might be a draft only) you will wish to delete all its
invoice lines too. If you don't, you will have orphaned invoice lines,
or you will have to bother the user with deleting line by line until the
draft is empty and can be deleted.

You may program your own routines to be fired at a click of the
supervisor's button, but doing so is somewhat similar to building
unbound forms in Access.

As I wrote last time: You know all about this - normalization, relations
and so on - so why be so scared?

/gustav

>>> jwcolby at ColbyConsulting.com 03-02-2006 16:33:46 >>>
John,

The first thing to do is examine the relationships for the tables
missing records.  If "cascade delete" is turned on (a NO-NO in my humble
opinion) then deleting a single record (such as a customer) will (after
a warning of
course) delete ALL child/grandchild/...greatgreatgreat records.  

Let's say that a client has invoices and invoice line items.  Yep, all
gone. Repair service calls / line items?  Yep, all gone.  Payments?
Yep, all gone.

Cascade delete of a single (for example) customer record WILL delete all
child records, however far down they go.  Potentially dozens or even
thousands of records, all gone because the user was "just deleting a
single customer record".

I pretty much design my databases to never turn on Cascade delete, and
then build delete queries tied to buttons which only supervisors can
see/click.

John W. Colby
www.ColbyConsulting.com 

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



More information about the AccessD mailing list