Best way to store deleted rows

Question

I have a table with operations that can be deleted by users.

What happens, is that I want to store deleted rows for some time, to serve as recover in case any client regrets the deletion.

One of the columns is id_user, so I had two ideas:

• create another table equal to the first one to store the deleted rows (the problems is to keep them equal, so any changes in the first one I have to remember to do on the second);

or

• update the rows setting id_user=-id_user, so that the records are not listed to this user but they are still related and I could recover them if I wanted (the problem is to store different stuff [valid and backup records] in the same table);

What is the best aproach?

PS: These records are called in a huge bunch of places in my application, adding another column implies in changing my aplication and that is out of question. If that ended up being the only solution I'd rather not to add this 'disaster recovery feature'.

Answer 1

I usually set it as a trigger and store changes in a separate table. Keep the id_user as a FK in the table and throw in a date field to record when the change was made. Then you can recover by date even if you don't know the exact date.

Answer 2

I wrote about something like this on Mar 05, 2012 (Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios)

Basically, you can do the following:

• Create a new TINYINT column called DELETED with a default value of 0. When you intend to delete a row, simply update the row with DELETED=1. Do not index this column since the cardinality would be too low.
• To undelete a row, you can simply update the row with DELETED=0.

As a option, you can create a tombstone table with all the known deleted user_ids.

DROP TABLE IF EXISTS deleted_users;
CREATE TABLE deleted_users
SELECT DISTINCT id_user FROM myusers WHERE DELETED=1;
ALTER TABLE deleted_users ADD PRIMARY KEY (id_user);

You could then query for delete data as follows

SELECT * FROM myusers
WHERE DELETED=1;

or

SELECT B.* FROM
myusers A INNER JOIN deleted_users B
ON A.id_user = B.id_user;

To do a permanent purge of the flagged deleted data you then run

DELETE FROM myusers
WHERE DELETED=1;

or

DELETE A.*,B.* FROM
myusers A INNER JOIN deleted_users B
ON A.id_user = B.id_user;
TRUNCATE TABLE deleted_users;

CAVEAT / DISCLAIMER

Please backup your data before trying this. Experiment with this in Dev/Staging