Posted by craig on 11/01/05 18:39

I have a question about database design that I think some of the more
experienced developers might be able to answer...

Early on in the development of the DB for a project we are working on, the
decision was made to use physical deletion of records from the DB rather
than logical deletion. This seemed to be working just fine until now.

We have added entity history functionality to our application (per the
requirements) in which we use history tables in the DB to maintain change
histories of many entities. These tables include foreign keys that
reference the records in the original tables for which they are maintaining
a history.

Now, when attempting to physically delete an entity for which history
records exist, we are obviously bumping up against referential integrity
issues.

Our options seem to be:

1. Stop enforcing referential integrity between history records and the
records they reference.
2. Switch from physical deletion to logical deletion of records.

I was wondering if anyone might have some advice/insight on which of these
two options would be better. We tend to think that option 2 is the better
way to go, but we are not yet sure of the implications of trying to make a
change like this at this point in the development process.

Any input would be highly apreciated!!!!

Thanks.

[Back to original message]