Why do you need to archive the records? How many records we talking about. Most people tend to believe that it's best to keep all the records, and mark them as archived, or inactive-rather then moving them somewhere else.
On your layouts you can always show/hide archived records with script triggers/layout triggers or just filter the relationship (which could be slow as records approach the millions)
I do this in my DB. Even though there are only about 5000 records, I never notice any speed issues. Now if this is a server there are probably better ways.
I wrote a script that when a record is marked inactive, it goes thru all the related records and marks them as inactive.
Thanks for you email.
It's the client's requirement that they need to see cleaner current database. that's the way of how they delete the records. I agree
we can mark those records as "Inactive", and not show them. But somehow their requirements are reasonable as well.
Mark them and export them to another DB file, aptly named Archive, with all related records. Connect the file as another table occurrence in your relationship graph.
Easiest for initial set up, mark all the records you want to archive in YourDataBase, and make a copy
Create another copy of the database (so you have the same structure), change the name to ArchiveYourDataBase (or whatever).
Perform a find for all non marked records in the archive DB. Delete all of those and their related data (hopefully you have the 'Delete related records in this table when a record is deleted in the other table' turned on for all child records. Now you have a similar database of just archived records.
Go back to the original db (actually it's copy). Do a find for all the marked records. Delete all found records (and once again all the child,grandchild records, etc. Now you have a db of just active records.
And you also have a DB of ALL records.
Now you'll have to connect the current DB of actives, to an external source, your archived DB. Then your scripted routine for archiving can first mark the records and related records, then after double checking (if you want), move them to the other tables in the archived DB.
You'll also need a script/routine to search archived records, and bring them back if they want to make them active.
Keep in mind I still think it's best to keep all the records in the same DB and just allow the users to see the active records. At the end of every find script you can just omit the inactives on the parent record.
An example is QuickBooks. Even though you can archive records (no one outside of qb probably knows what happens under the hood), just un-checking the 'Show Inactive', hides all the inactives, but keeps them available for reporting. Moving the records may mess up all the reporting, weekly/monthly/annual, and year to year reports.
If the records stay in the same DB, the users don't routinely see them, wouldn't you agree that's the 'cleaner' solution? Plus, if you change the structure in the working DB, you also have to change structure in the Archive DB.
I was thinking about the same solution as you suggested. but something like you last sentence stopped me. As we continue to change the working table structure, it is hard to keep the working table and the archive table structure same.
If you just add some fields in the working table, it is easy to find out and copy them into the archive table. But, when we update the working table's fields name, over time I might forget to update the archive table and then it is hard to find which fields to update;
and if I delete certain fields in the working table, does it mean I need to delete the archive table's same fields? No. not exactly. I still need the archive data kept.
does it make sense?
Yes you'll have to be diligent. But it could get worse then that if you have calc fields that you change, and don't remember to change in both copies and bring the record back.
If it were me, I'd try to sell the 'One DB, mark records, hide them on all finds, unless you specifically do a find for 'all records' concept'. It's not cluttered if they don't see it :)
Also, if you go with 2 connected DB's, you still have the same total amount of size (actually more) then one DB
I just came across a situation that make me prefer my option 2.
Here is my situation. I had a duplicate script using importing found set into a temp table and after some process I importing from the temp table into my current table. Recently I found the duplicated record missing some info, and the reason is, those fields definitions are different between the two tables. Over time the ones in the current table were updated from calculation to auto-entry, but the temp table was forgotten to keep pace with the structure. In order to get the duplication script work, I have to make the temp table have the same structure.
Thanks, and I am still looking for if there is a better archiving solution for me to keep the data and the structure.