Hi Randy,

I do appreciate your middle tier perspective. As for flagging the parent record as "NonActive" rather than deleting the parent, I don't think that will buy me anything worthwhile. If I decided to flag a parent record as "NonActive" rather than deleting the parent, then I might have parents flaged "NonActive" incorrectly and in my situation that would be tantamount to having orphaned child records.

It looks to me like anything I do in the middle tier would be helpful but not always sufficient. I can think of at least one scenario where using only a middle tier solution would not prohibit orphaned child records. If the user attempting to delete the parent record determines that there are no child records and so the deletion of the parent record can proceed, but then before the actual deletion of the parent record occurs in the database, a different user adds a child record of the parent record into the database, then the first user's subsequent deletion of the parent record will leave the second user's child record orphaned. I think setting the constraint in the database would always work correctly.

The consensus I see forming is that setting the constraint on the database must be done to protect against orphaned child records. However catching the potential problem sooner from the parent object's HookPreDelete() method would be very helpful and would solve the problem most of the time. Unless someone disagrees I think I will implement both solutions just to be safe.

Thanks again,

Sam

>I agree that a database constraint is a good way to stop orphans in the database but I think that it is only part of the solution. This is a business rule that should be inforced at the middle tier level even though the backend may have the same logic. You may swap out the backend later and loose the protection if someone forgets to add it.
>
>Is there a reason that you want to delete the parent instead of flagging it as "NonActive" in some way?
>
>(Can you tell that I am a middle tier person?) lol