Re: Representation for Deleted Entities: difficult question

From: Mike Nicewarner <psyclo_at_nospam_datamodel.org>
Date: Fri, 14 May 2004 11:46:41 -0500
Message-ID: <c82t4h$g17$1_at_news.netins.net>


As Leandro and Mikito point out, you have flaws in your design. First, deleting the user entity is the only thing that is logically deleted, but rather than make it an indicator, use a date, as in DELETE_DATE as nullable. Non-null entities are to be ignored. In addition, all relationships to the user entity should be evaluated to determine if they need to be sensitive to the user entity's status. Some may, and others may not. This is a business question, not a technical question. For instance, if there are invoices and inventory tables linked in some way to the user entity, would you really want to *not* display that information just because an associated user entity had been deleted?

I'd really need to see your design and talk to your business to know exactly what impact this design change would have on your database.

-- 
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike_at_nospam!datamodel.org
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message
news: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
> 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
Received on Fri May 14 2004 - 18:46:41 CEST

Original text of this message