Soft Delete Best Practices (Php/Mysql)

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...

Soft Delete VS Hard Delete

I would say that the third option is the best choice

My reasoning for this is any tickets stored in the database should be kept for the life of the database, if you attempt to delete a user from the database, you will remove that users reference to any tickets in the database.

(Thats if the database will let you delete it since the user's id will be linked to any tickets he/she may have created)

When that user goes to create a new account in the ticketing system, the tickets that he may have created months ago have dissapeared and may now have been created by null.

Implementing soft delete with minimal impact on performance and code

If the key is numeric, I handle a "soft-delete" by negating the key. (Of course, won't work for identity keys). You don't need to change your code at all, and can easily restore the record by multiplying by -1.

Just another approach to give some thought to... If the key is alphanumeric, you can do something similar by prepending a unique "marker" characters. Since deleted records will all begin with this marker, then will end up off by themselves in the index.

Does Propel know when an object has been soft-deleted, so that child entities could still show their deleted parent?

Soft delete is a broken abstraction. You should use the archivable behavior instead (see why in the Propel blog: http://propelorm.org/blog/2011/08/29/introducing-archivable-behavior-and-why-soft-delete-is-deprecated.html)

how to store deleted rows in a transactional database

"My question is how to set the unique constraint on a table which has a isActive flag as suggested by heximal above. I do have surrogate keys in all my tables. But I want to ensure the natural key columns (what we call business key columns) have a unique constraint.

If I have a deleted_on field to track deletes, then I can include this column as part of the natural key constraint. So, it allows more than one deleted record with the same business key combination, differing only in the deleted_on date field."

Even with a deleted_on DATE field as part of your natural key, you still couldn't delete-reinsert-delete all on the same day. May seem pathological, but can you really be certain that the pathological case will NEVER occur ?

If your database needs to reflect the fact that some of the content is "active" in the sense that it is highly relevant for current business, and some other content is "inactive", e.g. in the sense that the only reason for keeping it some time is for archival purposes, then design your database to reflect that fact by defining two tables: one with the "active" content and one with the "archived" content. Perhaps you can use a trigger to automatically achieve the "move-to-archive" whenever a delete occurs.

Doing so, you can have your natural key enforced by the DBMS using a unique constraint on the "active" table, and you can include a deletion-timestamp in the "archive" table, and you may not even need to define any key at all on that table.

---

Related Topics