1

I researched about what to choose between soft-delete and hard-delete of rows in a database. I read all the suggestions given on following links:

and came to conclusion that Soft Deletion is the only way to delete data and keep it at the same time if that old data becomes important in future for reasons like audits, recovery etc. And it can be more efficiently implemented if table rows are moved to a backup table that contains same type of columns and some extra information like Who deleted the row? or When was row deleted?.

I don't know what will be the future of my product and my clients not demanding now but may demand old data in future. But to implement Backup Table method of soft-deletion, I have to create clone tables for each table in my database. But there are lots of tables and more tables coming in future. Or if I change the design of one table (like changing data-types or adding columns), I have to change design for its backup table too.

So, my question is that is there any way I can create a single table that can contain the rows of other tables that are deleted? (like a RecycleBin table or something like that).

And I am open to other methods too that can achieve this with performance.

I am using SQL Server 2008 and 2012.

UPDATE

Ok I am suggesting this solution by combining everything you guys suggested:

  1. Original table renamed to A_Main from A
  2. Audit table is A_Audit where deletion metadata is stored. Example: who deleted, when deleted etc with an ID foreign key from A_Main.
  3. A view with the old original table name A is created so that to avoid modification in all queries and stored procedures. This view will select only those rows from A_Main where A_Audit doesn't contain their ID.
  4. Now the operations will be done on this view.

Is this a good way?

Community
  • 1
  • 1
Aishwarya Shiva
  • 3,460
  • 15
  • 58
  • 107
  • use laravel, it has free builtin soft-delete – dynamic Sep 15 '15 at 19:42
  • actually I am using .NET with SQL Server. And I don't know PHP programming. Laravel, I think, is a PHP Framework. – Aishwarya Shiva Sep 15 '15 at 19:44
  • I think you could only create a logical field like `DELETED` in tables and create views from your tables that only return the not `DELETED`registers. I don't know if SQL Server has it, but databases like Oracle have some features like partitioned tables and materialized views that could optimize the register access. – Jean Jung Sep 15 '15 at 19:51
  • Unfortunately this is off topic as any response is going to be based on opinion. There are no right or wrong answers here. Personally I despise soft deletes because there are too many opportunities to get a query wrong and suddenly you are seeing "deleted" data. I would prefer to have a separate table for "deleted" data so it is out of the way of the current data. At the end of the day you have to make a decision and make it work in your system. Sounds like you know the pitfalls of each version. Pick one and go for it. – Sean Lange Sep 15 '15 at 19:57
  • @JeanJung But for that I need to replace all the references of tables with their views in each stored procedure and query. But will it be efficient? I mean still table has data. – Aishwarya Shiva Sep 15 '15 at 19:59
  • @SeanLange Yeah the backup table is the solution but I want to know what big guys do in this situation? Because I have to take this decision now. Because product is not launched publicly yet and when public starts to enter data and delete rows it will become more difficult to manage deleted rows. We don't know if we will allow recovery option in future or use this data later for something else. So what sites like SO do in this situation if they use/used SQL Server? And this is not off-topic because it can have one answer either we can design single table or we can't. But if we can then how? – Aishwarya Shiva Sep 15 '15 at 20:10
  • Yeah, this takes a lot of work, but you can do this in parts and i think it has great benefits, when you add a default value for example, you can simply do a `COALESCE(FIELD, DEFAULT_VALUE)` on the view and it will be the same on the entire application. Remember to index the `DELETED` field and you will probably not face much problems of performance. – Jean Jung Sep 15 '15 at 20:26
  • It is absolutely off topic because it is opinion based. Yes you can do soft deletes in a single table. One way to do that is add an IsDeleted bit column. Then all your queries have to exclude the deleted rows for each and every table. There is no one answer that is sufficient here. – Sean Lange Sep 15 '15 at 20:28
  • I updated my question. Please have a look. – Aishwarya Shiva Sep 16 '15 at 19:11
  • Your update will work as long as the views meet the conditions to make them updateable. This will largely depend on how your original tables were designed. Check out Book Online for the conditions that make a view updateable. – HLGEM Feb 23 '17 at 22:44

1 Answers1

1

Treating this question as academic:

So, my question is that is there any way I can create a single table that can contain the rows of other tables that are deleted? (like a RecycleBin table or something like that).

Only if all of the tables that you want to archive have the same column structure. Otherwise, you need to create a backup table for each of the tables that you want to implement this method of soft-deletion for. Then you either put a trigger on the table to populate the backup table when a row is deleted from the original table, or control deletions through a stored procedure that populates the back up table and deletes from the original table.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • please see my update. I tried a simpler way. But don't know it is efficient or not. – Aishwarya Shiva Sep 16 '15 at 19:11
  • Sorry, the edit ("Is this a good way?") only makes your question more opinion-based, and off-topic. If it works, it's good. If it doesn't work, tell us why and maybe we can help you debug it. – Tab Alleman Sep 16 '15 at 19:29
  • I agree with the above answer by Tab Alleman. Audit tables (based on triggers or 'parallel updates') are a useful approach for capturing changes made in the 'main' table, and this technique is used by a commercial database application I am familiar with (4Series). If the main table has a trigger that updates the audit table, this becomes a simple and elegant solution. It could be argued the question is no longer "too broad", since an answer using a trigger and an audit table could be expressed in a few paragraphs. – Ubercoder Feb 13 '17 at 11:20