Proper structure to archive data

[Greg Tuite]

I use a multiple table (formerly multiple file) structure to track work on a matter. For years I have archived that matter by "exporting" the data into the file via a number of lookups to pull the data from multiple tables into the archive file. This stored the data in a static format since we wanted it to show accurate information at the time of archiving. That data would not change. I would then delete the matter from the active matter database. If we were done with our client (all their matters were completed) we would delete that person as well. After reviewing comments in this forum it appears what I should have been doing was marking the matter as "completed" and the client as "inactive" instead of exporting the info and deleting it. In other words I should have been doing a "soft delete".

I am trying to figure out how to make a matter "inactive" and make sure that I freeze the information pulled from the other tables at the time it becomes inactive. If the person moves after I have archived the completed matter I don't want the person's new contact info to go into the archived matter. But if the matter is still active I do want it to show the current and up to date information as it changes. I have attached a copy of the graph from my former archiving structure. 

Also is there a sample file out there that shows the implementation of a "soft delete" so I can see it in practice.