Soft deletion probably isn't worth it

I, for one, have undeleted things tons of times, taking them of the trash can before emptying it, undoing the delete action (in apps where this is possible), and so on.

Yes.

To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade.

My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great.

But that's my point. If you're relying on only the label of tables and views to determine what relation they represent, then you're already in an extremely unrealistic scenario, but in that scenario the names of the relevant tables and views in this example are as reasonable as one might expect.

> Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is.

But again, my point is that it's impossible to implement a technical solution to the problem that if one has multiple sets of numbers they can choose from then it is physically possible for them to choose the wrong one.

> believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive.

I was clear that I think it's possible to choose the wrong table. Just like it would be possible to select count(*) from blah where is_deleted = true when you intended to select count(*) from blah where is_deleted = false. Just like it would be possible to select count(*) from movies instead of select count(*) from television_series, in which case you'd get the wrong answer if what you wanted was a count of television series! Of course you should name things as best as you can, have naming conventions, have documentation of your schema, etc. so that your engineers can be informed, but the goal isn't to make it physically impossible for someone to make a mistake.

I disagree with the terminology in the article. "Soft deletion" suggests that the complexity is in the "soft" part, and that a common way of implementing it is problematic. I disagree. There isn't some orthogonal "soft vs hard" dimension to a generic concept of deletion. The complexity is in the meaning of deletion.

In accounting, you don't simply delete. Or when you do, you really do, and the two operations aren't the same in any meaningful sense. If you want data to still be available—whether it's for debugging or analysis or auditing or whatever—you should be thinking about the semantics of what you need, and structure your data model accordingly.

The `deleted_at` column approach is a DB design smell if it isn't supporting application logic (where "application" may include auditing or whatever). It works against the DB's mechanisms to maintain data integrity. FKs are just one example.

An example: consider the place where you want to keep historical data, but you're also going to be modifying your schema. If you use a `deleted_at` column, your migrations will start inventing more and more things that were simply not true at the time a deleted row was alive. It will lie to you. It's the same if you move data to a single deleted data table and then migrate that table repeatedly. For maximal semantic purity, you probably ought to leave "deleted" data in a historical table matching the historical schema, and then use views to glue things together for convenience. If you update the live data schema in an incompatible way, you might even be saved by the FK constraints on the archive tables.

But that's a pain, and whether or not it's less pain than the other approaches depends again on what deletion semantics you are targeting. Crossing your fingers and closing your eyes and hoping that your chosen mechanism's semantics are close enough to the semantics you need is going to bite you.

A `deleted_at` column can absolutely be the right solution if your rows have a status that changes over time, and one of the statuses that you're willing to support (with potentially brittle code) is "archived".