What is the best way to implement soft deletion?

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).

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`

@AdditionalCriteria("this.status <> 'deleted'")

put this on top of your `@entity`

[To see links please register here]

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.

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)