Search the OSCAR Documentation
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 |
|
| 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) |
|
| 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 origional 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. |
|
| 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 |
|
| LookupListItem Here items that match the LookupList.name are stored. For prevention it stores CVC derived routes of administration and anatomical sites |
|
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 |
|
| ctl_doctype is the table that maps which sets the document types available to use to categorize documents. |
|
| documentDescriptionTemplate is the table that sets template descriptions for any document types available. |
|
| queue is the table that enumerates and describes available queue |
|
| queue_document_link is the table that describes documents and their queue |
|
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 |
|
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 |
|
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 |
|
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 |
|
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