you might consider archiving just the Patient and Anesthetic records; and, in your archive, link these tables back to your live table if you need to see child data.
I would personally question the need to archive to a separate database. There are a number of articles on the need to delete (or archive) out of databases. If the data held is simply simple table entries, simply add a flag to 'hide' the top level data entry. This does't really affect the performance of the overall DB, but needs to be included in the design. For example archived items shouldn't appear in finds etc, even though they are still in the DB.
I use archive flags, a simple numeric field set to 1 when the record has been 'archived' or indeed 'deleted'. This field can be used by child tables quite simply to also archive any related records.
Sometimes we have a habit of applying 'real world' solutions to 'virtual world' scenarios. Archiving doesn't have to mean moving something to a different room or store, it just means we don't want to see it when we're searching for current stuff.
this is true - I also think that many programmers anthropomorphize the computer. "I don't want it to break a sweat because of my inefficient or 'brute-force looping' code."
Interesting answers, and you make a good point about why should I need to physically remove the records. I think the problem is that this is for a HIPAA-compliant electronic medical record (EMR), and if I leave those records on the iPad, whether the user can see them or not (I'm making an iPad-based anesthesia EMR), it becomes a security issue.
My brute-force solution is to first look for all of the anesthesia or patient records that are marked "archived", and then create a loop that takes the id of each parent record and then uses it to find all of the records in a specific child table and copy their data to the "archive" file. Then go to the next child table, delete, etc. for 20 or so tables.
This seems to me to be too brute force, and I'm hoping you guys have a more elegant solution! ;>)
Well, I think you should test Andy's solution of adding a 1/0 "flag_Archived" indexed number field to your main table(s).
See if modifying your queries with "flag_Archived = 0" will speed up operation without having to archive any records.
It would be nice if that's all you needed to do.
You could also set up a "synced" solution where you down load just the needed records to the iPad from a server, then upload any new or modified records back up to the server. The data on the iPad need only be those records needed for the current procedure or day's schedule of procedures.
Not sure on the HIPAA side, but at the end of the day those records have been on the iPad at some point and so already susceptible to security issues? Surely any records on the iPad need securing anyway, so need to be HIPAA compliant in the first place.
My second point is, and this is something I've brought up with one of my own clients, without resolution, what is the archive for? How and when will anyone need to access it? This can and should make a difference to the solution design.
One possibility worth considering is simply backing the entire DB up then using the out of the box, 'delete records when parent record is deleted' functionality of FM. It's simple.
Otherwise, as David says, sometimes a sledgehammer inefficient approach is fine if it's a not running constantly, it just needs lots of testing to make sure it's robust.
Just a point of order, I tend to 'qualify' an archive flag with an opposite 'IsLive' flag, as the archive flag is often 1 or Null (either the user or a calculation sets the archive 'bit' to be 1, otherwise its ""). The IsLive calculation sets archive::"" = 1 and archive::1 = 0. I then simply need to work with records with IsLive set to 1 and ignore IsLive set to 0. Child records can inherit the same flag.
be careful if you like - it's not like the computer cares.
These are very good posts - and while I consider them I want to add this bit of information which highlights why I want to get the records off of the iPad.
Under HIPAA, you have a duty to inform patients if their records have been released in a breach of security. In addition to notifying the individual patients, if the breach affects more than 500 records, you are "required to provide notice to prominent media outlets serving the State or jurisdiction."
Consequently, if the iPad is lost and you presume that the records have been breached (a judgment call, since the files are encrypted on the iPad and you may decide that is sufficient protection even if it has been stolen), you would like to have as few records as necessary physically on the iPad to decrease your need to inform patients.
And as for the need to archive, they will need to be accessed for an indeterminate amount of time (I have heard 30 years put out) should the patient request them.
That makes sense.
So - it's back to "physical" archiving. It seems that you need to identify exactly which tables contain sensitive information and limit your archiving to those tables - just to save you archiving all of your tables.
Ok, so I'm stating to think the suggestion Phil had was good. Keep your main DB on a server somewhere and only sync the records you need (or work direct from it). David has a really good point though, What part of the information is sensitive? if it is just names and details, that's probably just one table that needs 'archiving' (deleting). Is it sensitive that a number of nameless people had certain ailments?
The archiving side is still an interesting question, not just for this post, but others and all of our everyday work. Is it sufficient to archive by copying records to an FM 15 database with limited or no user interface to speak of. Even if we did create a UI to access these archives, would it be relevant in 30 years time? Will it be relevant in 5 years time? Do we need to keep all information for that patient (I'm guessing so), in which case the child tables will beed to be trawled and archived. If it is a case we need to keep the records for legal reason, failing the main served DB idea, I go back to periodic backups, then just delete patient information from the iPads.
I liked Phil's solution as well - but there is one problem.
My goal for the "base case" of this anesthesia EMR is that it can be run completely on the iPad - without any internet connection. Think anesthesia in extreme conditions - "mission cases" done in 3rd world countries, US military (I am an old Army nurse), and more specifically, a solo practicing Certified Registered Nurse Anesthetist (CRNA) at a plastic surgeon's office or similar.
So the server idea won't work. I like the idea of only archiving the parent records, but then I would lose the medications, the vital signs - the heart of the record.
One solution I thought about is simply to print every record to PDF, and then archive these PDFs - or, for the 30 year mark, physically print them out and keep them in a safe place. Perhaps into an easily scannable and Optical Character Recognition (OCR) amenable pdf, that can be "read in" at some later date 20 years hence?
Thanks for all of the ideas! I am getting a good sense of the parameters of this issue.