Are soft deletes a good idea?

Question

Are soft deletes a good idea or a bad idea? Instead of actually deleting a record in your database, you would just flag it as `IsDeleted = true`, and upon recovery of the record you could just flag it as `False`. Is this a good idea? Is it a better idea to physically delete the record, then move it to an archive database, and if the user wants the record back, then software will look for the record in the archive and recreate it?

Answer 1

Soft delete makes it much easier to restore the "deleted" value therefore offers more flexibility compared to a regular delete. On the other hand, you have to use more hard drive space to store all those soft deletions, but then again, you can restore those regular deletions by applying a backup of your database, so it's really supposed to be a business decision. There are pros and cons for both approaches, depending on what your priorities are.

Answer 2

I would suggest creating another table in which all deleted data are resided. To accomplish this task, an After Update trigger needs to be created in the real-time table on a field/attribute that is being flagged as Delete. Once that filed is flagged as Delete, After Update trigger will be automatically fired and the entire record will be moved to another table (e.g. tblDeleted) and the current record of real-time table will be deleted. Conversely, you will need to create another After Update for Deleted records in the deleted table in which a record being flagged as Undelete will be moved back to its original real-time table and will be removed from the deleted table. To delete records physically and forever from disk, you can create a Stored Procedure by which scheduled checking can be done automatically on the records in tblDeleted table. For example, a record will be physically removed when it gets close its lifetime threshold.