How to delete from a database?

Question

I know of two ways to delete data from a database table

• DELETE it forever
• Use a flag like isActive/isDeleted

Now the problem with isActive is that I have to track everywhere in my SQL queries that whether the record is active or not. Using DELETE however gets rid of the data forever.

What would be the best way to backup this data?

Assuming I have multiple tables in a database, should I have a common function which just backs everything up and stores it in another table (in XML probably?) or is there any other way.

I am using MySQL but am curious about techniques used in other DBs as well.

Answer 1

Replace the table with a view that hides the inactive items.

Or write a trigger on DELETE that backs up the row to an archive table.

Answer 2

You can partition your tables on the DELETED column and define the views which would include the condition:

… AND deleted = 0

This will make the queries over the active data just as simple and efficient.

Answer 3

You could use a trigger that fires on deleting records to back them up into some kind of graveyard table.

Answer 4

Well, if you were using SqlServer you can use triggers, which will allow you to move the record to a deleted table.

Answer 5

You could use an isDeleted column and defien a view which selects all columns except isDeleted with the condition isDeleted=false. Then have all your stps work only with the view.

Answer 6

You could maintain a history table, where you back the record up and time stamp

Answer 7

What is your goal? It's not backup, in the sense sysadmins use that term: you don't want to replace your current data with the old data in case the current data are lost. Perhaps it's archiving, but for what purpose? Will you ever need to query the archived ("deleted") data? How complete does the archiving need to be, do you want to be able to reconstruct every past state of the database that has every existed? It's a bit hard to suggest a solution without knowing the problem.

Answer 8

One of the biggest reasons for not deleting data is that it may be required for a relation - for example the the user may decide to delete an old customer from the database, but you still need the customer record because it is referenced by old invoices (which may have a much longer lifespan).

Based on this the best solution is often the "IsDeleted" type of column, combined with a view (Quassnoi has mentioned partitioning, which can help with performance issues that might pop up due to a lot of invisible data).

Answer 9

Here's a relevant blog post about Soft Delete (using delete flags).