Re: Relational Database Design Practices: Deletion?

"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.

If you go to http://www.pgro.uk7.net/books.htm and search for "Temporal" on the page, you will find a book that describes the ideal way to handle what you are doing. From that, you should be able to adapt a solution that works around SQL's lack of interval type generators. Received on Thu Jun 19 2003 - 15:24:53 CEST