What is your goal by doing a soft delete?
1. If the reason is because you need to access those data in the future, an archive table is great for your purpose.
2. You added IsDeleted column in your table for soft delete? You have to know you are creating unnecessary query on this column. Some will say, Views are fast and we can use that view or any materialized table to retrieve data. But do not forget, each view bring another performance cost.
Plus you have to include WHERE IsDeleted = false to all your existing queries. If you use nhibernate and entity framework you will have to add this extra code everywhere.
Any aggregate function will have to exclude the isdeleted record.
3. Confusion: A user on your app, will notice a delete record show up again…
4. Relationship with Foreign Key: Here you have to be careful. If table A with the Isdeleted record is link to table B, you may want to remove the B and not show it in the UI. Are you going for another soft delete in this table? Let s say yes; Then you try to add another record with same name, and due to constraint you cannot. Because the name has to be unique for example. You will tell me that one can just enable this record. YES. But do you understand the complexity of your code. Thus the complexities of soft delete bring never stop at a simple select query .
Also referential integrity stops working the right way with soft deleted
In conclusion, it’s all depends.
Never say never. However you need to find excellent reasons to use soft delete.
“Why not” is not a good reason and I will expect you to demonstrate the benefit of your decision before proceeding.
