Re: Archive records with relationships

Ole Laurisch wrote:

> Hi at all,
>
>
> I have a complex database structure with many tables. But let's simplify the
> whole thing. Let us presume that we have this three tables A, B and C.
>
> [A]-1--------n-[B]-1---------n-[C]
>
>
> where "1----------n" are 1:n relations.
>
> Now the application will add thousands of records per day in these tables.
> Therefor we need to archive records that are no longer needed.
>
> Now the user can specify to archive some records (by a filter) from the
> table B.
>
> How could we handle the problem, that there are references now pointing from
> a record in table A to a record in table B, which might not be existing any
> more?
>
> One opportunity is not to delete the records in table B, but replacing them
> with null-values (all except the key values). But that could neither be the
> only nor the best solution, could it?
>
> Any suggestions would be greatly appreciated!
>
> Many thanks in advance!
>
> Ole

There is nothing positive I can say about a design whose intent is to violate data integrity. Why are you leaving the records in A? Why are you deleting the records in B?

Your statement "Therefore we need to archive records" does not in and of itself explain anything? Why can't you just leave the records there or clean up after yourself? NULL is not a value ... but what is the point of removing a value from a field rather than just removing the entire record? It seems that it no longer serves a purpose. I think what you have here is a design that needs to be re-evaluated.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)