Re: Representation for Deleted Entities: difficult question

"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0405131032.6c2e9802_at_posting.google.com...
> 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".

Blanket statements like this are rarely true.

> 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.

So you think file is better than DBMS?

> Is there a more satisfying solution to this than have this "deleted"
> flag?

Can I suggest that there is a modelling problem? I can imagine customer having multiple timestamp columns, for example

table customer (

   ...

   DOB       DATE,
   married     DATE,
   divorced   DATE,
   died        DATE

but can't possibly see why you need "is_alive", "is_married" boolean columns. Received on Thu May 13 2004 - 14:43:46 CDT