In a relational database, using soft deletes seems pretty common. My reflection comes if it really is necessary to cascade these deletions? The reason is that it seems to me that cascading soft deletes will not add additional information.
those. let's say we have a MainContract table and a ServiceContract table, where the one-to-many relationship is. Say we gently remove MainContract, but ignore it to say that the three ServiceContracts belong to all this MainContract.
If we query DBs for ServiceContracts that are not deleted, we can easily check whether MainContract owning the ServiceContract has retired or not.
Just formulating the thoughts makes me realize that the choice of design here may depend on whether it is more likely that we will delete often, or if we need to look through a lot of historical records.
If we delete often, but you don’t need to check the history often, it would be better to have a simple deletion approach (not cascading soft deletions). On the other hand, if we often need to retrieve historical records, it may be worth implementing cascading deletes so that we need less complex queries.
However, in a relational database, a row often does not make sense on its own. Therefore, in any case, we will need to combine "up the tree" so that the line makes sense. For example, ServiceContract may not provide any relevant information without knowing what MainContract belongs to.
- ? - ?