Archiving Old Data -
OpenVPMS has been in use by clinics for over 25 years now - I wonder how much "old data" is being kept in databases - occupying disk space and slowing data access.
I define "old data" as that which no longer has any value ie - its being kept outside legislative requirements for animals that are no longer clients or alive.
Is it worth while looking at some sort of archive solution internally - ie selectively move records to an alternate db / file while leaving a link behind in the record.
This would free up space / speed
We have long given up trying to give MYSQL enough memory to load entire data sets into memory its not practical due to the size increases that go with storing documents/blobs in a database.
Thoughts and alternatives?
Re: Archiving Old Data -
You've aged us prematurely - we started in 2005 :)
The biggest consumer of space is documents, so one solution would be to move legacy documents out to a secondary database and refer to them via a link. This wouldn't improve performance, but it would improve backup and restore times as the primary database would grow at a smaller rate.
Legacy customer and patient data is another issue. Once these are deemed no longer relevant, what do you want to be able to do with them? Some possible strategies:
E.g. if a customer has been inactive for 10 years, all of their data would be removed
E.g, a customer would appear in searches but the only information held would be a document containing a list of their patients and a final account balance
This would store the data in a different form to that stored in OpenVPMS as it is a snapshot of the data at the time it was moved. It might also only store a subset of the data.
Re: Archiving Old Data -
I guess some sort of data rotation would be ideal - configurable
archive documents older that say 7 years - perhaps to a zip file etc. that can be archived and held if required.
archive and delete patients / customer older than a set period - archiving could be a database OR it could be file based.
To give you an idea - we have 1 clinic that only has 1 vet that the db size is increasing by 3.5gb a year due to high document storage - and a clinic with 5 vets that only increases by 700mb a year.
With the new plugins - adding lots of reports and other documents I suspect we are going to need to streamline this at some point.
I would view it as
1. Document Archive Settings
- Archive and Delete(from the live file) after X years.
I dont know how much data certain objects use - I gather a filled form uses more than say a letter - the filled letter seems to store a odt file where as the unfilled form seems to link to the template.
2. Patient / Customer Archive Settings
- Archive and Delete (from live data) after X years of no activity.
Anyway just some thoughts not really critical at this juncture.