[magicalhippo]

We make a B2B application that's installed on-prem for a lot of customers.

We do hard deletes on most things, mainly due to legacy reasons, and almost every week we get a request to restore data that a user deleted but later realized they needed.

And quite often the user realizes this after a week or more, at which point the only option is for the user to ask their IT to restore the DB from backup so we can extract the data from there.

So adding soft deletes to the tables the users commonly do mistaken deletes from is something we're planning on doing.

I don't see the alternatives given in the article would work for us. For example few of our customers even have a data warehouse. Our current DB doesn't support temporal tables, though we are migrating to MSSQL which does, so that might be an option soon. Though unclear how well it works with 3-4 levels of child tables which would also need to be temporal, especially since we need to do hard deletes due to GDPR etc and we have customers who work 24/7 so regular downtime is not tolerated. And users will have active locks against these key tables, not sure how that'll work out with the schema changes needed for hard deletes.

[afiori]

If the thing you want to do with the deleted data is mostly ad-hoc support queries (that is you are keeping the current workflow, not adding new UI and functionality for pervasive restores) then I feel like moving deleted entries to a new "shadow table" with the same schema (eg subscription might have a soft_deleted_subscription shadow) might work well for you.

I have never implemented this, but I feel like it would work well (including not having to specify a deleted_date IS NULL on every query)

[magicalhippo]

That is a possibility. But all our "soft-delete targets" have at least 2-3 levels of child tables, it's never just one table. So that complicates matters.

For example, it could be the user deletes a customer entry in our system, the customer has contacts, and each contact has multiple contact methods say. There are many other child tables for a customer, like delivery addresses and official id numbers and so on, this was just an example.

And yes, user wants to use this data as before it was deleted. So has to go back into the same tables.

On the bright side, we don't have too many foreign keys to "associated data". For example, orders with that customer id would not change, just point to a non-existing customer. So after restore that all works fine.

[yau8edq12i]

Why give the possibility to the user of deleting a customer, in that situation? Having orders pointing to inexistant customers sounds rough.

[magicalhippo]

Quite a lot of our clients find large lists/tables inelegant, confusing or otherwise annoying. They like to have them neat and tidy.

There's also GDRP, they can register persons as a customer and get requested to delete data, so they need to have the ability to actually delete.

But mostly the reason is legacy. Just quicker and easier to delete from database than implement a decent soft-delete.