Data and Referential Integrity

I apologize in advance if this isn't strictly on topic. I'm designing a database system, and one of the issues I'm dealing with is archiving the records. I see two options, either including a field in tblMain that tells me whether it is open or closed, or making a new table. I seem to think that it would be better to create a new table and copy the PK (primary key) and foreign keys to the new table (being that new data is entered when a record is ready to be archived). I read a one page summary of the pro's and con's of each, but I'm still not positive of which to do.

But my primary question deals with data and referential integrity. If tblClosed and tblMain both contain a PK called PropertyID (and I've taken measures to make sure the same number can not be in both tables), will I violate referential integrity by deleting a file from tblMain? Basically, if the 3rd table has a 1:M relationship with both tblClosed and tblMain, will it check both of them each time a deletion is made? Is there a smarter way to do this?

Thanks in advance, again, sorry if this isn't on topic or if I wasn't clear.

Adam Received on Wed Feb 13 2002 - 00:19:33 CET