This is a theoretical* design question about marking rows as removed or deleted. I assume rows should be marked as removed whenever there is a need to remove the pertinent data, but the row should stay in the relevant database table to maintain referential integrity.
* Theoretical, within the scope of this question, means that it should not be related to concrete example and also not related to concrete relational database.
For the sake of the question I have chosen a classical example: A Shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.
We have an
Orders table which references a
Users table. If we try to DELETE a user row, we will lose all referenced orders (assuming we have strong referencing). Therefore instead of a DELETion of a user row we may mark such row as 'removed' (i.e., have a
BIT (or boolean) column SET to '1', e.g.,
IsRemoved = 1) and clear his
login, password, first name, last name columns to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.
But there is an alternative, instead of having an
Users relationship, we can have a different structure:
Users, where the
Customers table has no personal data. When a user wants to quit, we simply DELETE the pertinent
User row, but
Customer along with its related
Orders stay stored in the database. No need to mark row as removed, no need for additional business rules to handle the removed rows behavior. I believe this approach is better and may be used for any situation where the
IsRemoved row approach is used.
Do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?
P.S. Of course, it is possible to referential integrity with NULLs in FOREIGN KEYs, however this approach is out of scope. While it may be useful in the Shop's example, the question is hypothetical, and shop is used only as example, not as a real problem (which may be solved with NULLs).