Soft Deletion in Database

One of the common problems you may face when you design your database is that you want to delete a row from some table in the database without actually deleting it.

The name of this concept is called Soft Deletion. You can apply soft deletion by creating a column called IS_DELETED in the table you want. This column is of the type Boolean. If its value is True, then the row has been deleted or invisible to be accurate.

It's a straightforward concept to apply, but it comes with costs. You may face problems where you are building complex queries on this table; then later, you discover by coincidence that you forgot to consider this column in your queries from the wrong data they produce. At this moment, you discover that you have to include IS_DELETED in nearly every single query you build which has this table.

That's why some people try to avoid this method to apply soft deletion by creating an archive table. When you delete some row from the original table, just insert it into the archive table. Hence, no query mistakes on the original table and no IS_DELETED overhead.

Try to avoid soft deletion as much as you can, and try not to use it unless there is a real need to do so. Notice that soft deletion has nothing to do with backups. Backups have to be done regardless you're applying soft deletion or not. They have to be applied periodically, and the period itself depends on your application.

Comments

[David Kröll]

Great article, I'd like to share some additional solution which came to my mind:

Another possible appraoch would be to create a view to apply the WHERE clause automatically, so that the deleted entries are automatically filtered when they are not used explicitely. I've already seen something similar but with a timestamp datatype, so there is additional info when the entry was deleted.

[Amr Saeed]

Thank you, David.

Yes, this is also a valid approach and many developers use it.

[Maxi Contieri]

Nice. Great advice

Soft deletion makes joins very complicated and is very error prone

Remember . If you need to model state on an entry you should do it by placing the entity in another set (table). Not by changing an attribute as you correctly pointed out

[Pacharapol Withayasakpunt]

Golang's Gorm uses DeletedAt.

[Gregori Piñeres]

PHP Laravel

class Cat extends Model
{
    use SoftDeletes;
}