2010
Soft Deletes Verses Real Deletes When Doing CRUD Interactions
JavaScript , jQuery , Web development Add commentsI happened to stumble across a blog post today by Phil Haack with the title "Death to confirmation dialogs with jquery.undoable." In it, Phil explained how he was inspired to write his plugin by seeing how his Netflix queue allowed him to "undo" the deletion of an item from the queue. As he said in his post:
"Notice that there’s no confirmation dialog that I’m most likely to ignore questioning my intent requiring me to take yet one more action to remove the movie. No, the movie is removed immediately from my queue just as I requested. I love it when software does what I tell it to do and doesn’t second guess me!"
His post got my attention because I'm currently in the middle of designing a scaffolding/code generation system, specifically to output pages for performing CRUD (Create, Read, Update, Delete) tasks, and it's currently designed so that when the user clicks on a "delete" link for a record (and Javascript is enabled), I make the user confirm their decision via a customizable dialog box (via the jqModal jQuery plugin) before proceeding with the deletion.
So I got to thinking about what would be involved in doing what he was suggesting: allowing the user to "delete" a record without confirmation as long as they had the option of undoing that action while still on the page.
I put "delete" in quotation marks because such a process would almost certainly involve making the delete a "soft" delete: marking/tagging the record as being deleted but not actually deleting the underlying database record. Undoing an actual deletion would be much harder, given that you would lose the unique record id in the deletion in addtion to the rest of the record data. I suppose you could potentially store the data from the record client-side (in the page) so the "undo" record could recreate the record from scratch, but that would be a pain and might not be feasible in every scenario. Undoing a soft delete/changing the delete flag in the record, on the other hand, would be pretty easy.
But if I went the soft delete routine, then the question would become: how do those records get deleted for real? I tend to design my web applications with a long term view, and I don't want the application database tables to fill up with deleted records over a number of years. My clients typically only have access to the database tables via the application itself, so leaving that up to them is not an option. So I would either have to given them another page/tool in the web application (perhaps a tool restricted to a tech-savvy few) to actually remove the deleted records, or perhaps run a scheduled task to remove those database records that have been marked for deletion for a year or more.
After some consideration, my current inclination is to stay with my current confirmation dialog/deletion routine. In most of my applications, the CRUD interactions are reserved for the administrative users of the application, who typically know when it's appropriate to delete a record. And the need to delete a record via the CRUD tools provided is usually rare, so having a confirmation dialog come up for each instance isn't too much of an annoyance.
But I'd be curious to hear other people's thoughts on this topic.
Jan 6, 2010 at 2:38 AM Hi Brian,
I have always liked Gmail's "undo" paradigm for deletion ... that way I only have to make a second click if I do something wrong, whereas with a confirm dialog I have to click twice every time.
I was just implementing this kind of thing in an app I'm building, and the same issues you outline ran through my head.
It came down to two ideas, depending on what kind of data you are deleting. Some deletes you regret immediately (damn... didn't mean to click that!) ... others take a while for the impact to sink in (damn ... I _knew_ I shouldn't have deleted that!).
For the mis-click scenario, I figure that to keep the db as clean as possible, you can do a hard delete of any flagged records immediately prior to each new soft delete (perhaps with conditions such as per user if you need). This way only the most recently deleted record is in a soft-deleted state.
For the second scenario, then presenting the user with a "Send to Recycle Bin" would give a familiar metaphor that even non-tech users would be happy with. You could also note to them that items in the recycle bin are permanently erased after x days, so that you eventually get clean data without burdening or relying on the user.
Views are helpful for this kinda thing, and of course you need to take care to cascade soft deletes, as you are basically breaking constraints that the db would normally protect your PK/FK relationships with.
Jan 6, 2010 at 5:57 AM Option 1: We have a special permission for this and it reveals a new application to administrators. Basically shows the 'holding' area for all records that get soft deleted. Once there, the administrator can purge-all, hard delete or recover.
Option 2: Forgo all of the above, soft delete is for the purpose of being able to 'undo' - give them a grace period of 24 hours to undo their action, otherwise... scheduled task of destruction purges.
Jan 6, 2010 at 3:53 PM A text editor usually allows me to control-z (effectively deleting my changes) all the way back to the beginning of the session--but once I close the document, the change stack is lost. A similar paradigm makes sense for your web app, given your statement about giving the user "the option of undoing that action while still on the page." I guess the challenge is i understanding when the user has left the page and effectively "closed" the app (at which point you'd want to commit the deletion). In a desktop app it's simple since the user does actually close the app, but in a stateless web app the user could just navigate away, or shut down, or go away for a year. So at what point do you say "we're done here?"
In the web world I thought the standard session timeout was about 20 minutes. Can you just keep track of sessions and, when they finish--however they finish, either via a quit or a timeout--finalize the deletions then? That would seem to emulate the behavior of a desktop app, making it intuitive to a user, and meet your requirement of just allowing a user the opportunity to undo while he's still on your page. If your app is workflow oriented--"press here to check out" or whatever--you can easily finalize the deletes as he moves thru the workflow, too.
If you do decide to keep track of changes over the long term, audit tables are better than just flagging the records. You could "undo" changes by unwinding the audit record, which would require a bit more work than just resetting a flag--but the upsides may outweigh that inconvenience. For example, by having separate audit tables, you can more easily decide how long you're going to store data, and simply batch delete or tape archive anything older. You can also index it differently as well, so as not to affect performance of the live tables. You could even put it in a different database on a different server so it's completely out of the performance picture. As a bonus it provides fabulous security, if that's important to the nature of the data, since you can see who changed what, and when.
It may just boil down to making the user think before he deletes. The key is simply to make it harder to delete than, say, bumping a button. If you have to right click on the exact record and select the correct menu item, versus just accidentally brushing up against the delete key, there's far less likelihood the user will want to undo what he just did anyway.
Jan 6, 2010 at 8:35 PM Thanks for the feedback, fellas!
@Rob: The idea of destroying any soft-deleted records prior to marking the current record as being soft-deleted is an interesting one, but what happens if the user deletes more than one record in the list (on the same page)? The undo option for the 1st record would no longer work. You could use the Javascript code that triggers the soft delete to remove any other undo options on the page, I suppose, but then you'd want to make it clear to the user up-front that the "undo" option is short-lived.
Part of why I like having the confirmation dialog is that I can use it to explain the consequences of the deletion without having to spell it out on the page itself, taking up visual real-estate.
Really good point about cascading the soft delete. I might actually have a Transfer-based routine that would help with that...
@Trebor: The reason I phrased it to imply that the user could only undo the soft delete while they were still on the page was because I was working under the assumption that the current list page was designed such that it didn't list records that had been soft-deleted, so where would the user go to see a list of soft-deleted records? Todd's "Option 1", providing a special view to list soft-deleted records, would be one way to go about it.
The problem I see with deleting records at the end of a session is in the timeout scenario. If the identity of the user is maintained within the session scope (as it is in many ColdFusion apps), then once the session expires you lose the ability to identify (and hence selectively remove) the records that person chose to delete.
I do use audit tables in those situations where I need to have some sort of version control or when the history of how the record has been changed (and by whom) is important. But I think that when you're designing a scaffolding or code generation system like I'm trying to do, the goal (at least initially) is to produce a set of MVC functions that addresses the most common uses cases, the kinds of views and database transactions that you find yourself writing into every application that you do. Once you've done that, you can use the resulting code as-is for the use cases that fit perfectly, modify the resulting code for those cases where you need to do something slightly different, or write special code from scratch for those records that require a dramatically different approach.
I said at the end of the post that I thought I'd stick with my current strategy, but given your responses, I've decided to give it some more thought.
Mar 12, 2010 at 5:20 PM I always go with soft deletes cause the tables I deal with never get so massive that the accumulated "dead" rows will slow down DB performance. Maybe once a year I'll get permission from the content managers to purge anything marked as deleted, at which point they'll usually be okay with it.
Outside of work I really like Netflix' undo feature. It makes for a great user experience. It's anyone's guess as to how they implement it though. Could be all they do is store the PK of the deleted movie in a cookie. PKs should fit rather easily inside one.