In the real world, some documents become obsolete and are immediately shredded, while others need to be preserved in archives for future reference.
Consider an order, which references many other pieces of information, such as seller details, buyer details, and product details. When a product is removed from the catalog, we can’t directly delete the product information; doing so would render the associated order information incomplete.
For tables that require logical deletion (referred to as “collections” in MongoDB), there are two methods:
- Method 1: Add fields like
is_deleted or is_active to indicate whether a row has been logically deleted.
- Method 2: Create a corresponding archive table (recycle bin table) for each table that needs logical deletion. First, insert the rows to be deleted into the archive table (including additional fields like deletion time, ID of the person who deleted it, etc.), and then delete the rows from the source table.
Many experts have already pointed out the drawbacks of Method 1.
Method 2 is easier to implement, non-intrusive to the source table, but it comes at the cost of increased disk space usage.
When designing the database, we should analyze the specific application scenario:
1. Based on the Business Requirements
If the business requirement is to back up data, the solution should be database backups, not logical deletions.
If the business requirement is to archive data, the solution should be archiving data, i.e., moving data into an archive table.
If the goal is simply to “freeze” data, an inactive flag should be used. This provides perfect semantic meaning and ensures consistency. It’s important to note that “freezing” is not the same as “deleting.” “Freezing” means that if you need to use the data again, you can simply “unfreeze” it. “Deleting” means the data is gone, and if you need it again, you must recreate it.
2. Based on the Database System
Some database systems already implement archival features for you. For example, SQL Server has a history table feature that automatically logs deleted records into a history table, and updates are stored with their previous values. In such cases, there’s no need to create an archive table or implement archival logic at the code level.
3. Based on the Data Type in the Table
For tables such as menu tables (dictionary tables) or log tables, logical deletion is unnecessary and physical deletion is sufficient.
Only tables that store critical data, such as account tables or balance tables, should implement logical deletion. In such cases, using an archive table may be the better approach.
For further reading:
(Note: Currently, Zhihu requires login to view the original content. Without logging in, it will show random text as part of their anti-scraping measures against AI models.)