You should avoid using Soft Delete when possible, and here’s why

Tram Ho

People make mistakes all the time . So, “worrying away” before all the worst situations is never redundant.

Cases like the administrator’s negligence accidentally deleting, logic errors in the source code, or when the admin page is occupied and intentionally destroyed by hackers, … In those cases, if the system is successful Pre-setting the Soft Delete feature – also known as soft delete – will greatly reduce the risk.

However, is there no point in applying Soft Delete? Why do I personally dislike Soft Delete , and when will it become a hindrance in your project? In this article, I would like to join you to learn more about Soft Delete, and understand its strengths / weaknesses.

You should avoid using Soft Delete when possible, and here's why

I. Brief explanation about Soft Delete

Soft Delete (roughly translated in Vietnamese as “soft delete”) is as easy to understand as its name. When you do delete a certain data (articles, messages, …), instead of deleting the actual data from the database, the system will simply “hide” it . This is done by marking a field in the table with the name is_deleted (if set to boolean) or deleted_at (if you prefer null | datetime). Those marked deleted records will no longer be shown outside.

Schema of a table using Soft Delete (soft delete)

One key reason Soft Delete is so popular is that it’s incredibly easy to set up with no effort at all. Just add the deleted_at column to the table you want to soft delete, you are finished 90% of the work, then just edit the deletions becomes the UPDATE statement marked in the soft delete column. But with modern web frameworks, this step is too easy. Laravel already supports Soft Delete at ORM level, and with Ruby on Rails just install an additional gem like discard .

II. Soft Delete “annoying” than you think!

At first it’s simple, but in the long run, the more problems that Soft Delete arises that you need to solve:

Unique Index and Primary key

A rather annoying case is when you Soft Delete a record whose field is marked as a Unique index in the database. For example, in the users table with the username field has a unique index, for some reason the account is deleted, and then someone else wants to register a new account with the same username. At this point, when a user registers a new account, the ORM side will still understand that the account name is still registered and pass the validate process as usual. But because the record is not completely deleted from the database, an index match error in the username field occurs on the database side.

There are 2 ways to handle this:

  • Remove the Unique index from the username field and depend entirely on validate from the app side to ensure the username is unique.
  • Creates a multi-column unique index between the username and deleted_at fields.

Both of the two methods above are not very beautiful because they either affect the integrity level, or cause more complications and problems for the database.

The problem of deleting the cascade

With the Rails gem discard or the soft delete feature available in Eloquent, you can enable soft delete for any individual table / model:

But what if a Post has many comments ? Currently, either discard or soft-delete of Laravel does not handle the deletion of related records. Many problems will arise, for example, it is difficult to count COUNT total comments correctly, or if your site has the “Latest Comments”, the comments of the article was deleted will appear there .

To handle this, you need to add Soft Delete for the Comment model as well, and use the life-cycle hook / callback in the Post panel to soft delete the comments after deleting the post. For consistency, once you have used soft delete in a table, it is best for all remaining tables in your database to enable soft delete as well.

While this is a solvable problem, it also adds a ton of complexity to your code.

The queries, JOIN more difficult

This goes without saying, especially in case you need to write pure SQL syntax and not use ORM. Almost every query or join will need to add WHERE deleted_at IS NULL , which makes the query look confusing and easy to miss / confuse.

But the reason for my “difficult query” is only a small part of the bigger problem: I am annoyed with the existence of deleted_at fields in tables, and I hate that normal and deleted records have been deleted. be mixed together right in the same table. It does something unscientific / not “clean” and does not go well with the usage of a relational database.

Soft Delete is also not completely secure

Soft Delete helps to reduce the risk of deletion and easily recover data immediately after deletion, but it is not possible to tell who the deletion was unless you further developed this feature.

On the other hand, Soft Delete is also not reserved for protection against mistakes during UPDATE .

III. Is there an alternative to Soft Delete?

It is difficult for any other solution to win against Soft Delete at a convenient level, but I can think of a few ideas as follows:

Create twice as many boards

For each table in the database, create another table with the same schema, dedicated to storing deleted records. For example, if I have users table, I will create another table deleted_users with almost the same schema . When a user is deleted, I will delete that user in the users table and create a new one in the deleted_users table.

The downside is that the number of tables in the database will become twice as much, and there is added complexity of ensuring that the schema of the users and deleted_users tables are the same.

Serialize data and save activity history

With this way, every time you apply a change to any record (whether it is update or delete), then re-serialize the model, or convert the fields in the record to JSON and save it to a different table with the name. Maybe activities or histories .

The big advantage of this approach is that you only need a single table to save the changes, and you can see the history of each change / delete (and you can also save the agent if you want). However, although feasible, it will be much more difficult than a regular soft delete if you need to restore data.

IV. Explore more

Share the news now

Source : Viblo