Soft Delete mode in database design

A little busy in recent days, so we're going to have a short, simple introduction to a schema in database design--soft Delete.

It can be said that the pattern mixed, and even a lot of people think that the pattern is a anti-pattern. So in this article, we're not just going to introduce this pattern, but we'll also list a series of questions that the pattern might cause to help you make the right decision about whether to use the pattern.

Soft Delete Introduction

First of all, think of a requirement, that is, rollback support for user operations. For example, I am now writing this article in Word. When I perform a wrong operation, I just need to type CTRL + Z to roll back. And in some Web applications, we also need this functionality.

For example, Rally is a Web application that is used to manage progress and tasks during the software development process. In the task management function, each task creation, modification and deletion will be recorded in the system.

Now that's the problem, if you need to support rollback, how can the system record a task that has been deleted by the user? The most intuitive idea is to add a column of deleted to the database to record whether the task has been deleted:

1 @Entity2 classTaskextends..... {3     Private Booleandeleted;4 ...5     Private BooleanisDeleted () {6         returndeleted;7     }8 9     Private voidSetdeleted (Booleandeleted) {Ten          This. deleted =deleted; One     } A}

If a task is deleted, then its deleted will be true. That is, when a user deletes a task, the system does not actually delete the task completely from the database, but only through deleted to indicate that it has been deleted. When you restore the task, you only need to set deleted to false.

OK, this is an introduction to the soft delete mode. Isn't it simple? But it is also because it is very simple, which leads to the abuse of it, so that it becomes a anti-pattern in many people's eyes. This kind of thing happens in it technology quite a lot. The simplest is the Java checked Exception. Indeed, it is a good feature that makes programming with Java more rigorous. However, excessive misuse has led many libraries to expose exceptions that are completely beyond the user's disposal to the class library interface, instead causing many software developers to develop a bad habit of ignoring all exceptions directly:

1 Try {2    obj.somefunction (); 3 Catch (Throwable e) {4 }

It is believed that the reader has seen the danger of doing so: catch catches even instances of error types that represent system errors. But one thing that cannot be overlooked here is that when a software developer is powerless about something, it is highly likely that he ignores some coding criteria, and first chooses to use a method that allows the system to function properly. For example, the above function call Obj.somefunction (), if it throws an exception that is related to the runtime logic inside the class library, and each time it can cause this problem, then it is highly likely that the software developer will use the above code to ignore the exception. This problem is even present in some widely used library of classes. For example, odata4j used to treat all exceptions resulting from the acquisition of OData metadata as if the target service had not exposed the metadata.

OK, that's a bit far. In summary, once a technology is too simple and can handle a situation, software developers will not be careful to study the context in which the technology is used, leading to abuse. Like checked exception, Soft Delete is an example of this.

Soft Delete the problem

So what's wrong with the database schema? To put it simply, it is too error-prone and covert. Imagine that if the user needs to list all the tasks, then in the SQL statement you need to use the Where deleted = ' N ' condition. And the condition appears in almost all SQL statements that handle the task. Once the condition has been forgotten in an SQL statement, this is most likely a bug, and the bug is sometimes very covert. For example, if you forget to mark the condition in a count statement and there are many tasks in the system, but the task is rarely removed, the bug may not be discovered for several years.

At the same time, if you decide that you need to use soft Delete extensively in your system, SQL will become very promiscuous. In the statistics function, we may need to filter out the number of use cases that contain the task, even the number of items that contain these use cases, then we need to identify multiple where deleted = ' N ' conditions in sql:

1 SELECT COUNT (*) 2  from project, story, Task 3 WHERE =  and =  and = N

So when debugging these statements, or looking at the execution plan of these statements for performance tuning, software developers will find that the introduction of these conditions makes SQL execution very complex.

Another problem is that if a system often performs a soft deletion of records, the data recorded in the database will be much more than the data that is actually required to be recorded. This junk data can cause database indexes to become large and may even severely affect the performance of the database.

Another issue is related to cascading. The database provides cascading operations, and when a data record is deleted, the database automates the operation of other associated records based on the correlation between the data and other records. This is also a way for the database to maintain its data integrity. But once the user uses soft delete, it is not removed from the database when it is soft-deleted, and the records associated with it are not removed by the database. That is, software developers need to do their own management of data integrity. In addition, software developers need to complete the organization of transactions in the data Access layer (Dal,data access layers), and the logic of these transactional organizations may need to be changed once the definition of the database tables has changed.

Soft Delete the implementation

Because soft delete has so many problems, software developers have proposed a number of workarounds for soft Delete, greatly reducing the cost of developing and maintaining soft Delete mode data.

One approach is to take advantage of the view functionality provided by the database. In this method, we need to create a view in the database to display the rows of data with the deleted value ' N ' in the table. In each SQL statement that operates on the data, we only need to manipulate the view directly, thus avoiding the requirement to indicate where deleted = ' N ' in the SQL statement every time.

The other approach is to spread the data across two different tables. One of the tables in both tables records rows of data with deleted values of ' N ', while the other table records rows of data that have been soft-deleted with a deleted value of ' Y '. And in the case of two tables, the system can even easily implement the function of the garbage bin.

In some cases, we can also draw on the idea of soft delete in database design. Soft Delete requires the user to manage the association of data in the database themselves. This is an extra job, but it also brings more flexibility.

Rollback of a task delete operation in rally naturally needless to say, the addition of trash can also become very easy. For some custom deletion logic, the flexibility of Soft delete is more pronounced. For example, in rally, if we need to implement "Delete user use cases if the user use case contains tasks, then those tasks will be moved to the parent use case" such a requirement, then we can complete the function in the soft delete's custom delete logic.

All right, here we go today.

Soft Delete mode in database design