Database Migration – Demographics
Migrating demographics from another EMR is simple, but time consuming
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.
- 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 ©
It is assumed that you or the implementation person(s) will require
- Some knowledge of data structures of your existing EMR
- Knowledge of data structures of OSCAR and their relationships
- a demographics file of your patients in CSV format
- a thorough understanding of the MySQL command line
- a TEST database to test your queries against
- 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
|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.
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.
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
- to save in CSV format.
- Windows line endings are \l\n
- Linux line endings are \n
- remove the heading line
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
- 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
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
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`='';
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;