Representation for Deleted Entities: difficult question
Date: 13 May 2004 11:32:21 -0700
Message-ID: <240a4d09.0405131032.6c2e9802_at_posting.google.com>
Our customer (of our ecommerce system) wants to be able to preserve deleted entities in the database so that they can do reporting, auditing etc.
The system is quite complex where each end user can belong to multiple institutional affiliations (which can purchase on behalf of the user). The end user also has a rich trail of past transactions affiliations etc. Thus in the schema each user entity is related to many others which in turn relate to yet others and so on.
In the past when a user was deleted all of his complex relationships were also deleted in a cascading fashion. But now the customer wants us to add a "deleted" flag to each user so that a user is never _really_ deleted but instead his "deleted" flag is set to true. The system subsequently behaves as if the user did not exist but the customer can still do reports on deleted users.
I pointed out that it is not as simple as that because the user entity
is related to many, many others so we would have to add this "deleted"
flag to every relationship and every other entity and thus have
"deleted" past purchases, "deleted" affiliations - a whole shadow
schema full of such ghost entities. This would overtime degrade
"where deleted = 0".
Is there a more satisfying solution to this than have this "deleted" flag?
Thanks for your help,
- robert