+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+--- Thread: What is the best way to implement soft deletion? (/Thread-What-is-the-best-way-to-implement-soft-deletion)
Pages:
1
2
RE: What is the best way to implement soft deletion? - slobdoy - 07-20-2023
Having `is_deleted` column is a reasonably good approach.
If it is in Oracle, to further increase performance I'd recommend partitioning the table by creating a list partition on `is_deleted` column.
Then deleted and non-deleted rows will physically be in different partitions, though for you it'll be transparent.
As a result, if you type a query like
SELECT * FROM table_name WHERE is_deleted = 1
then Oracle will perform the 'partition pruning' and only look into the appropriate partition. Internally a partition is a different table, but it is transparent for you as a user: you'll be able to select across the entire table no matter if it is partitioned or not. But Oracle will be able to query **ONLY the partition it needs**. For example, let's assume you have 1000 rows with `is_deleted = 0` and 100000 rows with `is_deleted = 1`, and you partition the table on `is_deleted`. Now if you include condition
WHERE ... AND IS_DELETED=0
then Oracle will ONLY scan the partition with 1000 rows. If the table weren't partitioned, it would have to scan 101000 rows (both partitions).
RE: What is the best way to implement soft deletion? - elleryfslcgmz - 07-20-2023
I would lean towards a `deleted_at` column that contains the **datetime of when the deletion took place**. Then you get a little bit of free metadata about the deletion. For your `SELECT` just get rows `WHERE deleted_at IS NULL`
RE: What is the best way to implement soft deletion? - Sirallanltat - 07-20-2023
@AdditionalCriteria("this.status <> 'deleted'")
put this on top of your `@entity`
[To see links please register here]
RE: What is the best way to implement soft deletion? - Sirgeotechnical210 - 07-20-2023
Use a view, function, or procedure that checks `is_deleted = 0`; i.e. don't select directly on the table in case the table needs to change later for other reasons.
And index the `is_deleted` column for larger tables.
Since you already have an audit trail, tracking the deletion date is redundant.
RE: What is the best way to implement soft deletion? - Mrphagocyter658 - 07-20-2023
Careful of soft-deleted records causing uniqueness constraint violations.
If your DB has columns with unique constraints then be careful that the prior soft-deleted records don’t prevent you from recreating the record.
Think of the cycle:
1. create user (login=JOE)
2. soft-delete (set deleted column to non-null.)
3. (re) create user (login=JOE). ERROR. LOGIN=JOE is already taken
Second create results in a constraint violation because login=JOE is already in the soft-deleted row.
Some techniques:
1. Move the deleted record to a new table.
2. Make your uniqueness constraint across the login and deleted_at timestamp column
My own opinion is +1 for moving to new table. It takes lots of
discipline to maintain the *AND delete_at = NULL* across all your
queries (for all of your developers)