Solved

Why use "soft deletes"?

Posted on 2007-03-22
3
650 Views
Last Modified: 2012-05-05
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.

Thanks!
0
Comment
Question by:codequest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 18777988
check the legal aspects of what you're doing you may find that legally you have to keep you're actuall records
of all transactions for longer than  you think...

basically auditability is the prime reason to use soft deletes...

but they can also avoid the performance overhead of a hard delete...

you can always have a strategy to soft delete transactionally , and then let a housekeeping routine
actually perform the delete/archive...
0
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 250 total points
ID: 18779173
Lowfatspread is exactly right...soft deletes let you maintain the data no matter what...but keep in mind the "legal aspects" might require the opposite...for example if your site maintains personal data or financial data...if a user wants to remove their data from your system and you did a soft delete instead of a real delete...their data is still in your system and should it ever come out legally that you maintained their data even though they thought they "deleted" it...you could be in trouble.
0
 
LVL 2

Author Comment

by:codequest
ID: 18780941
Thanks for inputs.  

Yeah, I remembered that I was using something like  Lowsfatspread's reasoning on why I wanted to do soft deletes.    Good to get that validation.

And, I had considered samtran's perspective.  Yes, it's all coming back to me now...

Good stuff.   That's got the gears turning again, and I think I know what I need to do.   There are no commercial transactions so my legal requirements are probably light.   Basically, 1) let user admin's do hard deletes, 2) save "fat" history records for billing and use reports that don't need the DB for lookup values, and 3) build routines to find "aged" data (to notify users of) and orphans (to delete when found.)  Since I'll be the admin in the early phases of deployment this should work.  It's a cheap solution with an hefty upgrade/conversion cost, but there's only so much time for release 1....

Any further thoughts welcome...though I think this is bagged.

Grazie!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question