DEV Community

Amr Saeed
Amr Saeed

Posted on

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.

Top comments (5)

davidkroell profile image
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.

amrsaeedhosny profile image
Amr Saeed

Thank you, David.

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

mcsee profile image
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

patarapolw profile image
Pacharapol Withayasakpunt

Golang's Gorm uses DeletedAt.

gregorip02 profile image
Gregori Piñeres • Edited

PHP Laravel

class Cat extends Model
    use SoftDeletes;
Enter fullscreen mode Exit fullscreen mode