Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are actually deleting the record in question and storing the changes in a separate audit table, or not deleting the record and using a boolean "deleted" column to mark it as deleted. The latter solution would require additional application logic to ignore the "deleted" records under normal circumstances, but would make it much easier to implement recovering the records on the application side.

share|improve this question
    
In response to sredzy and poelinca, yes, I forgot to mention that in the second case the flagged records would need to be deleted or moved after some reasonable elapsed time period. –  Abie Jan 4 '11 at 7:52
    
This seems more like an application/database development question than a DBA question. –  ScottCher Jan 4 '11 at 21:42
8  
DBAs are not precluded from understanding database design. –  Abie Jan 5 '11 at 1:40
1  
I didn't mean to imply that. The first line of the question "implement an 'undelete' feature in a web application" says to me this is an app development question, not a database administration question. –  ScottCher Jan 5 '11 at 14:28
2  
I don't like to see things get deleted ever. Think about order records, you don't want to ever have an unassociated product detail record, ya know? Or addresses being separated from the individual who lived there, etc. –  jcolebrand Jan 6 '11 at 15:47

7 Answers 7

Yeah, I would definitely go for the second option, but I would add one more field a date field.

So you add :

delete       boolean
delete_date  timestamp

It would let you give a time for the undelete action.

If time is less than an hour one can undelete. To really delete the entry deleted just create a store procedure that will clean every entry with delete set to true and time greater than one hour and put it as a cron tab that runs every 24hours

The hour is just an example.

share|improve this answer
    
Alternatively, you could have another flag - cleaned, or something - which indicates that the data associated with this record has been properly, comprehensively deleted. The record can be undeleted unless cleaned is true, in which case it is unrecoverable. –  Gaurav Jan 4 '11 at 9:03
10  
This is the common approach. I usually use one field deleted_at holding both the semantic of the delete boolean and the delete_date timestamp. If deleted_at is NULL handle the case delete is FALSE and delete_date is NULL, deleted_at containing a timestamp handle the case delete is TRUE and delete_datecontains a timestamp, saving you a time, storage and application logic. –  Julien Jan 4 '11 at 13:30
    
I agree w/ Julian -- no need for the extra field. But I'd only use this technique for the case where it's just simply 'undelete' and not kept for some auditing purpose, where I'd go with David Spillett's method. –  Joe Jan 4 '11 at 14:52
1  
I like the boolean and date field. Depending on how you implement the deletion logic you could even have a distinct table which holds the date and the unique key for the record that was "deleted". Stored procedures make this easy. It takes the additional space per row required down to 1 bit vs. 8+. You would also be able to report on deletions per day without touching the source table. –  AndrewSQL Jan 19 '11 at 1:46
    
Note: delete is a reserved word in MySQL. –  Jason Rikard Jul 13 '11 at 16:59

In our applications we don't really delete anything at a users request anyway (our clients are in regulated environments where deleting anything can potentially lead to legal issues).

We keep the older versions in a separate audit table (so for the table some_table where is also a table called some_table_audit) which is identical apart from having an additional version identifier (a timestamp if your DB suports time values granular enough, an integer version number or UUID that is a foreign key to a general audit table, or so on), and update the audit table automatically by trigger (so we don't need to make all code that updates the records aware of the audit requirement).

This way:

  • the delete operation is just a simple delete - no need to add any extra code to that (though you might want to record who requested what rows to be deleted, even if they are not actually deleted)
  • inserts and updates are similarly simple
  • you can implement undelete or revert by just returning the "normal" row to an old version (the audit trigger will fire again so the audit trail table will reflect this change too)
  • you can offer the chance to review or revert to any past version not just undelete the last one
  • you do not have to add "is marked as deleted?" checks to every code point that refers to the table in question, or "update audit copy" logic to every code point that deletes/updates rows (though you need to decide what to do with deleted rows in the audit table: we do have a deleted/not flag for each version there so there isn't a hole in the history if records are deleted and later undeleted)
  • keeping the audit copies in a separate table means you can partition them off into different filegroups easily.

If using a timestamp instead of (or as well as) an integer version number, you can use this to delete the older copies after a set amount of time if needed. But disk space is relatively cheap these days so unless we have reason to drop old data (i.e. data protection regulations that say you should delete client data after X months/years) we wouldn't.

share|improve this answer
    
How do you deal with deletion and renaming of columns? Set everything to nullable? –  Stijn Feb 12 '13 at 15:12
1  
@Stijn: It isn't often that the structures are changed so that doesn't come up much. Colunms are generally never removed once they've existed in production - if they stop being used just drop any constraints that would stop them benig NULL (or add defaults to deal with constraints by using a "magic value", though that feels more dirty) and stop referring to them in other code. For renames: add new, stop using old, and copy data from old to new if needed. If you do rename columns just make sure the same change is made to both base and audit tables at the same time. –  David Spillett Feb 13 '13 at 14:15

With a boolean deleted column , you'll start to have problems if you're table start's to grow and get's realy big . I suggest you move deleted columns once a week ( more or less depending on you're specs ) to a different table . That way you have a nice small active table and a big one containing all records gathered over time .

share|improve this answer

I'd go with the separate table. Ruby on Rails has an acts_as_versioned plugin, which basically saves a row to another table with the postfix _version before it updates it. While you don't need that exact behavior, it should also work for your case (copy before deleting).

Like @Spredzy I'd also recommend adding a delete_date column to be able to programatically purge records that haven't been restored after X hours/days/whatever.

share|improve this answer

The solution we use internally for this matter is to have a status column with some hard coded values for some specific states of the object: Deleted, Active, Inactive, Open, Closed, Blocked - each status with some meaning used in the application. From db point of view we don't remove objects, we just change the status and keep history for each change in the object table.

share|improve this answer

When you say that "The latter solution would require additional application logic to ignore the 'deleted' records", the simple solution is to have a view which filters them out.

share|improve this answer
    
It's not just a matter of a view. Any operations being performed on the set would have to exclude the "deleted" records. –  Abie Jan 16 '11 at 20:45

Similar to what Spredzy suggested, we use a timestamp field for deletion in all of our applications. The Boolean is superfluous, as the timestamp's being set indicates that the record has been deleted. This way, our PDO always adds AND (deleted IS NULL OR deleted = 0) to the select statements, unless the model explicitly requests deleted records be included.

We don't currently garbage collect on any except tables that contain blobs or texts; the space is trivial if the records are well normalized, and indexing the deleted field makes for limited impact on the select speed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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