[AccessD] Cascade-delete (was: Estimating Help)

Mwp.Reid at Queens-Belfast.AC.UK Mwp.Reid at Queens-Belfast.AC.UK
Sun Feb 2 05:48:01 CST 2003


Spot on

Martin

Quoting "John W. Colby" <jcolby at colbyconsulting.com>:

> John,
> 
> You are correct but there is another way of looking at this.  The LACK
> of
> cascade delete PREVENTS people that aren't supposed to be deleting
> records
> from deleting them.  So by turning it OFF you are ENFORCING that no
> one
> except those that are supposed to delete them is allowed to (by
> running
> delete queries).
> 
> By turning it ON, you are allowing anyone and their grandmother to
> delete
> records that they aren't supposed to.
> 
> What a nightmare!
> 
> Again, it boils down to "is EVERYONE supposed to be able to delete
> these
> records, or just a few"?  If everyone is supposed to be able to delete
> things then there's no point in turning it off.  In my databases I
> absolutely do NOT want the operators deleting things.  Delete a type
> of
> claim status and sure it warns you "related records will be deleted" but
> if
> they don't understand that, then claims get deleted,  but wait...
> claims
> can't be deleted without claim contacts, payments, ATPs, expenses, etc. 
> So
> some person who has no business deleting the thing in the first place
> has
> just ripped out god knows how many records in a dozen different
> tables.
> 
> I am absolutely a fan of referential integrity.  Turn it on by all means
> -
> use it as it was meant to be used.  Once you have turned it on however,
> what
> is the point of referential integrity if the salesman down the hall has
> the
> ability to construct a query and "oops" I hit delete... can you get my
> data
> back?".  You just made damned sure that he deleted the world by
> enforcing
> referential integrity but leaving cascade delete on.
> 
> Don't take this the wrong way, but the correct way to handle what you
> need
> to do is to analyze what tables need records deleted, in what order
> they
> need to be deleted to allow RI to be enforced, build delete queries to
> do
> the deletes, execute them in the correct order, and wrap them in
> transactions so that if any fail they all fail.
> 
> You are using cascade delete as the lazy man's way out.  And in the
> process
> you are exposing the db to anyone who should NOT be allowed to do the
> deletes, as well as those who should.
> 
> 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 John Bartow
> Sent: Saturday, February 01, 2003 9:37 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Cascade-delete (was: Estimating Help)
> 
> 
> I've always seen this as the difference of where you come from, ie. db
> administrator or programmer.
> 
> A db admin would want it in the db so that no programmer can miss the
> logic
> and screw things up - a programmer would want to do it in code.
> 
> I came from the DB admin. of Informix on Unix and I had a DB where the
> ref.
> int was ignored by the original programmers because they were going to
> do
> everything themselves. Problem is they weren't the only programmers
> and
> things don't stay static. It was a night mare.
> 
> So I look at it as this is the "way it should work for everyone who
> uses
> this DB and no one gets around that. In the case of cascade delete I
> certainly don't use it on everything but in the obvious case of where
> the
> business rules allow a record to be deleted - use it so that all of
> the
> records child tables are cleaned up when it is deleted.
> 
> As far as cascade updates - they aren't needed when using auto # keys
> but
> certainly are when using "natural keys", e.g. keys that can be broken
> and
> will need to fixed because the system didn't use auto # keys to start
> with
> ;-)
> 
> Referential integrity - its great!
> 
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Drew Wutka
> Sent: Saturday, February 01, 2003 3:53 PM
> To: 'accessd at databaseadvisors.com'
> Subject: RE: [AccessD] Cascade-delete (was: Estimating Help)
> 
> 
> Just my two cents here.  I personally build my own business rules into
> the
> front end, or the middle tier.  I don't like 'automatic' processes that
> I
> haven't built personally! <VBG>  I think of it along the same lines of
> tghe
> B vs. UnB issue.  It's there, and I can see it's use, I just prefer to
> control things on my own.
> 
> Drew
> 
> -----Original Message-----
> From: Jeanine Scott [mailto:jscott at mchsi.com]
> Sent: Saturday, February 01, 2003 3:05 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Cascade-delete (was: Estimating Help)
> 
> 
> Yes, I have been lucky. :) All the databases I've designed (with the
> exception of my very first) I've locked the user's totally out of the
> backend. They can only do what I give them security to do with the
> exception
> of one power user that I've really (and I stress really!) trusted.
> 
> I supposed I'll be one of those that has to be burned before I follow
> the
> path of wisdom. :) I definitely can see that situation you described
> happening if you have a project where the client isn't willing to pay
> for
> the administrative interface.
> 
> 
> Jeanine Scott
> Sr. Systems Analyst
> Spindustry Systems
> 515-669-2074
> jscott at spindustry.com
> 
> CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
> is
> for the sole use of the intended recipient(s) and may contain
> confidential
> information. Any unauthorized review, use, disclosure, or distribution
> is
> prohibited. If you are not the intended recipient, please contact the
> sender
> by reply e-mail and destroy all copies of the original message including
> any
> attachments.
> 
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John W. Colby
> Sent: Saturday, February 01, 2003 2:43 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Cascade-delete (was: Estimating Help)
> 
> 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.
> 
> I should have such users!  ;-)
> 
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.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