6 Replies Latest reply on Jul 24, 2015 7:51 AM by jasongan

    Archive Records

    jasongan

      Title

      Archive Records

      Post

      Hello All,

      I am looking for a good solution for archiving records. I am considering two options below, I am looking forward to your advice.
      Let me use an example to explain my options: 
      Database: X;  Table 1: Order; Table: Line Items;  ( "Order" and "Line Items" relate by Order_ID).

      Option 1:
         Create a Database Y, and Table "Order_Archive", Table "Line Items_Archinve".
         Whenever I want to archive a record, I export that Order and its Line Items and import into the archive tables.
         
         Pros: simple, data centralized and easy to retrieve by importing the archived data back.
         Cons: Hard to ensure the structures are same.
               If I add/delete/update the source table structure, how to keep the archive tables under same structure,and it will end up losing data.

      Options 2:
          Export record by IDs into individual Excel files.
          For example, to archive Order (ID 111), I will have two Excel files created. Order_111.xlsx, and LineItems_111.xlsx.
          If I need to archive more than one order, I will find the Orders, and create each Order to have two Excel files created.
          
          Pros: easy to archive Structure and Data.
          Cons: Hard to retrieve. data not centralized. 
          
          Although I can place all exported Excel files under an archive folder, and use Windows to search by keywords to locate those Excel archive files, but to retrieve certain orders, it will take time.

      Is there a better away to do this?

      Thank you,

      Jason Gan

       

        • 1. Re: Archive Records
          SteveMartino

          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.

          • 2. Re: Archive Records
            jasongan

            Hi Steve,

            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.

            Thanks

             

            Jason

            • 3. Re: Archive Records
              SteveMartino

              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.

              • 4. Re: Archive Records
                jasongan

                Hi Steve,

                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?

                 

                thanks,

                 

                Jason

                • 5. Re: Archive Records
                  SteveMartino

                  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

                  • 6. Re: Archive Records
                    jasongan

                    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.

                     

                    Jason