46

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.

Improve this question
3
  • 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
    Commented Jan 4, 2011 at 7:52
  • What database are you using?
    – Evan Carroll
    Commented Dec 23, 2016 at 20:40
  • Temporal Table is a best solution for SQL Server 2016 and above.
    – Sameer
    Commented Jul 31, 2019 at 12:17

9 Answers 9

Reset to default
39

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 stored 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.

Improve this answer
5
  • 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
    Commented Jan 4, 2011 at 9:03
  • 16
    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
    Commented Jan 4, 2011 at 13:30
  • 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
    Commented Jan 19, 2011 at 1:46
  • Note: delete is a reserved word in MySQL.
    – Jason Rikard
    Commented Jul 13, 2011 at 16:59
  • Remember that a filtered index on your deleted field can greatly improve performance when you're querying for nondeleted rows
    – Ross Presser
    Commented Aug 1, 2019 at 17:01
23

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.

This answer has been around a few years and a couple of key things that could affect this sort of planning have changed since then. I'll not go into massive detail, but breifly for the benefit of people reading this today:

  • SQL Server 2016 introduced "system versioned temporal tables" which do a lot of this work for you, and more besides as some nice syntactic sugar is provided to make historic queries easier to construct & maintain, and they coordinate a subset of schema changes between the base and history tables. They are not without their caveats, but they are a powerful tool for this sort of purpose. Similar features are also available in other DB systems.

  • Changes to data protection legislation, particulaly the introduction of GDPR, can significantly alter the matter of when data should be hard deleted. You have to weigh up the balance of not deleting data that might be useful (or, indeed, legally required) for auditing purposes at a later date against needing to respect peoples rights (both generally and as specifically set out in relevant legislation) when considering your designs. This can be an issue with system versioned temporal tables as you can't modify the history to purge personal data without schema short term changes to turn off the history tracking while you make changes.

Improve this answer
2
  • How do you deal with deletion and renaming of columns? Set everything to nullable?
    – Stijn
    Commented Feb 12, 2013 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
    Commented Feb 13, 2013 at 14:15
9

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

Improve this answer
7

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.

Improve this answer
4

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.

Improve this answer
3

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.

Improve this answer
1
  • It's not just a matter of a view. Any operations being performed on the set would have to exclude the "deleted" records.
    – Abie
    Commented Jan 16, 2011 at 20:45
2

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.

Improve this answer
1

I am used to seeing table rows with columns like 'DeletedDate' in them and I don't like them. The very notion of 'deleted' is that the entry should not have been made in the first place. Practically, they can't be removed from the database but I don't want them in with my hot data. Logically deleted rows are, by definition, cold data unless someone specifically wants to see deleted data.

Furthermore, every query that is written has to specifically exclude them and indexes need to consider them as well.

What I would like to see is a change at the database architecture level and the application level: create a schema called 'deleted'. Each user-defined table has an identical equivalent in the 'deleted' schema with an extra field holding metadata — the user which deleted it and when. Foreign keys are requiring to be created.

Next, deletes becomes insert-deletes. First the row to be deleted is inserted into its 'deleted' schema counterpart. The row in question in the main table can then be deleted. Extra logic does, however, need to be added in somewhere along the line. Foreign key violations can be handled.

Foreign keys have to be properly handled. It is bad practice to have a row logically deleted but whose primary/unique has columns in other tables which refer to it. This shouldn't happen anyway. A regular job can remove widow rows (rows whose primary keys have no references in other tables despite the presence of a foreign key. This is, however, business logic.

The overall benefit is the reduction of metadata in the table and performance improvement it brings. The column 'deletedDate' says that this row shouldn't actually be here but, for the sake of convenience, we are leave it there and let the SQL query handle it. If a copy of the deleted row is kept in a 'deleted' schema, then the main table with the hot data has a higher percentage of hot data (assuming it is archived in a timely fashion) and fewer unnecessary metadata columns. Indexes & queries no longer need to consider this field. The shorter the row size, the more rows can be fitted onto a page, the faster SQL Server can work.

The main disadvantage is the size of the operation. There are now two operations instead of one as well as the extra logic and error-handling. It can lead to more locking than updating a single column otherwise would take. The transaction holds locks on the table longer and there are two tables involved. Deleting production data, at least in my experience, is something rarely done. Even still, in one of main tables 7.5% of almost 100 million entries have an entry in the 'DeletedDate' column.

As an answer to the question, the application would have to be aware of 'undelete's. It would simply need to do the same in reverse order: insert the row from the 'deleted' schema into the main table and then delete the row from 'deleted schema. Again some extra logic & error handling is needed to ensure to avoid errors, problems with foreign keys and the like.

Improve this answer
0

You might alternatively place the onus on the users (and developers) and go with a sequence of 'Are you sure?', 'Are you definitely sure?' and 'Are you abolutely, well and truly sure?' questions before the record is deleted. Mildly facetious but worth considering.

Improve this answer
0

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.