Soft delete best practices (PHP/MySQL)

Question

Problem

In a web application dealing with products and orders, I want to maintain information and relationships between former employees (users) and the orders they handled. I want to maintain information and relationships between obsolete products and orders which include these products.

However I want employees to be able to de-clutter the administration interfaces, such as removing former employees, obsolete products, obsolete product groups etc.

I'm thinking of implementing soft-deletion. So, how does one usually do this?

My immediate thoughts

My first thought is to stick a "flag_softdeleted TINYINT NOT NULL DEFAULT 0" column in every table of objects that should be soft deletable. Or maybe use a timestamp instead?

Then, I provide a "Show deleted" or "Undelete" button in each relevant GUI. Clicking this button you will include soft-deleted records in the result. Each deleted record has a "Restore" button. Does this make sense?

Your thoughts?

Also, I'd appreciate any links to relevant resources.

Answer 1

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