I believe in the user experience of always having a confirm pop-up for deletions. However (though we're very used to it), a separate small pop-up window is not ideal and for users on different browsing devices, using different input devices, they can be a nightmare.

So I was thinking of changing all my 'confirm delete?' pop-ups into NOT having a pop-up, deleting the record immediately BUT then offering a link to restore the record on whatever page they user is put on after the 'delete'.

This absolutely requires that I update the application so that instead of deleting the record, it is either marked as deleted, or moved to a 'deleted_original-table-name' and thus in either case can be deleted.

There may then be an 'admin' process available to periodically delete them permanently.

I am looking for thoughts on why this might be a bad idea and any pitfalls to watch for.

I'd like to know if there are any advantages/disadvantages to moving it to a separate table vs. marking it has deleted.

  • 2
    I'd update the application to use soft delete even if you decide to keep the question. Commented Aug 26, 2014 at 16:06
  • 1
    A unique index is the biggest pitfall with softdelete I encountered so far. Commented Aug 26, 2014 at 16:07
  • Thanks CodesInChaos, could you expand a little more? What defines a 'soft-delete' ? Marking the records? What's the deal with the unique index. Is the danger if primary keys are reused or the index gets too big, or ? Commented Aug 26, 2014 at 16:12
  • soft delete is marking a record as "to be deleted on a later date". Hard delete is carrying out the actual deletion. Commented Aug 26, 2014 at 16:13
  • 1) Both a flag and moving it are forms of soft deletion, since the data is still available. A flag is the conventional implementation. 2) I'm talking about reusing a value on a field which has a unique index (usually not the primary index) once the record occupying it has been deleted. Commented Aug 26, 2014 at 16:16

2 Answers 2


I would think of the problem in the opposite way. Instead of "soft delete" think of it as an archive flag. You should never plan on restoring the record, since you will either have to circumvent your rules for new records, which may have changed since this record was created, or allow the record to slip past your "new record" business logic and possibly violate uniqueness rules and other such guidelines that are not established as database constraints.

I don't see a "restore" feature working very well, in fact working worse and worse as your business changes (but your "soft delete"-ed data stays the same). Unless you make all the plans for the undelete feature at the same time you design the soft delete feature and test it (as well as continuing to test it through time).

What's more realistic is that you will create an "archive" system that sits beside your production system. Every so often, archived rows will be sent there. When they are needed they can be swivel-chair entered into the new system by a data entry clerk, effectively being recreated from scratch.

It should be noted that either design will work for such a scenario.

  • +1 Usually I would be restoring against the same data model within a few hours or days but this is a good point - Occasionally the model would have changes and this wouldn't work. Keeping the records in the base table avoid this problem. So +1 Commented Aug 26, 2014 at 20:30
  • It's not really up to you when it's restored, it's up to the user. It's also not up to you when the data model or validation rules change. It is up to you to set expectations. So, if you provide an "undelete" UI element, the user is going to expect that to work and they will blame you when it doesn't. You will also blame you because the error will be as varied as the reasons to change something would be. Best off calling it an "archive" flag. Commented Aug 26, 2014 at 20:50

TBH I wouldn't move it to a separate database or table as that would just be an extra layer of complication.

In my last job, who developed and maintained enterprise solutions for large clients, we would leave the records in place, just using a bit field/boolean to hide it from the user. A user with higher privileges could come along and then undelete a record if it was done in error.

After a period of time, a deletion service would run which would then do a hard delete on these records. You could add a "DeletedOn" field to decide whether a record was deleted long enough ago to justify a hard delete.

I'm going to be implementing something similar in my new project soon.

  • Thanks! The one thought I had on moving them to another table is that this would actually require the least change because I would NOT have to change all existing queries to now 'ignore' those records. Any thoughts on that ? Commented Aug 26, 2014 at 16:14
  • I'd implement it - it's fairly straightforward to add "&& not record.markeddeleted = 1" to all your stored procs (if that is what you're using) Commented Aug 26, 2014 at 16:42
  • any reason for the downvote? Commented May 19, 2015 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.