Soft delete best practices (PHP/MySQL)

That's how I do it. I have a is_deleted field which defaults to 0. Then queries just check WHERE is_deleted = 0.

I try to stay away from any hard-deletes as much as possible. They are necessary sometimes, but I make that an admin-only feature. That way we can hard-delete, but users can't...

Edit: In fact, you could use this to have multiple "layers" of soft-deletion in your app. So each could be a code:

• 0 -> Not Deleted
• 1 -> Soft Deleted, shows up in lists of deleted items for management users
• 2 -> Soft Deleted, does not show up for any user except admin users
• 3 -> Only shows up for developers.

Having the other 2 levels will still allow managers and admins to clean up the deleted lists if they get too long. And since the front-end code just checks for is_deleted = 0, it's transparent to the frontend...

Using soft-deletes is a common thing to implement, and they are dead useful for lots of things, like:

• Saving a user's data when they deleted something
• Saving your own data when you delete something
• Keep a track record of what really happened (a kind of audit)
• etcetera

There is one thing I want to point out that almost everyone miss, and it always comes back to bite you in the rear piece. The users of your application does not have the same understanding of a delete as you have.

There are different degrees of deletions. The typical user deletes stuff when (s)he

• Made a misstake and want to remove the bad data
• Doesn't want to see something on the screen anymore

The problem is that if you don't record the intention of the delete, your application cannot distinguish between erronous data (that should never have been created) and historically correct data.

Have a look at the following data:

PRICES | item | price | deleted |
       +------+-------+---------+
       |   A  |  101  |    1    |
       |   B  |  110  |    1    |
       |   C  |  120  |    0    |
       +------+-------+---------+

Some user doesn't want to show the price of item B, since they don't sell that item anymore. So he deletes it. Another user created a price for item A by misstake, so he deleted it and created the price for item C, as intended. Now, can you show me a list of the prices for all products? No, because either you have to display potentially erronous data (A), or you have to exclude all but current prices (C).

Of course the above can be dealt with in any number of ways. My point is that YOU need to be very clear with what YOU mean by a delete, and make sure that there is no way for the users to missunderstand it. One way would be to force the user to make a choice (hide/delete).

If I had existing code that hits that table, I would add the column and change the name of the table. Then I would create a view with the same name as the current table which selects only the active records. That way none of the existing code woudl break and you could have the soft delete column. If you want to see the deleted record, you select from the base table, otherwise you use the view.

I've always just used a deleted column as you mentioned. There's really not much more to it than that. Instead of deleting the record, just set the deleted field to true.

Some components I build allow the user to view all deleted records and restore them, others just display all records where deleted = 0