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.
Patient Demographic Data Model
| Table Name | Description |
|---|---|
| client_image | for the image in the echart.stores the byte contents |
| 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 | old table. Isn’t used anymore |
| 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 |
| 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 |
|---|
| drugs |
| DrugDispensing |
| DrugDispensingMapping |
| DrugProduct |
| DrugProductTemplate |
| allergies |
| drugReason |
| favorites |
| favoritesprivilege |
| pharmacyInfo |
| prescribe |
| prescription |
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 |
|---|
| security |
| secRole |
| secObjPrivilege |
| secObjectName |
| secPrivilege |
| secUserRole |
Consultation Data Model
| Table Name |
|---|
| consultationRequestExt |
| consultationRequests |
| consultationResponse |
| consultationServices |
| consultdocs |
| consultResponseDoc |
| professionalSpecialists |
| specialistsJavascript |
Preventions / Immunizations Data Model
These tables represent preventions in OSCAR.
| Table Name | Description |
|---|---|
| preventions | This is where each provided prevention is stored. id = 101035 demographic_no = 21920 creation_date = 2023-12-01 12:32:42 prevention_date = 2023-12-01 12:32:00 provider_no = -1 provider_name = NULL prevention_type = [Inf] Influenza quadravalent deleted = 0 refused = 3 next_date = NULL never = 0 creator = 942 lastUpdateDate = 2023-12-01 12:32:42 restrictToProgram = usually NULL programNo = NULL if restrictToProgram is NULL snomedId = 19401000087100 used for CVC immunizations. This is the generic concept and not the brand |
| preventionsExt | This is a key value pair listing of details of each prevention . key value pairs can be providerName = outside provider, name = FLUZONE® Quadrivalent (Inf), brandSnomedId = 20691000087107, lot = U8098BA NOTE often the key is persisted with an empty value and Snomed mapping is only provided for CVC immunisations id prevention_id = id in preventions table keyval val |
| PreventionsLotNrs | Rarely used this is a place to put LotNrs by generic term in the “old type” preventions. eg Flu id creationDate providerNo preventionType lotNr deleted lastUpdateDate |
| config_Immunization *obsolete* | This contains an XML definition of old style prevention sets. Controlled through the orphaned page CreateImmunizationSetConfig.jsp setId setName = Routine Infants & Children setXmlDoc createDate providerNo archived |
| immunizations *obsolete* | ID demographic_no provider_no immunizations = XML save_date archived |
| 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 parentConceptId id = 7766 versionId = 0 snomedConceptId = 20691000087107 (either Brand or as a generic concept) displayName = NULL picklistName = NULL generic = 0 (1 if generic concept) prevalence = NULL parentConceptId = 19401000087100 (present only if a Brand) ispa = 0 (1 if listed in the Ontario’s Immunization of School Pupils Act (ISPA)) typicalDos = 0.5 typicalDoseUofM = mL strength = see Product Monograph shelfStatus = Marketed |
| CVCMapping | id oscarName cvcSnomedId preferCVC |
| CVCMedication | These are the actual brand name immunizations available eg Adacel(R) id = 4244 versionId = 0 din = 2420643 dinDisplayName = 02420643 snomedCode = 20691000087107 snomedDisplay = FLUZONE Quadrivalent 15 micrograms per 0.5 milliliter suspension for injection Sanofi Pasteur Limited (product) status = Marketed isBrand = 0 manufacturerId = NULL manufacturerDisplay = Sanofi Pasteur Ltd |
| CVCMedicationGTIN | A one to many 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. id = 5016 cvcMedicationId = 4244 gtin= 697177005114 |
| CVCMedicationLotNumber | A one to many list of lot number for a given brand CVC Medication Id in the CVCMedication table. id = 35362 cvcMedicationId = 4244 lotNumber = U8098BA expiryDate = 2024-06-30 |
Measurements Data Model
OSCAR has an advanced measurement tracking system which uses several tables.
measurementCSSLocation records the location of CSS files for styling measurement input groups
measurementGroup contains the list of measurement groups
measurementGroupStyle maps which CSS stylesheets are used for an input group (links to cssID in measurementCSSLocation)
measurementMap maps LOINC and identification codes and lab types to measurements
measurementsDeleted is where deleted measurements are moved to
measurements is the main table where entered (active / not deleted) measurements and their values are stored (i.e. the measurement type HT for height with a value of 175 for demographic # 1042 entered by provider # 123 on January 20th 2024, etc. will be stored here).
measurementsExt contains key-value pairs linked to the measurements table by measurement_id (i.e. what lab result ID was it linked to, what’s the range, etc.)
measurementType contains the list of active measurement types
measurementTypeDeleted is where deleted measurement types are stored
Documents Data Model
document is where information about documents is stored.
Since OSCAR can treat documents separately from patients, and documents can be unlinked/relinked to different patients, 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 and lab reports are in which provider’s inbox. It has the lab_no (where lab_type=’DOC’) and provider number.
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).
When an eForm is saved as part of a patient’s chart, that is in the ‘eform_data’ table. This is how Oscar records which checkboxes have been checked, which fields are filled, who saved the eForm, etc.
Ontario Billing
Here are table descriptions for some of the Ontario billing tables in OSCAR:
billing_on_eareport
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, Adrian Starzynski