Re: Representation for Deleted Entities: difficult question

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<40a3cbf2.428366208_at_localhost>...

> You didn't post database you're using.

Thanks for your answer. This particular customer is using Oracle but our software is supported on SQL server as well.  

> In Oracle you could partition the main table (from where all
> 'cascaded' is coming from) into deleted/undeleted records. Put a view
> on top of the table just as original table would look like with where
> clause 'undeleted' and the undeleted partition would always be used.
> Only certain versions of oracle allow rows to migrate from partition
> to partition, u can look it up.
>
> However, you would still suffer from 'performance' issues on the other
> tables, if your data is truly huge.
>
> Best solution would probably be to separate all deleted/undeleted data
> into separate tables, and built a union view on top of them for
> reporting purposes.
>
> my2c
> Someone might have a better idea.
>
>
>
> On 13 May 2004 11:32:21 -0700, robertbrown1971_at_yahoo.com (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
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email
Received on Fri May 14 2004 - 11:14:47 CDT