Database Migration – Demographics

Migrating demographics from another EMR is simple, but time consuming

Preface

If you had the misfortune to start with a proprietary EMR that does not meet your needs or is no longer supported you want to move your data to OSCAR.

This is bread and butter for OSCAR Service Providers (OSP’s) who do this competently and usually charge about $1000 for the demographics portion.

Additional components are more work and more money than is described here.

Document Version History

• v1.0 – initial public release on oscarmanual.org – Oct 10, 2014
• v1.1 – added info on additional demographic tables – Oct 16, 2014
• v1.2 – corrected DOB conversion to give zero padding – Oct 16, 2014
• v1.3 – added in hc_type into demographics – Dec 17, 2014
• v1.4 – ported to WorldOSCAR and lightly dusted – Oct 10, 2023
• v2.0 – Revised public release on WorldOSCAR.org – Oct 17, 2023

This document is copyright by Peter Hutten-Czapski 2014-2023 ©

Prerequisites

It is assumed that you or the implementation person(s) will require

1. Some knowledge of data structures of your existing EMR
2. Knowledge of data structures of OSCAR and their relationships
3. a demographics file of your patients in CSV format
4. a thorough understanding of the MySQL command line
5. a TEST database to test your queries against
6. About 8 hours to write test and deploy the scripts

How to Convert Demographics

This is how to transfer demographics from another system to OSCAR via SQL

The work is initially done on a TEST database and when successful is done  (much quicker) again on the OSCAR production database.

Patient Demographic Tables

Table Name	Description
admission	Controls access to the patient data. There has to be 1 admission record for every active demographic in any program
client_image	Stores the byte contents of the patient image as displayed in the echart.  Optional
demographic	This is the main table which represents a patient in the system. demographic_no is the primary identifier
demographic_merged	Merged records are maintained here. headRecord is the active record, and the others are inactive records which link back to head. Optional
demographicaccessory	Its an old table. Isn’t used anymore, can be ignored.
demographicArchive	Archived version of every change to a demographic record.  Consider adding to it to help resolve errors that occur when you overwrite new data from another source.
DemographicContact	Relationship table which represents a demographic’s association with personal and professional contacts. Links to different tables based on the type.  Optional
demographiccust	This contains a few values from the master record like note, alert and midwife. OSCAR will allow 0 but better 1 entry for each record in ‘demographic’
demographiccustArchive	This maintains archives of demographiccust entries
demographicExt	key-value pairs for demographic. For example, the patient cell phone is stored here. Other data, including that from a foreign EMR, should be placed here if there is no better spot.  The UI can be configured to show this information in the Master Demographic. Optional
demographicExtArchive	Archived version of all key-value pairs
demographicPharmacy	Relationship table for preferred pharmacy used in Rx module, can be empty

While the entire Data Model CAN be configured, only the bare minimum, importing to the demographic and the admission tables, is described below.

Export data to a Spreadsheet

Lets assume you used your previous program to export a report on your demographics and got the following fields

File Number

First Name

Last Name

DOB

H/C

version

Home Phone

Address 1

Address 2

City

Province

Patient Rostered

Rostered Date

Sex

00031447-00

Abra

Cadabra

13/07/2001

1999999998

XP

(705) 647-1111

101 Farah Ave.

Box 22

New Liskard

ON

Patient

07/10/2009

F

This is all very fine, but as per usual there are a number of problems with the file

• The length of the File Number (12) is larger than the chart_no in OSCAR (default 10), it’s a simple update to fix
• The Date of Birth is one field while OSCAR divides the year month and date of birth
• The Address is in two lines while OSCAR uses only one line
• New Liskeard is incorrectly spelled
• The indication of Roster status is not the same as in OSCAR (which uses RO)
• Various obligatory fields in OSCAR are missing.

Clean up Your Act

Now is a good time to use a spread sheet and sort and fix misspellings, missing data, and paste large numbers of OSCAR specific “RO” into place

Libre Office Calc is a suitable spread sheet

Remember

• to save in CSV format.
• Windows line endings are \l\n
• Linux line endings are \n
• remove the heading line

Load Data Infile

Now use the power of MySQL to load the file and transform and add all the pieces that OSCAR expects into a MySQL table that resembles the demographics one

First make a temporary table for the data to test your work.  As elsewhere in the manual replace ****** with your MySQL password

$ mysql -uroot -p****** oscar_15 < CREATE TABLE stacy LIKE demographic;

Now tweak it so that the demographic_no does not get auto filled (you will need to drop the key as well)

ALTER TABLE `stacy` CHANGE `demographic_no` `demographic_no` INT( 10 ) NOT NULL;

ALTER TABLE `stacy` DROP PRIMARY KEY;

Now for a CSV file that you have cleaned that has lines that look like the following

00031447-00,Abra,Cadabra,13/07/2001,1999999998,XP,(705) 647-1111,101 Farah Ave.,Box 22,New Liskeard,P0J 1P0,ON,RO,07/10/2009,F

Try something like

$ mysql -uroot -p****** oscar_12_1
mysql> LOAD DATA INFILE '/home/peter/Downloads/10092014B.csv' INTO TABLE `stacy` FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'
    -> ( `chart_no` , `first_name` , `last_name` , @DOB , `hin` , `ver` , `phone` , @Add1 , @Add2 , `city` , `postal` , `province` , `roster_status` , @roster_date, `sex` )
    -> set
    -> roster_date=str_to_date(@roster_date,'%d/%m/%Y'),
    -> year_of_birth=DATE_FORMAT(@DOB,'%Y')),
    -> month_of_birth=DATE_FORMAT(@DOB,'%m')),
    -> date_of_birth=DATE_FORMAT(@DOB,'%d'),
    -> address=CONCAT_WS(', ',@Add1,@Add2),
    -> hc_type=province,
    -> provider_no='124',
    -> patient_status='AC',
    -> date_joined='2009-01-01',
    -> official_lang='English',
    -> family_doctor='<rdohip></rdohip><rd></rd>',
    -> country_of_origin='-1',
    -> newsletter='Unknown',
    -> lastUpdateUser='101',
    -> lastUpdateDate='2014-10-01 00:00:00',
    -> patient_status_date='2014-10-01';
Query OK, 674 rows affected, 2 warnings (0.03 sec)
Records: 674  Deleted: 0  Skipped: 0  Warnings: 2

Notes

• there is an entry in the list of fields for every field in the CSV file in their natural order
• the str_to_date function converts the string in the CSV to MySQL date type
• the YEAR, MONTH and DATE functions are used to extract those specific fields from the combined DOB found in the other EMR
• the CONCAT_WS function is used to combine the two address fields with a ‘, ‘ between them into the OSCAR address field
• various defaults are set based on the data used NOTE that OSCAR wants zero padded date_of_birth and month_of_birth
• ANY warnings should worry you, review the output to see what you may still need to fix
  • use SHOW WARNINGS to determine the warnings from the last executed statement
• Be particularly suspicious of SQL’s that take long to complete, your table joining syntax is probably off and so will be the data
• You HAVE TO test the data conversion for EACH table to ensure that the conversion is complete and correct

Determine Duplicates Already in OSCAR

You can skip this step if you are starting with an empty OSCAR.  however if you are merging in a new list into an existing production database, you will not want to add duplicate entries!

First just grab the demographic_no from the OSCAR installation where the health insurance numbers match (requires unique hin’s to work)

mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no WHERE s.hin = d.hin AND s.`hin` <> ''
Query OK, 125 rows affected (0.12 sec)
Rows matched: 125  Changed: 125  Warnings: 0

Then if your import contains any files you want to transfer that have a blank hin you should try to match those by name and DOB

mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no 
    -> WHERE s.`hin` = '' AND d.last_name=s.last_name AND d.first_name=s.first_name AND 
    -> d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now the table `stacy` has a hundred twenty six demographics that match the production database identified by a non blank s.demographic_no.

ASSUMING that the incoming charts are more current lets overwrite the existing with the new.

mysql> UPDATE `demographic` d, `stacy` s SET
    -> d.`address`=s.`address`,
    -> d.`ver`=s.`ver`,
    -> d.`city`=s.`city`,
    -> d.`province`=s.`province`,
    -> d.`postal`=s.`postal`,
    -> d.`phone`=s.`phone`,
    -> d.`roster_status`=s.`roster_status`,
    -> d.`patient_status`=s.`patient_status`,
    -> d.`date_joined`=s.`date_joined`,
    -> d.`chart_no`=s.`chart_no`,
    -> d.`official_lang`=s.`official_lang`,
    -> d.`provider_no`=s.`provider_no`,
    -> d.`roster_date`=s.`roster_date`
    -> d.`patient_status_date`=s.`patient_status_date`
    -> WHERE d.`demographic_no`=s.`demographic_no` ;
Query OK, 126 rows affected (0.93 sec)
Rows matched: 126  Changed: 126  Warnings: 0

The above is a bit brutish of an approach however as mentioned it depends on the assumption that the incoming data is better than the old.  Even if true in the aggregate, that may not be true in a specific.  You risk overwriting a good work telephone number with a NULL, or worse still a version code with an empty string.  Better would be a stored procedure that goes through the matching demographics one row at a time and does logic to effect the merge based on empty or NULL values and the dates, and copying the existing into entries in the Archive tables.

Adding New Patients into OSCAR

Load the unique new entries into OSCAR

mysql> INSERT INTO `demographic` 
    -> (`title`, `last_name`, `first_name`, `address`, `city`, `province`, `postal`, `phone`, `phone2`, `email`, `myOscarUserName`, `year_of_birth`, `month_of_birth`, `date_of_birth`, `hin`, `ver`, `roster_status`, `patient_status`, `date_joined`, `chart_no`, `official_lang`, `spoken_lang`, `provider_no`, `sex`, `end_date`, `eff_date`, `pcn_indicator`, `hc_type`, `hc_renew_date`, `family_doctor`, `alias`, `previousAddress`, `children`, `sourceOfIncome`, `citizenship`, `sin`, `country_of_origin`, `newsletter`, `anonymous`, `lastUpdateUser`, `lastUpdateDate`, `roster_date`, `patient_status_date`, `roster_termination_date`, `roster_termination_reason`)
    -> SELECT `title`, `last_name`, `first_name`, `address`, `city`, `province`, `postal`, `phone`, `phone2`, `email`, `myOscarUserName`, `year_of_birth`, `month_of_birth`, `date_of_birth`, `hin`, `ver`, `roster_status`, `patient_status`, `date_joined`, `chart_no`, `official_lang`, `spoken_lang`, `provider_no`, `sex`, `end_date`, `eff_date`, `pcn_indicator`, `hc_type`, `hc_renew_date`, `family_doctor`, `alias`, `previousAddress`, `children`, `sourceOfIncome`, `citizenship`, `sin`, `country_of_origin`, `newsletter`, `anonymous`, `lastUpdateUser`, `lastUpdateDate`, `roster_date`, `patient_status_date`, `roster_termination_date`, `roster_termination_reason`
    -> FROM `stacy` s
    -> WHERE s.`demographic_no`='' ;
Query OK, 548 rows affected (1.14 sec)
Records: 548  Duplicates: 0  Warnings: 0

Admit the New Patients

Now you need to load these patients into the admission table to the OSCAR program (10016) so that they can be accessible in OSCAR’s GUI

INSERT INTO `admission`(client_id,program_id,provider_no,admission_date,admission_status,team_id,temporary_admission_flag,lastUpdateDate)  
SELECT d.demographic_no,'10016',s.provider_no,'2014-10-01','current',0,0,'2014-10-01 00:00:00'
FROM `stacy` s, `demographic` d
WHERE s.`hin`=d.`hin` AND
d.last_name=s.last_name AND d.first_name=s.first_name AND 
d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth AND
s.`demographic_no`='';

Finishing Up

If you are going to do any more data importation you will need a table that cross references the older EMR’s chart numbers with OSCAR’s demographic.

Add in the demographics of the new patients for those with a hin

mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no WHERE s.hin = d.hin AND s.`hin` <> ''

Then if your import contains any files you want to transfer that have a blank hin you should dry to match those by name and DOB

mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no -> WHERE s.`hin` = '' AND d.last_name=s.last_name AND d.first_name=s.first_name AND -> d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth;