Relational Database Design Practices: Deletion?

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 - 07:34:53 CEST