In a relational database it seems quite common to use soft-deletes. My pondering comes to if it is really necessary to cascade these deletes? The reason I wonder is that it seems to me that cascading a soft delete wouldn't add any additional information.
I.e. lets say we have a table MainContract and a table ServiceContract where the relation is one-to-many. Say we soft-delete the MainContract but ignore doing so to lets say three ServiceContracts that all belong to this MainContract.
If we query the DB for ServiceContracts that are not deleted, we could easily check if the MainContract owning the ServiceContract is deleted or not.
Just formulating the pondering makes me realize that the design choice here perhaps depends on whether its more likely that we will delete often or if we will need to browse a lot among historical records.
If we delete often but don't need to check the history that often, it would be better to have a simple approach to deleting (not cascading the soft delete). On the other hand, if we need to retrieve historical records often, it could be worth implementing cascading deletes so that we would need less complex queries.
However, in a relational DB, a row is often not meaningful on its own. So in any case we will need to make joins "up the tree" in order for a row to make sense. For example a ServiceContract might not provide any meaningful information without knowing what MainContract it belongs to.
Does anyone have any thoughts on this? Has anyone used any or both of these approaches?
