0

Using SQL Server 2012, with over 200 tables. I need to implement soft-delete so that I can give my users the ability to "restore or undelete" deleted records. The tables from which the users delete records are user-defined with variable number of relationship (foreign keys) to other tables.

I have seen so far two methods:

  1. IsDeleted column on all tables
  2. Having mirror archived tables

however both methods seems to work well on a standalone table, when it comes to tables with relationships, I think neither of the methods are practical.

Method 1: complicates queries, and causes in incorrect query results.

SELECT Id,Name FROM User
Left Join Group ON Group.Id = User.GroupId AND Group.IsDeleted=0
WHERE User.IsDeleted=0 AND ISNULL(Group.IsActive,0) <> 0

This above query returns invalid results because of the joined table IsDeleted filter. In other posts, it is suggested that IsDeleted=0 is enough, however as I demonstrated above that is not the case.

SELECT Id,Name FROM User
Left Join Group ON Group.Id = User.GroupId
WHERE User.IsDeleted=0 AND Group.IsDeleted=0

This above query too, won't work correctly in some cases depend on how the user filters the records (the problem appears when there are more joins). In below example: User has GroupId field (lookup to Group) User has RoleId field (lookup to Role) Role has GroupId field (lookup to Group)

SELECT Id,Name FROM User
LEFT JOIN Group ON Group.Id = User.GroupId
LEFT JOIN Role ON Role.Id = User.RoleId
WHERE User.IsDeleted=0 AND Group.IsDeleted=0 AND Role.IsDeleted=0
AND /* ...user defined filters...*/ Role.Name is not null

The query above too won't return the correct results if the group is deleted, but not the role related to the group, the role record will also get filtered out.

(I know there are ways to fix the queries in case by case basis, but that would not work for my use case, I need a solid pre-defined pattern to apply to all generated queries - the database queries are not predefined and they are generated on the fly.)

Method 2: very hard to implement, at the time that a record is being deleted and archived, need to figure out what other records in other tables reference it and track them somehow to be able to update them back when the record is undeleted.

Anyone has a suggestion what is the best method to implement soft delete in a relational database effectively?

sam360
  • 1,121
  • 3
  • 18
  • 37
  • See also: http://dba.stackexchange.com/questions/25548/separate-archive-tables-or-soft-delete-for-inventory-database, http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea, http://stackoverflow.com/questions/378331/physical-vs-logical-soft-delete-of-database-record, etc. – Lukas Eder Jun 07 '15 at 21:12
  • @LukasEder, I have read all these threads and discussions, however I am not convinced if anyone actually provided a working solution to the problem I identified above, hence why I posted it. – sam360 Jun 07 '15 at 22:56
  • Does your user need only the ability to restore deleted records, or do you need to allow selecting both deleted and undeleted records at the same time? – Zohar Peled Jun 08 '15 at 06:09
  • @sam360: You may be right, but the fact that a similar question has been asked so many times with so many answers and still, the "right" answer has not yet been found (for you) shows that the question isn't really a good fit for Stack Overflow's Q&A style, see http://stackoverflow.com/help/dont-ask – Lukas Eder Jun 08 '15 at 08:22
  • - The user has to be able to "Restore" deleted records. - It would be good to allow the administrator query deleted and undeleted records together, but on this item I have flexibility, I can provide separate API to access deleted records – sam360 Jun 08 '15 at 18:11

0 Answers0