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).
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.
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?