What’s the actual use case for soft deletes. Audits? Have an audit log then. There’s little reason to have deleted but not really deleted items in your database.
What's a use case for not even allowing soft deletes?
Easy, alternative soft deletion: `deleted_record_insert`
91–100 of 109 posts
Re: Easy, alternative soft deletion: `deleted_record_insert`
#92In some cases a soft delete is the only option. Say I run a chain of stores and wish to close a particular store mid year. The existing sales data still needs to reference the now closed store, but any other attempt to interact with the store should be prevented.
The application and its use of data must take this into account. I don't believe there is some magic bullet here. However, audit tables are invaluable for tracking changes and culprits.
Eventually, the store and all its data can be deleted using cascade delete to maintain referential integrity.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#93Earlier quoted context omitted.
Here's a simple test: would the data be turned up in legal discovery? Nobody is doing a sector level disk scan but you would be expected to turn over relevant audit data if you had retained it. So it needs to be accurate and GDPR applies.
Not really. You actually may have other legal requirements to keep the data but you shouldn't use it for daily business because of GDPR. Imagine banks and long closed accounts. Moreover, technical backup solutions, where only a very limited set of people have access, are fine. If you store DB backups, you don't have to rewrite or delete them because a customer that used your service for a week decided to ask for dele…
That's a different part of GDPR: purpose limits. Deleted or not, you can only use data for an approved consented purpose that is mapped to that data item.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#94In most case, i just see deletion as change status from `active` to `deleted`. You should never remove the actual record from database.
That's illegal in Europe, and unethical everywhere.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#95In some cases a soft delete is the only option. Say I run a chain of stores and wish to close a particular store mid year. The existing sales data still needs to reference the now closed store, but any other attempt to interact with the store should be prevented. The application and its use of data must take this into account. I don't believe there is some magic bullet here. However, audit tables are invaluable for t…
I’ve found similar and settled on three distinct solutions that tackle different aspects of the problem that soft deleting covers.
The first is analytics/reporting, which can be used to aggregate or otherwise store data without being coupled to the main application (therefore, not a problem to delete data if it’s been processed already).
The second is auditing to maintain a paper trail.
The third is a soft delete backed by a TTL, basically so there’s a window of time to undo the operation, but the data doesn’t remain there forever (which might be a problem with GDPR and the like - depending on what your data is.)
Main reason I had for trying to break it down this way was because soft deletes in a relational database, with foreign key constraints, becomes unintuitive pretty fast. You need escape hatches to properly delete records, you need to remember to filter deleted ones from your results, and then there’s even more complication when trying to handle this across associations. So the final piece is only to soft delete things that require it, rather than enforcing it across all tables.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#96I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.
I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change.. how did solve that particular issue? or was it not a requirement in your case?
I implemented in the app layer so had the user available to implement the new audit record. I suppose doing it in the DB layer would impose a challenge. The few ways to solve that that I’m aware of would require a) your app to kick off a function with the user id of the mutation or b) pushing user auth into the DB layer and principal switching your DB connection. Both have downsides IMO.
I don’t typically favor DB level solutions because bleeding app logic beyond basic schema and integrity means you lose some portability and/or your architecture messes separation of concerns pretty quick. Maybe that means you don’t get the full benefit of the DB but it’s a trade I usually make.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#97In most case, i just see deletion as change status from `active` to `deleted`. You should never remove the actual record from database.
That's illegal in Europe, and unethical everywhere.
That depends on the industry.
In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years).
There are ways to comply without soft-deletion, but in practice they are much more expensive to implement and never used.
0: "Illegal" is the wrong word, really "non-compliant with regulations, resulting in significant fines but except in case of gross negligence or fraud, probably not criminal charges".
Re: Easy, alternative soft deletion: `deleted_record_insert`
#98Earlier quoted context omitted.
True, but in that case I wonder the reason why you or anyone else has such direct access to such data. ETL would eliminate such things for a data lake before anyone would run ad-hoc queries (that could and likely do) contains PII and other privileged data.
Well, for one thing, there are countless software systems worked on by hundreds of thousands of developers which will never include a data lake or any ETL more complex than "a programmer writes a script to generate a CSV and upload it/email it somewhere".
And likely all breaking GDPR laws
Re: Easy, alternative soft deletion: `deleted_record_insert`
#99Earlier quoted context omitted.
I think there's still a spot for deleted_at or deleted_at'like functionality. It's around historical data, especially in a work scenario. For example a worker might create a thread and then 38 other workers reply to it. There could be a lot of great information in this thread. It could also be referenced in 5 other threads and external sources (docs, etc.). If the worker leaves the company, should you really delete t…
Wouldn't this be better handled by marking the user entry as inactive and then reading that value from the join rather than setting every conversation thread as is_deleted/is_deactivated? I feel like you are solving a different problem than the one presented in the article.
It was more about the comment I was replying to around a use case where historical data is important to keep around.
Marking it as inactive with a boolean makes you lose the context on when the inactive toggle happened.
Chances are you want to use deleted_at on the user in my use case because that provides you the timestamp of when it happened and also lets you have application logic which prevents that user from being able to login and do things, but still lets you show their previous information to everyone else as if they didn't leave and all you have to do is have a tiny bit of template logic that appends " (Deactivated)" to their name if `deleted_at` is not NULL.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#100Earlier quoted context omitted.
Well, for one thing, there are countless software systems worked on by hundreds of thousands of developers which will never include a data lake or any ETL more complex than "a programmer writes a script to generate a CSV and upload it/email it somewhere".
And likely all breaking GDPR laws
Or not doing business in the EU.