Search the OSCAR Documentation
< All Topics
Print

OSCAR EMR Data Model

Share this

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 NameDescription
client_imagefor the image in the echart.stores the byte contents
demographicprimary table which represents a patient in the system. demographic_no is the primary identifier
demographic_mergedmerged records are maintained here. headRecord is the active record, and the others are inactive records which link back to head.
demographicaccessoryold table. Isn’t used anymore
demographicArchivearchived version of every change to a demographic record
DemographicContactrelationship table which represents a demographic’s association with personal and professional contacts. Links to different tables based on the type
demographiccustcontains a few values from the master record like note, alert and midwife
demographiccustArchivearchives of demographiccust entries
demographicExtkey-value pairs for demographic. Simple way to add custom attributes. For example, the patient cell phone is stored here.
demographicExtArchivearchived version of all key-value pairs
demographicPharmacyrelationship table for preferred pharmacy used in Rx module

eChart Data Model

Encounter notes and CPP are stored in the casemgmt_ tables:

Table NameDescription
casemgmt_cppTable where all the CPP data is held (including social history, family history, medical history, ongoing concerns, reminders…)
casemgmt_dx_linkLocation where diagnostic codes are linked to encounter notes
casemgmt_issueLocation where assigned issues are stored, including issue types, statuses, etc.
casemgmt_issue_notesLocation where issues are linked to encounter notes
casemgmt_noteTable where all the encounter notes are stored
casemgmt_note_extTable where key value pairs related to encounter notes are stored
casemgmt_note_linkLocation where notes are linked to various foreign tables
casemgmt_note_lockLocation 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_tmpsaveLocation for draft (temporarily saved) encounter notes

Ticklers Data Model

Table NameDescription
ticklerMain table where tickler data is held
tickler_linkThese are special links to Notes or other entities
tickler_category
tickler_commentsWhen editing ticklers, comments can be created
tickler_updateAll 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 NameDescription
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
PreventionsLotNrsRarely 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
CVCImmunizationThis 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
CVCMappingid
oscarName
cvcSnomedId
preferCVC
CVCMedicationThese 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
CVCMedicationGTINA 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
CVCMedicationLotNumberA 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

Table of Contents