Why use "soft deletes"?
Posted on 2007-03-22
I'm writing database housekeeping routines, at very-newbie-in-DBA-land level, and ran into an old friend, the "soft delete".
I've got several records in different parent/child relationships, and in my newbie-ness I'm way invested in tableadapters and no cascading referential integrity relationship...stuff.
But I did write "soft delete" queries, which update recordstatus to a non-active value, so the record is passed over by all other queries.
When I delete a parent, in some cases, I check for the child, and give warning that the child has to be deleted first, and cancel the delete. That all works fine.
Probably better in stored procedures, but at the moment that's the one extra CSS/JS/VB/SQL/Hosting/whatnot that I'm unable to absorb. There's always release 2.
Setting aside the fact that I probably should be using more powerful technologies and architecture, I'm still stuck with the fact that I'm not sure where and why I would use the "soft delete" in the first place.
Now I'm thinking, "What was I thinking?" If it's soft deleted, then it basically is lost, and has to be dug out with additional formats and procedures, and then hard-deleted at some point anyway.
And how would I recover from a soft delete anyway? I don't have and don't want to offer procedure for that.
So maybe soft delete was a road to the dump, instead of the scenic overlook, and I should not use it unless there's a good reason...just do "hard" deletes, flashing warnings and checking for the children, and relying on DB level backups to restore data at terrifying custom recovery rates (read EULA carefully.)
Plus I'm going to capture the record create and delete events in an app history log, and there's always the SQL Log...if I ever learn anything about it.
Any thoughts on the usefulness and/or trouble with soft-deletes would be appreciated.