Search the OSCAR Documentation
< All Topics
Print

Data Model

Data Model

The following represents the OSCAR 19 data model.. and more or less the data model for all OSCAR based versions and forks.  My production OSCAR 19 has 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

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
  • allergyid = 104
  • demographic_no = 5206
  • entry_date = 2007-03-22
  • DESCRIPTION = PENICILLINS
  • HICL_SEQNO = 0
  • HIC_SEQNO = 0
  • AGCSP = 0
  • AGCCS = 0
  • TYPECODE = -1 0 8 ATC class 10 AHFS 11 Generic 12 Compound 13 Brand 14 Ingredient
  • reaction = rash
  • drugref_id = 24160
  • archived = 0 or 1
  • start_date = NULL or 0000-00-00 or date
  • age_of_onset = NULL or age
  • severity_of_reaction = 0-4
  • onset_of_reaction = integer
  • regional_identifier = NULL
  • life_stage = NULL
  • position = 0
  • lastUpdateDate = datetime
  • providerNo = NULL
  • nonDrug = 0 or 1
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

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

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)
  • 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
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.
  • id
  • prevention_id = id in preventions table
  • keyval = name etc
  • val = FLUZONE® Quadrivalent (Inf)
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 origional 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 parentConceptIdFor example for this brand concept there is an entry with snomedConceptId = 19401000087100 with id 7655
  • 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))
  • typicalDose = 0.5
  • typicalDoseUofM = mL
  • route = route code eg 78421000 for IM
  • strength = see Product Monograph
  • shelfStatus = Marketed
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
  • id = 85420
  • language = en or fr
  • useSystem = https://cvc.canimmunize.ca/v3/NamingSystem/ca-cvc-display-terms-designation
  • useCode = enClinicianPicklistTerm
  • useDisplay = Clinican Tradename Picklist (en)
  • value = FLUZONE® Quadrivalent (Inf)
  • CVCImmunizationId =7766
CVCMapping
  • id
  • oscarName
  • cvcSnomedId
  • preferCVC
CVCMedication
These are the actual brand name immunizations available
  • 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
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.
  • id = 5016
  • cvcMedicationId = 4244
  • gtin= 697177005114
CVCMedicationLotNumber
An optional many to one 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
LookupList
two lines are relevant for CVC derived routes of administraion and anatomical sites.  Reason codes for appointments, the health unit list and some other lists are also stored here and in LookupListItem
  • id = 6
  • name = routes of administration
  • listTitle = Routes of Administration
  • description = Routes of Administration from CVC
  • categoryId = NULL
  • active = 1
  • createdBy = OSCAR
  • dateCreated = timestamp
LookupListItem
Here items that match the LookupList.name are stored.  For prevention it stores CVC derived routes of administration and  anatomical sites
  • id = 863
  • LookupListId = 6
  • value = 26643006
  • label = Oral: PO
  • displayOrder = 0
  • active = 1
  • createdBy = OSCAR
  • dateCreated = timestamp

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
  • ccsID
  • location
measurementGroup
contains the list of measurement groups
it can be empty
  • id
  • name = the group name eg vitals
  • typeDisplayName = one row for each type eg HT
  • archived present in Well usually 0
measurementGroupStyle
maps input group CSS for which input Group
it can be empty
  • groupID
  • groupName = the group name eg vitals
  • cssID = matching cssID in measurementCSSLocation or 0
measurementMap
maps LOINC and identification codes and lab types to measurements
  • id
  • loinc_code = X100666
  • ident_code = VDR
  • name = VDRL
  • lab_type = ICL
measurements
this is the main measurements table. The Well version has extra fields
  • id
  • type = PLT
  • demographicNo = 2304
  • providerNo = 0 if from labs
  • dataField = 175
  • measuringInstructions = x10 9/L Range 150-400
  • comments = none if from lab
  • dateObserved = date time
  • dateEntered = date time
  • appointmentNo = 0 if from lab
measurementsDeleted
where deleted measurements get archived
ALMOST the same as measurements

  • id
  • type = PLT
  • demographicNo = 2304
  • providerNo = 0 if from labs
  • dataField = 175
  • measuringInstructions = x10 9/L Range 150-400
  • comments = none if from lab
  • dateObserved = date time
  • dateEntered = date time
  • dateDeleted date time
  • originalId
measurementsExt
additional key value fields
  • id
  • measurement_id = PLT
  • keyval = 2304
  • val = 0 if from labs
measurementType
a list of active measurement types
  • id
  • type = ESS
  • typeDisplayName= Epworth
  • typeDescription = Epworth Sleepiness Scale
  • measuringInstruction
  • validation = from validations table eg 4
  • createDate = date time
  • parent_type in Well
measurementTypeDeleted
a list of inactive measurement types
  • id
  • type = HR
  • typeDisplayName= Pulse
  • typeDescription = Heart Rate
  • measuringInstruction = in bpm (nnn) Range 40-100
  • validation = from validations table eg 5
  • createDate = date time
  • dateDeleted = date time
  • prev_type_id in Well
validations
a list of validation types
  • id = 2
  • name = Numeric Value: 0 to 10
  • regularExp = NULL
  • maxValue1 = 10
  • minValue = 0
  • maxLength = NULL
  • minLength = NULL
  • isNumeric = 1
  • isTrue = NULL
  • isDate = NULL

Documents Data Model

Table Name Description
document
the primary table that references documents for both patients and users
  • document_no = 2
  • doctype = photo
  • docdesc = mid back
  • docxml =
  • docfilename = 20241210000notapatient.jpg
  • doccreator = 919
  • responsible =
  • source =
  • program_id = usually 10016
  • updatedtime = date time
  • status = A
  • contenttype = image/jpeg
  • public1 = 0
  • observationdate = date
  • reviewer = NULL
  • reviewdatetime = NULL
  • number_of_pages = 0
  • appointment_no = 0
  • docClass =
  • docSubClass =
  • sourceFacility =
  • contentdatetime = date time
  • restrictToProgram = 0
  • receivedDate = NULL
  • abnormal = 0
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
  • module = demographic or provider
  • module_id = -1  or number eg 101
  • document_no = 8366
  • status = A H or S
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
  • provider_no = 101
  • lab_no = 283087
  • status =
    • F filed
    • N new
    • A acknowledged
    • X unlinked
  • comment = NULL
  • timestamp = date time
  • lab_type = DOC or Hl7 or CML …
  • id
ctl_doctype
is the table that maps which sets the document types available to use to categorize documents.
  • module = provider or demographic
  • doctype = Pharmacy
  • status = A,I
  • id
documentDescriptionTemplate
is the table that sets template descriptions for any document types available.
  • id
  • doctype = matching ctl_doctype eg Lab
  • description = Haematology
  • descriptionShortcut = label for the button eg Haem
  • provider = NULL
  • lastUpdated = date time
queue
is the table that enumerates and describes available queue
  • id = 1
  • name = default
queue_document_link
is the table that describes documents and their queue
  • id
  • queue_id = queue number, default is 1
  • document_id = Link to the document table
  • status =
    • A active ie visible
    • I inactive not shown
    • NULL

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)

HL7 Labs Data Model

HL7 labs are primarily listed in the hl7TextMessage table.  NOTE that the providerLabRouting  table documented above does double duty for HL7 labs AND scanned documents.

Table Name Description
hl7TextMessage
the primary table that contains the HL7 lab
 

  • lab_id = 881910 this is the primary reference number
  • message = is a hash of the HL7 formatted lab result
  • type =
    • HL7
    • CML
    • MDS
    • M
    • U
  • fileUploadCheck_id = 696696
  • serviceName =oscar_peter…
  • created = date time

 

The details of the labs are listed in the hl7TextInfo table.

Table Name Description
hl7TextInfo
the primary table that has abstracted metadata for the HL7 lab
  • id = 881916
  • lab_no = 881910
  • sex =
    • NULL
    • F
    • M
    • U
  • health_no = 1234567890
  • result_status =
    • NULL
    • A
  • final_result_count = not versions but results
  • obr_date = date time
  • priority =
    • NULL
    • A
    • R routine
    • S
  • requesting_client = P Hutten-Czapski
  • discipline = INR, HEMATOLOGY etc
  • last_name = SMITH
  • first_name = JOHN
  • report_status =
    • F for “final” but sometimes there is more F labs coming
    • P partial
    • A
    • D
    • X …. and others
  • accessionNum = lab provided number that uniquely identifies all the versions
  • filler_order_num = usually blank
  • sending_facility = usually blank
  • label =user supplied override for discipline

The HL7HandlerMSHMapping table is potentially of importance in some Southern Ontario instances.

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
  • fid = 202
  • form_name = Invoice
  • file_name = HFHTinvoice.html
  • subject = admin
  • form_creator = NULL
  • status = 0
  • form_html =<html>
  • patient_independent = 0
  • roleType = NULL
  • showLatestFormOnly = 0
  • programNo = NULL not in Well
  • restrictToProgram = 0 not in Well

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
  • fdid = 70984
  • fid = 95
  • form_name = Ontario Lab
  • subject = Hypertension
  • demographic_no = 3421
  • status = 0
  • form_date = 2024-09-28
  • form_time = 16:40:27
  • form_provider = 101
  • form_data =<html>
  • patient_independent = 0
  • roleType =
  • showLatestFormOnly = 0

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
  • id = 5593207
  • fdid = 94364
  • fid = 202
  • demographic_no = 6382
  • var_name = subject
  • var_value = New Onset AF

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
  • id = 1
  • fid = 202
  •  group_name = Admin

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: Colcamax Resources, Marc Dumontier, Adrian Starzynski

Table of Contents