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)
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
WHEREclause 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.Thank you, David.
Yes, this is also a valid approach and many developers use it.
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
Golang's Gorm uses DeletedAt.
PHP Laravel