Do any databases let you refer to constant values in foreign keys?
Then you could do
FOREIGN KEY (foreign_id, NULL) REFERENCES foreign_table(id, deleted_at)Soft deletion probably isn't worth it
11–20 of 514 posts
Re: Soft deletion probably isn't worth it
#12The "Code leakage" problem can easily be solved by using views. Or am I missing something?
Re: Soft deletion probably isn't worth it
#13"The concept behind soft deletion is to make deletion safer, and reversible." That's one part. The other part is that in many industries you have regulatory data retention and audit requirements. This is arguably the most valuable and common reason to perform Logical deletes.
In banking and bookkeeping, there’s no such thing as a “delete”. Once something is in the ledger you can’t undo it - you have to make a new entry that negates the old one.
Re: Soft deletion probably isn't worth it
#14Which is why I don't add that extra deleted field. Rather duplicate all the tables into a new database called "archive" and then insert there before deleting from main.
That works for updates too, by preserving the old data and showing you a time machine like backlog. But the archive database gets too large over time and you need to purge it periodically. You can create some delete triggers for automating this "save before delete" behavior.
Re: Soft deletion probably isn't worth it
#15"The concept behind soft deletion is to make deletion safer, and reversible." That's one part. The other part is that in many industries you have regulatory data retention and audit requirements. This is arguably the most valuable and common reason to perform Logical deletes.
Why not just use an audit table, to keep from littering your indices?
You still need a record of the data, which probably isn't fully captured in the audit table
Re: Soft deletion probably isn't worth it
#16Earlier quoted context omitted.
Yep, we have an abstraction layer on top of the ORM to provide common queries. "Give me all X" will always return stuff not soft deleted. Data people also like to go diving through old data, and without getting into data warehousing and stuff like that, it's not too complex to support a single flag to enable us to keep old stuff.
They might like to, but you should definitely consider if saving data no longer required for your business violates privacy regulation/ethical considerations.
Definitely. When a user "deletes" their account, we null out all identifying fields to "DELETED_PII_$user_id". We have running metrics we compute that would go off the rails if we dropped the row completely.
Re: Soft deletion probably isn't worth it
#17If you are going to think about this pattern, why not go one step further and simply event source everything with an append-only, immutable log?
You could even sprinkle cryptographic guarantees into the mix. This would be very challenging to do with mutable DB rows.
Re: Soft deletion probably isn't worth it
#18The "Code leakage" problem can easily be solved by using views. Or am I missing something?
Solved with "...and deleted = false"
Re: Soft deletion probably isn't worth it
#19Soft deletion is certainly very situationally worth it. I've found the most value when 1. it is well supported at the ORM layer and 2. business requirements dictate strong auditability of data. While I have undeleted items on occasion, I've used soft deletes more frequently to debug and build a timeline of events around the data. For context, I've worked in fintech where I often needed to review backoffice approvals,…
Wouldn’t storing the deleted data in an immutable storage, with time stamp, be much better for auditability ? I mean how could you audit deleted, restored and deleted again data with that setup?
Also, while I know it’s not really accurate, I tend to understand relations as sets, it makes me uncomfortable to have soft deleted data that are neither member or not member of the set.
Re: Soft deletion probably isn't worth it
#20Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE deleted_at IS NULL solves the majority of the author's problems, including (IIRC) foreign key issues, assuming the deletes are done appropriately.
I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against. Sure, concerns about splitting logic between the DB and app layers are valid, but there are fairly well developed techniques for keeping DB and app states, logic and schemas aligned via migrations and partitioning and whatnot.