Search the OSCAR Documentation
OSCAR EMR Data Model
These data models may differ slightly from one OSCAR version to another.
You can go to Administration>Reports>Query By Example and run “SHOW TABLES;” to see a list of tables on your OSCAR installation.
This article helps in development of Report Templates.
The following represents the OSCAR 19 data model, and more or less the data model for all OSCAR based versions and forks. A production OSCAR 19 install can have around 658 tables. Some are not used and obscure. Others have lots of entries in almost every OSCAR installation known. This is a working list of some of those.
Patient Demographic Data Model
| Table Name | Description |
|---|---|
| client_image | The byte contents for the image in the echart. |
| demographic | primary 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. |
| demographicaccessory | obsolete |
| demographicArchive | archived version of every change to a demographic record |
| DemographicContact | relationship table which represents a demographic’s association with personal and professional contacts. Links to different tables based on the type |
| Contact | older relationship table |
| demographiccust | contains a few values from the master record like note, alert and midwife |
| demographiccustArchive | archives of demographiccust entries |
| demographicExt | key-value pairs for demographic. Simple way to add custom attributes. For example, the patient cell phone is stored here. |
| demographicExtArchive | archived version of all key-value pairs |
| demographicPharmacy | relationship table for preferred pharmacy used in Rx module |
eChart Data Model
Encounter notes and CPP are stored in the casemgmt_ tables:
| Table Name | Description |
|---|---|
| casemgmt_cpp | Table where all the CPP data is held (including social history, family history, medical history, ongoing concerns, reminders…) |
| casemgmt_dx_link | Location where diagnostic codes are linked to encounter notes |
| casemgmt_issue | Location where assigned issues are stored, including issue types, statuses, etc. |
| casemgmt_issue_notes | Location where issues are linked to encounter notes |
| casemgmt_note | Table where all the encounter notes are stored |
| casemgmt_note_ext | Table where key value pairs related to encounter notes are stored |
| casemgmt_note_link | Location where notes are linked to various foreign tables |
| casemgmt_note_lock | Location where session ID’s, IP addresses, provider ID, note ID, demographic #, etc. are stored for encounter notes that are “locked” / in progress (to prevent multiple users from trying to edit the same note at the same time) |
| casemgmt_tmpsave | Location for draft (temporarily saved) encounter notes |
Ticklers Data Model
| Table Name | Description |
|---|---|
| tickler | Main table where tickler data is held |
| tickler_link | These are special links to Notes or other entities |
| tickler_category | |
| tickler_comments | When editing ticklers, comments can be created |
| tickler_update | All tickler update history is stored here |
| tickler_text_suggest |
Medication / Prescriptions / Drugs Data Model
Drugs / Rx / Allergy Data Model
| Table Name | description |
|---|---|
| drugs | the main table of one drugId for each medication Rx |
| DrugDispensing | for dispensing from a local formulary |
| DrugDispensingMapping | for dispensing from a local formulary |
| DrugProduct | for dispensing from a local formulary |
| DrugProductTemplate | for dispensing from a local formulary |
| allergies | allergies |
| drugReason | one drugs.drugId to many drugReason.id AND many drugReason.id to drugs |
| ICD9 | the most common drugReason.codingSystem |
| DxCodeTranslation | ICD9.icd9 1:1 with patient friendly icd9 terms |
| favorites | saved drugs/instructions for reuse |
| favoritesprivilege | is the favorite public and or writable |
| pharmacyInfo | list of Pharmacies and their details |
| demographicPharmacy | which pharmaci(es) are attached to a demo and in what rank order |
| prescribe | not used |
| prescription | one script_no to many drugs Rx in textView |
eFax Consultation Request Data Model
Contacts Data Model (with Health Care Team)
Security Tables
The security tables are responsible for logins, and role based security.
| Table Name | Description |
|---|---|
| security | username password hash totp pin |
| secRole | role_no and role eg doctor |
| secObjPrivilege | rows for each role and object name eg doctor _admin.provider |
| secObjectName | object code eg _admin.fax and description Configure and Manage Faxes |
| secPrivilege | 6 static one letter code privilege and their corresponding description |
| secUserRole | each provider to many security roles |
| log | a large file that logs each action of every user |
Consultation Data Model
| Table Name | Description |
|---|---|
| consultationRequestExt | key-value pairs for apptNo letterhead. Has archive |
| consultationRequests | the main consult request |
| consultationResponse | not used |
| consultationServices | types of consultation Services |
| consultdocs | attachments (D)ocument (E)form (L)ab and their id |
| consultResponseDoc | not used |
| professionalSpecialists | lists of Specialists |
| specialistsJavascript | Javascript to populate the specialist picklists in the consutation request form |
| faxes | details of the Consult_nnn.pdf file fax sending and date time |
Every specialist is stored in professionalSpecialists. Consultation services are stored in consultationServices. Specialists are linked to services via serviceSpecialists (serviceId from consultationServices, specId from professionalSpecialists).
Preventions / Immunizations Data Model
These tables represent preventions in OSCAR.
| Table Name | Description |
|---|---|
| preventions This is where each provided prevention is stored.The prevention_type is either the OSCAR 9 style eg Flu or taken from CVCImmunizationName Clinician Tradename Picklist (en) for the generic prevention concept eg [Inf] Influenza quadravalentNote snomedId is only provided for CVC style preventions and represents the generic concept (not the brand snomed) |
|
| preventionsExt This is a key value pair listing of details of each prevention many to one linked to the prevention_id in the preventions table . key value pairs can be one ofcomments, neverReason, lot, manufacture, name, result, reason, location, route, dose, providerName, din, location2, brandSnomedId, previousId, expiryDate, locationsDisplay, routeDisplay, chronic, healthcareworkerNOTE Often the key is persisted with an empty value. |
|
| PreventionsLotNrs Rarely used this is a place to put LotNrs by generic term in the “old type” preventions. eg Flu |
|
| config_Immunization *obsolete* This contains an XML definition of old style prevention sets. Controlled through the orphaned page CreateImmunizationSetConfig.jsp |
|
| immunizations *obsolete* |
|
| CVCImmunization This provides the details such as typical dose, the snomed id and the parent concept id and if the concept is generic. There is only one entry for Generic but there might be many brand exemplars who map to the parentConceptIdFor example for this brand concept there is an entry with snomedConceptId = 19401000087100 with id 7655 |
|
| CVCImmunizationName This provides many to one details for the given CVCImmunizationId . There are multiple entries for each generic and trade entryThe generic entry for Clinician Tradename Picklist (en) is what is displayed eg [Inf] Influenza quadravalent in the Nav Side Bar with the Fully Specified Name eg Influenza quadrivalent vaccine (product) being the title |
|
| CVCMapping |
|
| CVCMedication These are the actual brand name immunizations available |
|
| CVCMedicationGTIN An optional many to one list of Global Trade Item Numbers (GTIN) codes for a given brand CVC Medication Id in the CVCMedication table. Vaccine 2D barcodes code the GTIN to identify the product (and also manufacturer), and the vaccine’s expiration date and lot number. |
|
| CVCMedicationLotNumber An optional many to one list of lot number for a given brand CVC Medication Id in the CVCMedication table. |
|
Older style preventions and non immunization preventions are controlled by XML files within source. PreventionItems.xml PreventionConfigSets.xml for structure. prevention.drl provides the decision logic for both old style and CVC preventions.
PreventionItems.xml:
<item resultDesc="" name="RSVPreF3" desc="Recombinant PreF RSV Vaccine eg Arexvy" healthCanadaType="RSVPreF3" link="https://ca.gsk.com/media/6988/arexvy.pdf" layout="injection" minAge="60" maxAge="100" atc="J07BX05" showIfMinRecordNum="1" />
To manually add a new immunization as a CVC entitity at a minimum you need to add one row each to CVCImmunization for the generic and brand concepts, several rows in CVCImmunizationName that link to the CVCImmunizationId for Clinician Picklist terms and a Fully Specified Name, and a CVCMedication row for the trade name linked by the Snomed id.
INSERT INTO `CVCImmunization` ( versionId, snomedConceptId, generic, parentConceptId, ispa, typicalDose, typicalDoseUofM, strength, shelfStatus)
VALUES ( '0', '51311000087100', '1', '', '0', '0.5', 'mL', 'see Product Monograph', 'Marketed');
SET @gen_imm_id = LAST_INSERT_ID();
INSERT INTO `CVCImmunization` ( versionId, snomedConceptId, generic, parentConceptId, ispa, typicalDose, typicalDoseUofM, strength, shelfStatus)
VALUES ( '0', '51301000087102', '0', '51311000087100', '0', '0.5', 'mL', 'see Product Monograph', 'Marketed');
SET @trade_imm_id = LAST_INSERT_ID();
INSERT INTO `CVCImmunizationName` (language,useSystem,useCode,useDisplay,`value`,CVCImmunizationId) VALUES
('en','http://snomed.info/sct', '900000000000003001','Fully Specified Name','Vaccine product containing only Human orthopneumovirus antigen (medicinal product)', @gen_imm_id),
('en','https://api.cvc.canimmunize.ca/v3/NamingSystem/ca-cvc-display-terms-designation', 'enClinicianPicklistTerm','Clinician Tradename Picklist (en)','[RSV] Respiratory syncytial virus', @gen_imm_id),
('en','https://api.cvc.canimmunize.ca/v3/NamingSystem/ca-cvc-display-terms-designation', 'enClinicianPicklistTerm', 'Clinician Tradename Picklist (en)', 'AREXVY (RSV)', @trade_imm_id);
INSERT INTO `CVCMedication` (versionId,din,dinDisplayName,snomedCode,snomedDisplay,status,isBrand,manufacturerDisplay)
VALUES ('0',2540207,'02540207', '51301000087102', 'AREXVY 120 micrograms per 0.5 milliliter powder and suspension for suspension for injection GlaxoSmithKline Inc', 'Marketed','0','GSK');
Measurements Data Model
OSCAR has an advanced measurement tracking system which uses several tables.
| Table Name | Description |
|---|---|
| measurementCSSLocation records the location of CSS files for styling measurement input groups it can be empty |
|
| measurementGroup contains the list of measurement groups it can be empty |
|
| measurementGroupStyle maps input group CSS for which input Group it can be empty |
|
| measurementMap maps LOINC and identification codes and lab types to measurements |
|
| measurements this is the main measurements table. The Well version has extra fields |
|
| measurementsDeleted where deleted measurements get archived |
ALMOST the same as measurements
|
| measurementsExt additional key value fields |
|
| measurementType a list of active measurement types |
|
| measurementTypeDeleted a list of inactive measurement types |
|
| validations a list of validation types |
|
Documents Data Model
| Table Name | Description |
|---|---|
| document the primary table that references documents for both patients and users |
|
| ctl_document is the table that maps which document_no is attached to which demographic_no (it stores which document is attached to which demographic), and the status of the link |
|
| providerLabRouting is the table that maps which document ID’s as well as Lab reports which are in which provider’s inbox. It has the lab_no (where lab_type=’DOC’) which corresponds to document.document_no |
|
The status column in providerLabRouting:
- F = filed
- N = new (not yet acknowledged)
- A = acknowledged
- X = provider removed (unlinked) from document
Documents that are only uploaded to the chart and not filed/acknowledged/linked to any inbox will not show up in providerLabRouting (since that table only tracks inbox assignments of documents)
eForms Data Model
The list of eForms is the ‘eform’ table. eForms are identified by form ID (fid).
| Table Name | Description |
|---|---|
| eform the primary table that contains the eforms you can choose from |
|
When an eForm is saved as part of a patient’s chart, it is added to the eform_data table. This is where OSCAR finds a previous eForm so that it can reload it
| Table Name | Description |
|---|---|
| eform_data there is one row for each saving of an eform |
|
While you can extract the filled values of interest from eform_data.form_data its easier to get them from eform_values
| Table Name | Description |
|---|---|
| eform_values Contain the values entered into each submitted eform.There is one row for each input name |
|
Eforms can be part of an eform_groups
| Table Name | Description |
|---|---|
| eform_groups Contain the eform fids for each group There is one row for each fid |
|
Lab Results Data Model
- HL7HandlerMSHMapping: maps hospital sites/facilities for lab reports (mainly only Ontario)
- hl7TextInfo: contains the extracted data from HL7 lab reports – health card #, result type (abnormal etc.), observation date, requesting physician, etc… Maps each entry to lab_id on hl7TextMessage, and the corresponding file checksum on fileUploadCheck
- hl7TextMessage: contains the HL7 data for all lab results
- patientLabRouting maps each entry in hl7TextMessagee to a demographic
- fileUploadCheck
Ontario Billing
Here are table descriptions for some of the Ontario billing tables in OSCAR:
billing_on_eareport
radetail
The radetail table updates invoices when you process a Remittance Advice.
Note: some billing rejections that aren’t in billing_on_eareport show up in radetail (ex. I9, I2, D6…) – these are rejections that OHIP doesn’t consider outright denials for some reason, even since they are rejected (“I6 premium not applicable”, “D3 service not allowed together”…)
billing_on_cheader1
billing_on_cheader1 ‘status’ column values (only the one letter in quotes goes into this column, ex. O):
- “H” Capitated
- “O” Bill OHIP
- “P” Bill Patient
- “N” Do Not Bill
- “W” Bill WSIB
- “B” Submitted OHIP
- “S” Settled
- “X” Bad Debt
- “D” Deleted Bill
- “I” Bonus Codes
billing_on_errorCode
This table simply contains the OHIP billing error/rejection codes:
billing_on_item
Please note – if you need to query billing items/invoices in Oscar you might find this useful:
When a bill is created it is stored in billing_on_cheader1 (includes the demographic_no, billing provider info, status etc.), the actual billing items (services codes) for the bill are stored in billing_on_item and are linked to the bill in billing_on_cheader1 by ch1_id.
billingservice
The billingservice table stores all the billing service codes. The difference between OHIP and 3rd party billing codes is that 3rd party (private pay) start with an underscore: _
ex. _FEECODE
Other Ontario Billing Tables
Depending on your Oscar version you may have a dozen more billing tables – ex. for tracking billing permissions, payments, etc.
Sources
Marc Dumontier, Colcamex Resources, Peter Hutten-Czapski, Adrian Starzynski