For many of the most painful deletion questions, the root problem is that when the software was first made the stakeholders/product-org didn't think about use-cases for deleting things. At best, they assume a "do not show" property can be placed onto things, which falls apart when you get to legal issues that compel actual removal.
Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion.
Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.
I do something similar, but instead keep a "date_deleted" column null by default, and the "active" column as a boolean.
That way, I kill two birds in one stone by having a dedicated column for last deletion (instead of updating a record that is supposedly deleted) and the status just as a boolean instead of some enum, or integer or string.
reply