OSCAR EMR Data Model

These data models may differ slightly from one OSCAR version to another.

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

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
preventions
preventionsExt
PreventionsLotNrs
config_Immunization
immunizations
CVCImmunization
CVCMapping
CVCMedication
CVCMedicationGTIN
CVCMedicationLotNumber

Ontario Billing

Here are table descriptions for some of the Ontario billing tables in OSCAR:

billing_on_eareport

billing_on_cheader1

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.

Sources

Marc Dumontier, Colcamex Resources, Adrian Starzynski