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 >