Solved

Why use "soft deletes"?

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hbo knew my windows software 4 62
Loops and updating in SQL Query 9 55
VB.net DataGridView show bit field as Text column not CheckBox column 5 18
ASP.NET MVC - Views 3 22
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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