Geek Answers Handbook

Best practice for enabling "restore" for database objects?

This is for a CRM application using PHP / MySQL. Various objects, such as a client, contact, note, etc., can be "deleted" by the user. Instead of actually deleting the entity from the database, I just want it to be deleted in the application, but stored in the database and can be "restored", if necessary, at a later time. Perhaps even add some kind of "basket" to the application.

I thought of several ways to do this:

  • Move the deleted object to another table. (client to customer_deleted)

  • Change the attribute of the object. (false enabled)

I am sure that there are other ways, and each of them has its own consequences for the size of the database, performance, etc. I am just wondering what is the usually recommended way to do something like this?

+4
source share
2 answers

I would take a combination of both:

  • Set the deleted flag to true
  • Use cronjob to move records after a while to a tab like ARCHIVE
  • If you need to restore the entry, select into article table and delete it from the archive

Why would I go like this?

  • If the client deleted the wrong one, recovery can be performed by instand
  • In a few weeks / month, the table of articles can grow to a large number, so I would archive all the records that were deleted in 1 week pa
+1
source

It is common practice to set the deleted_at column to the date that the object was deleted by the user (default is null). You can also include the deleted_by column for the label that deleted it. Using any remote column makes it easier to work with FK relationships since they will not break. By moving the row to a new table, you will need to update FK (and then update it again if you ever restore it). The disadvantage is that you have to ensure that all your queries exclude deleted rows (where this would not be a problem if you moved the row to a new table). Many ORMs make this filtering easier, so it depends on what you use.

0
source

More articles:


All Articles