Data Management: logical and physical deletion

You're on a meeting with your team and you're talking about the next feature on you application: the app interface must let the user delete an information. The first thought you may think is: Ok! Let's make a "DELETE" query for this case, it solves the problem! So your team starts to work on the solution.

The problem on DELETE implementation

The Front End Dev develops the user interface, sending you an ID within the request for the data deletion.

You, as a Back End Developer, captures the ID, verify if it exists and them run the "DELETE FROM my_schema.my_table WHERE id = ?". Nice! Problem solved, isn't?

The feature goes to production and the user starts to consume your latest release. On just one day, the user starts to delete data, and at the end of the day he discovers that he wasn't suppose to delete this information, he was mistaken about it.

Of course the team will ask you if it's possible to recover the deleted data, and you start to think about how will you recover it. I'm not even thinking about the scenario you don't have a Backup, because if that's the case, my friend... let's not think about it now ??

Now you need to start the dump creation before the moment the user deleted the data you want, and you'll need to recover specific information to insert again. This could take a lot of time, but instead of doing this, let's think in a better (and simple) solution!

When you use the DELETE query, you're taking out the data physically from your database. While doing this you'd need to recover with a previous dump, thinking about adding a new column to your table may be a valid solution for you. Lemme show you an example for this scenario.

First Step: create a new column "statusID"

This is my example table, the "users" table:

I want to delete information on my application, but I want to be easy to recover. Let's add a new column called "statusID", linking to another table called "status".

ALTER TABLE users.users ADD statusID INT DEFAULT 1 NOT NULL AFTER id;

Second Step: create a link with the "status" table

Nice! Now we have a new column. Let's link with the other table, the status table:

ALTER TABLE users.users ADD CONSTRAINT users_has_status_FK FOREIGN KEY (statusID) REFERENCES `general`.status(id);

PS: I'm assuming you've created the status table with an ID, at least, for the foreign key to work. I'm assuming you've created at least two registries on your status table too, the "Active" and the "Inactive" registries.

So, how this changes our previous situation?

The solution

Now, if you want to delete the user, you just need to set the statusID to "2", the inactive status. If you need to list the active users, make a condition to select all users where the status ID is equal to "Active", or "1" on my case.

By doing the "deletion" like this, you're deleting data logically instead of physically, its easier to maintain your data if you need to recover.

The possible negative point is that you don't delete the data physically, of course. This may lead your database to grow in a size you'll know just considering all your data volume with the team. You must chat with everyone involved on the tech decisions to make it work on the best way possible.

Hope you enjoyed the content, bye! =D