Cascading Deletes: share your ideas

Continuing the discussion from Renaming a script can break a server-side schedule?:

In the discussion above some replies referred to cascade deletes: ranging from not using them to commenting on how hard to find they are in other people's work (or even your own).

I've seen different developers attempt various conventions to signal the fact a specific table occurrence has cascade delete enabled, mostly:

  • using a specific color
  • having something in the name of the TO that mentions it
  • combining the above 2 methods

In my own systems my convention is different altogether:

  • I dedicate a section of the graph to cascade deletes, with a large note that says "cascade deletes are defined here, nowhere else"

Yes, that means some TOs and their corresponding relationships are duplicated and, should these relationships evolve, there is increased maintenance to keep them the same.

That said, cascading deletes tend to be applied to relationships that are "bare bones" and simply express parent-child or ownership type of relations (nothing filtered or subject to a range of some sort), so they usually do not change much over time.

For me, this translates to having a clear benefit that outweighs the little extra maintenance: cascade deletes are 'centralized' easy to find for myself or someone else less familiar with this specific database.

On your end, what have you been using that works well for you (and others ideally)?

we use cd in general not, not often.
It will make sense if there is really no benefit if the deletion of object-groups should be separated - but in other situation, we do not cd (ie invoice and invoice items)

Draw back is, if You're deleting some data, junk might remain.. but if others (developers) join, there is some risk - therefore I use cd really seldom

Short answer: we use it where it makes sense to use it. Not a big fan of dogmas :slight_smile:

As to finding them: we rely on naming conventions and we run an analysis on all solutions anyway before starting work or a new phase. These things are fast and indispensable and makes finding things very easy.

1 Like

We're with Wim on this one.

If we've Quotations/Quotation Line Item, Orders/Order Line Items, etc. then it just makes so much sense to use cascading deletes.

Naming conventions are our go to tool as well.

In the rare cases where we use cascading deletes, we move them off into their own TOG, employ some TOG hopping, verify everything is right, delete, then TOG hop back to the original context.

1 Like

@jormond why do you TOG hop? Cascading deletes fire even if you're not on a layout based on a TO where the cascading delete is defined, no?
Screen Shot 2020-01-17 at 12.40.26 PM

I know of only one way to toggle cascading deletes on/off dynamically and it involves using an external data source to exclusively hold the cascading delete definitions.

2 Likes

It's mainly about quickly identifying places where it happens. They all appear in one section of the graph.

I understand that part, I think that's a fine (like wine) idea. But why do you navigate (hop) to that graph when deleting? The outcome should be the same whether switching layouts or not.

All deletes happen the same way. If you want to delete a record ( which is ridiculously rare ), you do it from those TOGs. Kind of carries, in principle, an API approach. Specific actions happen a specific way. So there are no questions.

It also helps with cognitive recognition. Because I see it a lot, I know where they are.

1 Like

Ah, I see, so you're not hopping to that TOG to make the cascades work, you're doing it as a coding practice ("this is how we delete around here"). It just happens to be where the cascades are defined too.

2 Likes

Yes. That. I could have lead with that I suppose. LOL

2 Likes

Hello @jwilling,

That is a very clever way to toggle something like that. I am wondering if you actually do it in practice, or just simply recognize it as an option (that you don't use).

I love the understanding that fuels it, but I'd be hesitant to try it in a solution, lest I inadvertently confuse someone (including myself).

Thank you,

-steve

1 Like

No, I 100% don't use it in real solutions. I treat cascading deletes as a business rule, and there's no circumstances where I'd want to toggle them off from a data integrity standpoint (maybe there are times where doing so would benefit performance, but that's secondary to the data being correct). This was just for fun/research.

But if you're interested heres the demo. Just open the main file and try toggling cascades on/off.

The idea is that the Delete_Definitions file has TOs based on the main file's tables. If the delete file is open then those relationships are "active" and will cause cascades. If the delete file is closed, then the main file has no idea about the cascades defined therein and just deletes the parent record without any cascading. Since the main file has no TO references to the deleter file, it can open and close the deleter whenever it wants.
Cascading Deletes Deleter EDS File.zip (141.5 KB)

5 Likes

Thanks for sharing @jwilling.

So very clever! -- something I applaud even if it would never go into a solution.

1 Like

We run into lots of system that do not have cascading deletes on...

In those cases, one of the first things that we do is implement Custom Menus that trap the Delete command with a Script that validates if there are related records per table using Get ( LayoutTableName ) . This helps prevent orphans while we familiarize ourselves with the system.

1 Like

When building solutions, I don't bother to mark cascading deletes in any special way. If a situation requires a cascading delete then it gets one.

When digging into someone else's work I'll always produce a DDR as the very first step. Although it's not unusual to be asked to modify the data sets in some way, I go to a lot of trouble to ensure that the right thing is going to happen. A DDR search will reveal any cascades, so they aren't invisible.

2 Likes

Thanks for the kind words. "Clever" is often the enemy of practical, so I think we're on the same page there. It sure is fun to scratch the curiosity itch sometimes, though.

And just to make this side quest less tangential, my personal preference for cascading deletes is to use anchor buoy, and define the deletes right from the anchors and put a little red text object in the graph indicating a delete rule, like in my picture above with the red box.

5 Likes

FMPerception is a lifesaver with things like this. I'm a big fan of analysis tools over documentation whenever possible- less room for human error.

I'm sure there are other analysis tools, but this is the one I use and love. It will find all cascading deletes, and it's fast!

6 Likes