January 2, 2014 at 9:44 am #31459clinicoffice_userParticipant
Hi – I have two completely separate databases and I was wondering if they can be merged into one database. Is this possible please?January 2, 2014 at 10:59 am #33361SupportKeymaster
Hi – thanks for your enquiry. At present, I’m afraid that we do not have a facility to do a complete merge of two databases.
It is possible to do a partial export / import though. For example, if you have a “main” database which has the majority of your patients, appointments, finances etc. and a “secondary” database which has a smaller number, then you could export the patient list (i.e. patient names, addresses and contact details) from the secondary database and import that data into your main database. You could then do a similar export / import of the appointment diary too.
Q. Why don’t we have the facility to do a complete merge of two databases?
ClinicOffice uses a “relational database structure” which is incredibly complex. Writing a utility to merge two relational databases containing both disparate and overlapping data would be A MASSIVE undertaking.
Q. Why is writing a database merge utility so difficult?
Every record in a database has a unique indentifier (an 'ID' field) and each record may reference dozens of other records. When merging two databases you cannot simply aggregate the two datasets. Firstly that would result in conflicting unique ID fields and secondly some records need to be merged rather than aggregated.
For example you may have an Appointment Type called “Consultation (id: 1)” in database 1 and an Appointment Type called “Initial Consult (id: 5)” in database 2. They are of course the same appointment type and should be merged. However, they have different ID fields. There will be thousands of other entities referencing AppType:1 in database 1 and thousands referencing AppType:5 in database 2, all of which need to be somehow reconciled in a merged dataset.
That's a simple example. What about overlapping patient records? How do you determine if a patient is the same person? The spelling of their name? What if their name is mispelled in one database, or hyphenated differently? Then the records get incorrectly aggregated rather than merged. What if you have 3 “John Smiths” in one database and 2 in the other? Which ones match up? Perhaps compare their DOB or Postcode, but what if that data not been entered into both databases or has been entered differently? From a computer's point of view it's incredibly difficult to know when overlapping records should be merged and when they should be separated.
Even once you've worked that out, you still need to fix every single reference to each merged entity. Here is a list of the database entity IDs that a single patient record links to :-
patient -> app.per_id, patient -> appwait.per_id, patient -> cas.per_id, patient -> doc.per_id,
patient -> fileatt.per_id, patient -> custom_patient_fields.per_id, patient -> img.per_id,
patient -> inv.per_id, patient -> inv.patient_per_id, patient -> alloc.per_id,
patient -> item.supplier_per_id, patient -> link.ref_per_id, patient -> link.ref_cn_per_id,
patient -> pay.per_id, patient -> per.refby_per_id, patient -> per.gp_per_id,
patient -> per.resp_per_id, patient -> per.insurance_per_id, patient -> per.invrecip_per_id,
patient -> per.familyhead_per_id, patient -> po.supplier_per_id, patient -> sess.per_id,
patient -> staff.per_id, patient -> visnote.per_id, patient -> credit.per_id,
patient -> pclog.per_id, patient -> custom_session_fields.per_id, patient -> custom_case_fields.per_id,
patient -> perpasswd.per_id, patient -> recall.per_id, patient -> custom_contact_fields.per_id,
patient -> company.primary_per_id, patient -> actlog.per_id, patient -> ticket.per_id,
patient -> charge.patient_per_id, patient -> charge.invto_per_id, patient -> auxper.master_per_id,
patient -> cardsave.per_id
…and that's just a patient record. Merging financial records is even more complex.[/spoiler:27wgri7f]
Q. Are you planning to write a utility to merge databases?
As a small company, we obviously have limited resources and our development time is focused on developing ClinicOffice and undertaking bespoke (paid for) development work. The time and cost to design, code and maintain a database merge utility (which is essentially a one-shot utility that a customer would only use once) would be incredibly high.
So… what we’re asking is that if this is an absolute requirement for you (and if a simple export/import is not adequate) then please contact us to let us know. If there is sufficient interest and we can raise sufficient funding to at least cover the costs then we’ll be happy to schedule in the development.
- You must be logged in to reply to this topic.