Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Representation for Deleted Entities: difficult question

Re: Representation for Deleted Entities: difficult question

From: <>
Date: 15 May 2004 00:55:34 GMT
Message-ID: <20040514205534.084$> (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.

Users are not deleted. They may die, they may be incarcerated, they may be fired, but they cannot be deleted. Only data is deleted.

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

If I am hit by a bus tomorrow, will the office furniture I ordered (but which is owned by the company) disappear? Will the patents I've generated for the company no longer be valid?

> "deleted" affiliations - a whole shadow
> schema full of such ghost entities.

Why would each of these need it's own deleted flag? If they are being joined against the user table, then they can just rely on the user table to provide the necessary logic. If they are not being joined against the user table, then why would you want them to be excluded based on a condition of a user?

> This would overtime degrade
> performance since now each query in the system has to add a clause:
> "where deleted = 0".

Do they want the reporting to go back indefinately, or would they like the data to be "really" deleted after a certain time of "virtual" deletion?

> I assume this is a standard problem since many organizations must have
> this need of preserving deleted records (for legal or other reasons).

It is a standard problem, but there is not a standard solution.


-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Fri May 14 2004 - 19:55:34 CDT

Original text of this message