0

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

Improve this question
2
  • 1
    You appear to be interested in developing a database with temporal features. If that's the case, you might find Q & A no. 1 and Q & A no. 2 of help (and both propose an approach that looks similar to your first idea, by the way).
    – MDCCL
    Commented Aug 7, 2017 at 18:43
  • @JoeW I've added some details of why your suggestion doesn't suits me. And regarding the clients, this 'feature' was request by a user after he messed up and deleted stuff by mistake.
    – carla
    Commented Aug 7, 2017 at 18:55

2 Answers 2

Reset to default
2

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.

Improve this answer
4
  • Regarding the tables, do you use any method to keep them structuraly equal? When you add a column to the first do you manually change the second?
    – carla
    Commented Aug 9, 2017 at 17:44
  • I set up a second table with all the same columns and add one for date and user so I know who made the change and when. Then just set a trigger to move the row to the new table and add in the additional fields.
    – DCook
    Commented Aug 9, 2017 at 19:49
  • What I meant was, if next year your table 1 structure is changed (e.g. you add one column), do you manually change the second ou do you have a trigger to do that too?
    – carla
    Commented Aug 9, 2017 at 19:57
  • Depends on how you want to do it. Sometimes, I have included the entire record on the secondary table in which case it too would need to be updated to reflect the change in table one. Others I have simply kept the date, time, whom, table name, field name and the original field value. But that was for a specific database which had multiple tables. So each change generated a new row in the secondary table. Mostly because things in the originals were never supposed to change. It was scores on a variety of exams for a company and they suspected someone was tampering.
    – DCook
    Commented Aug 10, 2017 at 11:51
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

Improve this answer
5
  • The transition is not a concern, my doubts were about best practices. Since you know the stuff, why is the second approach not recomended?
    – carla
    Commented Aug 9, 2017 at 18:02
  • If id_user is indexed in any way, there can a significant performance hit doing mass deletes (i.e., changing id_user to -id_user on hundreds of rows in a single operation, causing all indexes to reorder their leaf pages, possibly making the index rather lopsided, warranting having to do ANALYZE TABLE to stabilize the index statistics).
    – RolandoMySQLDBA
    Commented Aug 9, 2017 at 18:08
  • So if there is no mass deletes, it could be used?
    – carla
    Commented Aug 9, 2017 at 18:16
  • 1
    You could. Your second approach has two advantages: 1) no additional column and 2) no additional table. It may be the better approach in terms of implementation. Nevertheless, there are may be some issues down the road if id_user is indexed. Mass deletes is just one example. Index lopsidedness is another. If id_user is not indexed, then your second approach is OK to do.
    – RolandoMySQLDBA
    Commented Aug 9, 2017 at 18:54
  • Won't adding the PK after populating the table be slower (with InnoDB)? Especially since the SELECT will probably deliver the rows in PK order. Note that you can do CREATE TABLE t ( ..., PRIMARY KEY(...) ) SELECT ...;
    – Rick James
    Commented Aug 16, 2017 at 0:16

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.