Data - Do you avoid deleting records by flagging them as IsDeleted (aka Soft Delete)?
21/05/2020 3:38 PM by
When users are deleting a lot of records as part of normal operations - they can and do make mistakes. Instead of the painful process of having to go to a backup to get these records, why not simply flag the records as IsDeleted?
- You do not have to delete all related records e.g. Customers, Orders, Order Details. Instead, you can just flag the parent record as deleted with an "IsDeleted" bit field.
- You do not lose historical data e.g. how many products one of your previous clients purchased.
- You can actually see who deleted the record, as your standard audit columns (e.g. DateUpdated, UserUpdated are still there. The record does not just vanish.
- It is simple to implement - particularly when using a code generator. For example - our code generator produces views and stored procedures, and all data access layer code. With all data access done through the data layer views, we simply had to add a filter to all views ("WHERE IsDeleted = 0"). Our autogenerated delete stored procedures simply set the "IsDeleted" column to false.
- Depending on your interface design, you may have to join to parent tables to ensure that deleted child records do not appear. Typically, the interface would be designed in such a way that you would not need be able to created new records based on the deleted items (e.g. you cannot create a new order record for a customer that is deleted). Performance of queries can potentially suffer if you have to do these joins.
- While storage space is very cheap, you are not removing records from your database. You may need to archive records if the number of deleted records becomes large.
Also see Using Audit Tools for alternatives to this approach using 3rd party auditing tools.
Watch William Liebenberg's SpendOps talk for more details about why soft deletes are advantageous in Azure:
Do you feel this rule needs an update?