Re: Cascading Deletes

Kristian Damm Jensen wrote in message
<3A88F353.1AD0BBAC_at_REMOVEcapgemini.dk>...
>Todd Taylor wrote:
>>
>> Peter / Kristian,
>>
>> Let me re-phrase the example to clarify the point I was trying to make.
>>
>> The system was orignally delivered with the ON DELETE CASCADE clause used
>> with the foreign key declaration between order_header and order_detail.
>> Only one DELETE statement is used by the system to delete an order.
 After a
>> few months of use the customer changes their mind on some requirement in
 the
>> system and, after some analysis, it is decided that the foreign key must
 be
>> re-created _without_ the ON DELETE CASCADE clause. Had the application
 not
>> been written to use the cascading delete, that portion of the code would
 not
>> have to be re-coded, assuming someone remembered that it even needed
>> changing.
>
>That various parts of the code is depended on each other is nothing new.
>Of course this requires you to analyse these dependencies when you make
>changes, which means hard work and risk of forgetting some things. In
>this specific case the dependency is between program code and DDL, but
>DDL is code too.

A well architected application will have isolated the impact of this type of change. My point was that if you did not use the cascading delete, then you would have had to code in the "delete children then parent" logic to start with, and there would have never been an impact on the code.

>On the other hand, these dependencies cannot be totally avoided,and if
>you are trying to avoid it as much as you can, you will have to
>duplicate code in the system, making it harder to maintain in other
>aspects.

Well architected applications do not have duplicate code.

>As I have said earlier I tend to favour the attitude that the DBMS
>should do as much work as possible. If then, at the initial design of
>the system, it makes sense that headers and headerdetails are seen as
>one entity, then I would use cascading deletes. When the customer
>arrives with changed specs 2 month later, I will make it clear to him,
>what the cost of changing ones mind is, then make the revision of the
>program if he's willing to pay. I will *not* take into account each and
>every possible change of rule when designing the system.

Your last two statements in this paragraph are very disturbing and I am sorry to hear that you take that approach to development. Requirements will ALWAYS change. I design my databases and my applications to be flexible to change. If there are assumptions to be made, I run them by my clients BEFORE I make them so that they are aware of how large an impact a change to that part of the system can be. My customers appreciate the fact that they can usually come to me with a changes that can be easily implemented. Sometimes even some "major" requirements changes can be made with minimal effort. Always, always think ahead. It will pay off for you in the end.

>> Let's also say that, for whatever reason, it is valid that some order
>> headers can exist without details. With that in mind, I'll ask again, if
>> something happened to the system and you had to go behind the scenes to
>> remove the bad headers, with cascading deletes on, are you sure you typed
 in
>> the right surrogate key for the header table? Peter's query will not
 help
>> because you now have to type in the IDs. Granted, nothing will protect
 you
>> from deleting the valid "bereaved" headers inadvertantly, but not having
 the
>> cascading deletes enabled protects the other orders.
>>
>> Does this change anything for either of you? I think the specifics of
 the
>> example were getting in the way before.
>
>Yes, the above example is so specific, that it does not make sense to
>discuss the general aspects of the problem on this basis. If you are
>absolutely sure, that there is only one place in the whole system, where
>deletion of headers take place, then the discussion is meaningless. Why?
>Because the problems involved in changing the system will be very minor,
>no matter which approach you used to start with. The trouble is: what
>will you do, when the system gets complex?
>
><snip>
>
>Why, oh why can't you quote usenet-style? Either you are answering a
>point made in the post you are responding to, in which case it will
>clearer, if you put the response *below* the quote, or you are not, in
>which case you might as well delete the quote.

In the case that I am not answering a specific point (you say I should just delete everything), I generally include all ( or at least the relevant portions ) of the previous posts so that readers do not have to find the previous post to find out what was being discussed prior to the current post. Keep in mind, some newsgroup servers archive postings and referencing the previous ones is not possible without doing a search on Deja(oops, I mean Google). How convenient is that? However, if that is the way it is supposed to be then I will try to do better next time.

TNT
>--
>Kristian Damm Jensen | Feed the hungry. Go to
>kristian-damm.jensen_at_capgemini.dk | http://www.thehungersite.com
>
>
Received on Wed Feb 14 2001 - 05:28:33 CET