Solved

Why use "soft deletes"?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

929 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

10 Experts available now in Live!

Get 1:1 Help Now