How to Mark Database Records as Deleted

Imagine you’ve decided that you no longer want to physically delete records from the database, but rather keep them.

In this case, you need a logical way to identify which records are deleted, often referred to as a soft delete.

🎥 Don’t feel like reading? Maybe watch a video instead:

Many developers implement soft delete by adding a boolean flag, such as is_deleted, which can be set to true or false.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE
);

While this approach is simple, it lacks a crucial piece of information often needed in larger projects: the timestamp of when the records were deleted.

Instead of using a boolean flag, you can add a timestamp column to store the date and time when the records were deleted.

If the deleted_at column is null, it means the records are not deleted.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

There are also more complex methods to mark database records as deleted, such as moving them to an archive table or versioning the records within the same table.

Each of these methods has its advantages and disadvantages, and they may be suitable for specific projects.

These more complex mechanisms could be a topic for another post.

In conclusion, from my experience, using a timestamp for soft deletes has been the ideal solution in 99% of cases.

But what about you? How do you prefer to flag records as deleted?

Share your answer in the comments section. I respond to all of them!

Happy coding 💜

Comments