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.
(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.)
Initial data for a project, such as roles (e.g., super admin role), permissions, and which users are granted admin privileges, is typically designed during the requirements analysis phase.
This initial data is a part of the project’s operation and will be used in the production environment. However, data seeding is generally used during the development phase.
Although Laravel doesn’t offer a built-in solution for this, we can leverage the database migration feature to achieve it. In terms of functionality, data migration is also part of the project, with the execution timing aligning perfectly with the installation of the project. The execution order is critical, ensuring that the initialization data is applied after the database table structure is created.
We can generate migration files for initializing data using the following command:
php artisan make:migration seed_categories_data
We define the naming convention for such migration files as seed_(table_name)_data.
For project initialization data, using a Seeder is less convenient than using a database migration file, especially in collaborative development. With multiple migration files from different developers, you only need to execute a single migration command. If issues arise, you can easily roll back, whereas Seeders lack rollback functionality. Rollback is crucial, both in development and production environments. If the initial data can be fully determined before the project is deployed (which is almost impossible), then using a Seeder is acceptable and recommended. However, after the project goes live, it’s likely that the initial data will require modifications (such as additions, deletions, or updates). In such cases, using a Seeder is inappropriate, and database migration files should be used instead. Seeders are primarily used for generating test data, and they should not be used for altering or deleting production data.
