Re: Representation for Deleted Entities: difficult question

Robert Brown wrote:
> 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
> performance since now each query in the system has to add a clause:
> "where deleted = 0".
>
> I assume this is a standard problem since many organizations must have
> this need of preserving deleted records (for legal or other reasons).
> I tried to talk them into creating a simple audit file where all the
> deletions will be recorded in XML but they were not too happy with
> that.
>
> Is there a more satisfying solution to this than have this "deleted"
> flag?
>
> Thanks for your help,
>
> - robert

Would it work to insert the "deleted" rows into comparable tables for "deleted" customers?

Alternatively, add the is_deleted column to the customer table and now created views for the other tables "where is_deleted = 0". Received on Thu May 13 2004 - 19:12:13 CDT