Re: Representation for Deleted Entities: difficult question

Note that SQL Server 2000 EE does support partitioned views.

On 14 May 2004 09:14:47 -0700, robertbrown1971_at_yahoo.com (Robert Brown) wrote:

>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 - 18:20:13 CEST