Database design question...

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."craig" <e@.mail.com> wrote in message
news:8TM9f.163662$lI5.141621@.tornado.ohiordc.rr.co m...
>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.

This has been answer in microsoft.public.sqlserver.programming.

Please do not multi-post.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:v7udnQrqD7ZPdPreRVnyjA@.giganews.com...
> "craig" <e@.mail.com> wrote in message
> news:8TM9f.163662$lI5.141621@.tornado.ohiordc.rr.co m...
>>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.
>>
> This has been answer in microsoft.public.sqlserver.programming.
> Please do not multi-post.
> --
> David Portas
> SQL Server MVP

Potentially different audiences on two different servers with different
perspectives.|||So CROSS-POST if you must. Do NOT multi-post.

Multi-posting is extremely inconsiderate to those who help you as well
as likely to be ultimately less productive for you. That's because
multi-posting divides, weakens and renders redundant the efforts of
others and makes it more likely they will kill or ignore your posts in
future. If you don't know how to cross-post then read your manual.

--
David Portas
SQL Server MVP
--|||Definitely did not intend to be inconsiderate.

Sorry.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130946993.340299.287440@.g14g2000cwa.googlegr oups.com...
> So CROSS-POST if you must. Do NOT multi-post.
> Multi-posting is extremely inconsiderate to those who help you as well
> as likely to be ultimately less productive for you. That's because
> multi-posting divides, weakens and renders redundant the efforts of
> others and makes it more likely they will kill or ignore your posts in
> future. If you don't know how to cross-post then read your manual.
> --
> David Portas
> SQL Server MVP
> --