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.