John Bartow
john at winhaven.net
Sun Feb 2 11:50:00 CST 2003
John, "Lazy" is not something that I'm accused of very often! Cool, I have to tell my wife this one ;o) But I guess I would prefer to be called "ignorant" by someone of your Access stature. I believe that your situation may be quite different than mine. I would say that Cascade Delete should be turned on for any child record of a primary record which can be deleted by an application. Thus, the situation must exist where an application is allowed to delete a record. The applications which you are involved with apparently do not include this situation. Note that I say application and not "user". I don't advise allowing "users" to access the BE. And I do understand this is not an easy thing to accomplish. It must be part an organization's rules of conduct, discipline and the effort to secure it as best as possible. In my situation, where there are multiple developers utilizing the same BE with different applications (VB apps, Access FEs, GeoMedia GIS, etc.) the use of cascade delete is desirable in some situations. If I depended on using specific queries to do this same task, I would end up with a mess because not every developer working on this project understands Access queries on their various uses. My contract does not include instructing every developer on the subtleties of the database and how I did "work around's" to not using the built in RI capabilities. But I don't use Cascade Delete everywhere, quite to the contrary, when I started advising this particular client on the management of this particular BE, the first thing I did was to have them remove dozens of Cascade Deletes which could result in the nightmarish scenario you have presented. The second thing I did was to detect and delete around 7,000 orphaned records where the Cascade Delete should have been turned on! (This was in a VB app that allowed for the development of hypothetical scenarios which could be accepted for use or not at the whim of the end user. The VB programmer either didn't bother or didn't know how to delete child records for the scenarios which weren't accepted. These orphaned scenarios couldn't even be accessed again via the VB interface.) I have found Cascade Delete to be an important and powerful RI tool when used in the appropriate situations. John B. 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