Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted.
So who uses this technique?
For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow
Posts table has a
ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation.
If you’re using Oracle, you can take advantage of its Flashback capabilities, so you don’t need to change your application code to offer such a functionality. Another option is to use the SQL Server Temporal Table feature.
However, not all relational database systems support Flashback queries, or they allow you to recover a certain record without having to restore from a database backup. In this case, Hibernate allows you to simplify the implementation of soft deletes, and this article is gong to explain the best way to implement the logical deletion mechanism.