Re: Relational Database Design Practices: Deletion?

GREAT minds think a like...consider a similar situation

I manage a mortgage administration database. One of the requirements on information is that it is stored for 6 years. This has something to do with the need to retrieve this information for purposes of varying financial reasons.

This presents the following problem:
The database is designed with normal relational database concepts in mind. Thus, I have a table which keeps a record of all solicitors that are added to the database. This allows a single SolicitorID to be stored in the table which stores the application information which can be called up based on a one (solicitor) to many (application) relationship and the relevant details extracted when necessary.

However, if I remove the same Solicitor record from my database, if I then try to pull solicitor information off as in the above scenario I will have essentially lost all my information concerning the applicants solicitor.
This is quite a reasonable possibility since we are talking about 6 years to keep the data.

The only alternative is a non relational, high redundancy and sluggish database which simply stores all the solicitor information in the applicants file.

I am sure there is someone out there who has come across this sort of problem before. I can think of various ways around it, or compromises which would give you the best of both worlds, but I was just wondering if anyone might have some well-tried suggestions.

many thanks for anyones assistance

lstocky

"Adam Weiss" <adam_at_removespamfodder.signal11.com> wrote in message news:<3ef14b53$1_at_news.qnet.com>...
> I've been thinking a lot about multiple tables containing information that
> are linked via foreign keys. Mostly it has to do with transient data and
> history data you want to preserve.
>
> For example, say I have two tables. One contains transient data which
> changes frequently, data is added and deleted on a regular basis. Then I
> have another table which is more of a historical logging table that
> references data in the transient table by means of a foreign key. While I
> could live without the data from the transient table, I'd really like to
> know what the best practice is for keeping it around.
>
> In the past when I've run into situations like this, I've enforced a rule
> where no data is ever deleted from the transient table, but rather flagged
> deleted and hidden from the user except for when referenced by the
> historical table. Even if the deleted flag is an index, it seems that as
> the table grows infinitely, performance would be adversely affected. I've
> scoured the web looking for dicussion on this topic and haven't found much
> of anything useful. I'm curious what the best practice is for this type of
> situation. I can think of a few ideas:
>
> - Stuff the data from transient table into the historical table (seems like
> unneccessary duplication of data)
>
> - Deleted flags. (but then you need some kind of archival scheme over time
> and performance may suffer)
>
> - Some kind of complicated delete stored procedure scheme that moves rows of
> data into archival tables and updates all references throughout the system.
>
> I really think these are non-optimal and I'm curious about best practices
> for this type of stuff. If anyone could point me at some resources (books,
> online articles) that discuss these types of data management design issues,
> I'd be greatly appreciative.
>
> Thanks in advance.
>
> --Adam Weiss
Received on Thu Jun 19 2003 - 17:04:41 CEST