John W. Colby
jcolby at colbyconsulting.com
Sun Feb 2 14:05:04 CST 2003
>I have found Cascade Delete to be an important and powerful RI tool when used in the appropriate situations. Call it what you will. You used RI capabilities to give users not even capable of understanding relationships the ability to delete things that they otherwise wouldn't have been able to delete. As I stated very clearly, if EVERYONE has the authority to delete these records then who cares. If you are turning on cascade deletes to give 1/2 your users abilities they need, but simultaneously giving another 1/2 of your users abilities they shouldn't have, then you are living very dangerously. THAT is the lazy man's way of doing things. If the shoe fits, then the shoe fits. If it doesn't then I wasn't even talking to you. >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. And I state, very clearly, that this is what delete queries exist for. APPLICATIONS have no business setting rules with such wide applicability. How would you like it if I came in to your database and said, "Sorry John, I want to turn on cascade delete here because MY application needs it (I am too damned lazy to build delete queries, or too ignorant, or too...)". I tend to think you would be annoyed. I would certainly be annoyed. The whole point of server side (using the term loosely here) rules is that they are enforced whether the application knows they are needed or not. Cascade deletes are very much a server side rule. They allow (or disallow) the whole world to do something. MAYBE your application is the only one using this thing. But will it be tomorrow? After you leave? Will those new users of the new application be allowed to delete these things? Don't you care? I do! Cascade delete gives ANYBODY, accessing the database BE from ANY language, from ANY application, or even directly opening the BE and manually deleting things, the ability to delete a record and all of it's children (and grand / great grand...). That is a VERY dangerous thing to do in order to not have to write delete queries for the application to use to delete specific records for specific users. I can now walk (or remote access) into your environment and wreak havoc with your database, not having a clue how things relate to other things etc. Don't think for a minute it doesn't happen. I am hired by the engineering office down the hall (or on the other side of the country). You don't even know I exist. I delete your stuff. You jump up and down, swear, shoot your users, are arrested and hauled off to jail. And I don't even know that you are angry. Not too bright really (IMHO). Cascade delete is a tool. It can be used to prevent deletes as easily as it can be used to allow ripping the guts out of your database. I choose to use it to prevent ripping the guts out of my systems. I take the time to build delete queries. If it is my database. It isn't always, in which case I state my piece and go on about my business. As I stated very clearly, if EVERYONE has the authority to delete these records then who cares. Obviously every user who ever will come in contact with your data has or will have the authority to delete the records that you set up cascade deletes on. In which case I can only say, "Good job"! 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: Sunday, February 02, 2003 12:41 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) 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.