Continuing the discussion from Reducing DB size by deleting Transactions:
Yes - this is a common issue in most POS systems with data 2/3+ years old.
I have written interfaces into a few SQL based POS systems and there is a clear difference in “Clean Up” to “Archiving”:
Cleanup - Remove transaction data only older than say 2 years on wards (a transaction being any record data which is created for adding a sale/receipt and does not include Master data such as Products, Customers etc);
Archiving - Removing transactions as described above BUT placing them in a format which can be easily accessed preferably by the POS.
The Cleanup is the easiest option as the transaction data is deleted based on a user specified data, files packed and all system go.
The Archiving typically requires the full creation of a “second” database which can be switched to or accessed by the POS. Archiving can be tricky because you will need to Archive again in a few years so the questions arises -
“Do you create another System”? OR “Do you append old data into the 1st Archive”?
If you wish to be neat and look at a good historical base then you would APPEND but this infers you need to make sure ALL Master products exist/menus exist/ rules exist etc etc…
For my 2 cents worth (and experience) - once the data is old it is dead. The client rarely wishes to go back into the past and on that rare occasion I suggest review the historical reports that they printed out before archiving (cheeky). Archiving is the “ducks nuts” but requires a lot of work and has a many forms of implementation. The Archive Systems I have written were almost “Systems within Systems”.
My thoughts, Paul.
[A man waiting for V5 and hopefully Multi Customer Accounts 
]
PS - Pls link this as I could only create a new topic?