The lengths people will go to, to avoid using an ORM!
Avoiding the soft delete anti-pattern
51–60 of 61 posts
Re: Avoiding the soft delete anti-pattern
#52Just add on delete trigger to store whole row as json in generic archive table and move on.
I would never do that. A delete trigger to a generic archive table that stores the row as a row, this is what I would do. JSON in a RDBMS is something that can be done, but rarely should be done. Why keep it in the database and not as a file on disk (in the filesystem), otherwise?
Re: Avoiding the soft delete anti-pattern
#53How about a separate, schema-wise identical "deleted_x" table that you "move" deleted entities to? Can't get much more explicit than that, and still enables whatever joins you'd like on historical deleted data.
If you're using foreign keys, that becomes quite tricky. Not impossible, but you're dealing with way more than a single record being moved and may need to duplicate some references between live and deleted data.
Re: Avoiding the soft delete anti-pattern
#54Just add on delete trigger to store whole row as json in generic archive table and move on.
I would never do that. A delete trigger to a generic archive table that stores the row as a row, this is what I would do. JSON in a RDBMS is something that can be done, but rarely should be done. Why keep it in the database and not as a file on disk (in the filesystem), otherwise?
How do you store it as a row in your case? JSON offers a very reasonable trade off; it works forever over all tables in spite of schema changes.
Re: Avoiding the soft delete anti-pattern
#55Earlier quoted context omitted.
I would never do that. A delete trigger to a generic archive table that stores the row as a row, this is what I would do. JSON in a RDBMS is something that can be done, but rarely should be done. Why keep it in the database and not as a file on disk (in the filesystem), otherwise?
How do you store it as a row in your case? JSON offers a very reasonable trade off; it works forever over all tables in spite of schema changes.
A schema change combined with a soft delete is a very different scenario. RDBMS are a very expensive way to store JSON.
Soft delete purpose is either short term "simulate delete until you are sure you can hard delete" or "hide it and archive it". In the first case long term storage is not a problem, in the second you want to keep it in the database if you want the option to query it (and you want the table structure for that, JSON query is very expensive) or take it out if you don't. At least these are the use cases I saw in 30 years of software engineering.
Re: Avoiding the soft delete anti-pattern
#56It’s a great article exploring the idea, but the premise and arguments leading to it are somewhat weak, imo. First, views aren’t “fragile”. I may be wrong here, but it feels like TA tries to squeeze that along with some abstract-ORM issues. Second, “anti-pattern” is a very technical rating of this phenomenon. Business logic and its databases may contain data that may, may not, or can never be viewed as deletable, at…
I don't think the author is saying that views aren't fragile. Requiring every view and orm dao to have that annotation is fragile because you might forget it.
Re: Avoiding the soft delete anti-pattern
#57Earlier quoted context omitted.
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…
Why give the possibility to the user of deleting a customer, in that situation? Having orders pointing to inexistant customers sounds rough.
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.
Re: Avoiding the soft delete anti-pattern
#58Just add on delete trigger to store whole row as json in generic archive table and move on.
I would never do that. A delete trigger to a generic archive table that stores the row as a row, this is what I would do. JSON in a RDBMS is something that can be done, but rarely should be done. Why keep it in the database and not as a file on disk (in the filesystem), otherwise?
You want to disconnect it from fk constraints and schema because you don't want to manage schema evolution on deleted history.
If you keep it as mirrored schema you'll have to manage individual history table per table (high overhead), manage it on every migration and you'll run into problems sooner or later - you won't be able to migrate it correctly as historic records will be disconnected unlike ordinary ones.
Re: Avoiding the soft delete anti-pattern
#59Earlier quoted context omitted.
Yeah, before breaking out Debezium, Kafka Connect and S3, consider soft delete first. It might not scale, but maybe you don't need to scale just yet, and maybe a column called is_deleted is far more appropriate and far less complex for your current purposes.
IMO deleted_at, either with null or epoch as the "not deleted" value is nicer than is_deleted because it allows some level of auditing.
Good point there.
Re: Avoiding the soft delete anti-pattern
#60Earlier quoted context omitted.
How do you store it as a row in your case? JSON offers a very reasonable trade off; it works forever over all tables in spite of schema changes.
A schema change combined with a soft delete is a very different scenario. RDBMS are a very expensive way to store JSON. Soft delete purpose is either short term "simulate delete until you are sure you can hard delete" or "hide it and archive it". In the first case long term storage is not a problem, in the second you want to keep it in the database if you want the option to query it (and you want the table structure…
Keeping deletions must have some answer to schema evolution of first class data.
In case of json the answer is detach.
Performance wise:
- you have access to indexed timestamps - this allows you to narrow down resultsets
- you have indexed or sharded-on table name column - useful for more monolithic databases with many tables and large datasets
- you usually want uniform (string) or variant non-unique, indexed identity fields, ie. composite up to 3 (identity, secondIdentity, thirdIdentity columns)
You don't care about the rest because its schema will vary in time and you don't want to deal with it.
You keep those deletions as second class objects because you won't interact with them as you do with first class data. You keep them for things like audit, down migrations, restore, "we can't delete stuff because we need to keep history for 6 years" etc. in a place that doesn't clutter primary dataset.
If you want typed schema you can add version column and keep version based schemas, manage schema schema version migrations in the background outside of your usual deployment cycle etc - but this becomes quite fancy, usually you don't care, you care about what's in use.