Solved

Why use "soft deletes"?

Posted on 2007-03-22
3
642 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
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this article I will describe the Copy Database Wizard 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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now