Compass Database Mappings: Difference between revisions
Line 637: | Line 637: | ||
|result_value | |result_value | ||
| | | | ||
|- | |-style='background-color: #ddd;' | ||
|referenceRange.low.unit | |referenceRange.low.unit | ||
|observation | |observation | ||
|result_value_units | |result_value_units | ||
| | | | ||
|- | |-style='background-color: #ddd;' | ||
|referenceRange.high.unit | |referenceRange.high.unit | ||
| | | | ||
| | | | ||
| | | | ||
|- | |-style='background-color: #ddd;' | ||
|referenceRange.low.value | |referenceRange.low.value | ||
| | | | ||
| | | | ||
| | | | ||
|- | |-style='background-color: #ddd;' | ||
|referenceRange.high.value | |referenceRange.high.value | ||
| | | |
Revision as of 13:33, 20 July 2021
Introduction
FHIR Mappings
Patient
property | table | column | foreign key |
---|---|---|---|
identifier.value | patient | nhs_number | |
name.family | patient | last_name | |
name.given | patient | first_names | |
name.middle | patient | first_names | |
name.prefix | patient | title | |
birthDate | patient | date_of_birth | |
gender | patient | gender_concept_id | |
deceasedDateTime | patient | date_of_death | |
address.line | patient_address | address_line_1 | |
address.line | patient_address | address_line_2 | |
address.city | patient_address | city | |
address.postalCode | patient_address | postcode | |
address.country | |||
address.use | patient_address | use_concept_id | |
address.district | patient_address | address_line_3 | |
telecom.value | patient_contact | value | |
telecom.use | patient_contact | use_concept_id | |
communication.language | |||
communication.preferred | |||
careProvider | patient | organization_id | organization.id |
careProvider - how are GP details populated? |
Practitioner
property | table | column | foreign key |
---|---|---|---|
identifier.value | practitioner | gmc_code | |
identifier.system | practitioner | ||
name.family | practitioner | name | |
name.given | practitioner | name | |
name.prefix | practitioner |
EpisodeOfCare
property | table | column | foreign key |
---|---|---|---|
startDt | episode_of_care | date_registered | |
endDt | episode_of_care | date_registered_end | |
careManager | |||
patient | episode_of_care | patient_id | patient.id |
Encounter
property | table | column | foreign key |
---|---|---|---|
class | encounter | admission_method | |
contained.Parameters.parameter.name | |||
contained.Parameters.parameter.valueCodeableConcept.coding.code | |||
contained.Parameters.parameter.valueCodeableConcept.coding.system | encounter_additional_property | property_id & value_id | |
type | encounter | admission_method | |
id | encounter | id | |
period.start | encounter | clinical_effective_date | |
period.end | encounter | end_date | |
location - how is the location table referenced? | encounter | institution_location_id | |
patient | encounter | patient_id | patient.id |
participant | encounter | practitioner_id | practitioner.id |
serviceProvider | encounter | service_provider_organization_id | organization.id |
episodeOfCare | encounter | episode_of_care_id | episode_of_care.id |
Organization
property | table | column | foreign key |
---|---|---|---|
identifier.name | organization | name | |
identifier.value | organization | ods_code | |
identifier.system | |||
identifier.type | |||
type.system | |||
type.code | organization | type_code | |
type.display | organization | type_desc |
Location
property | table | column | foreign key |
---|---|---|---|
identifier | |||
status | |||
name | location | type_desc | |
description | location | name |
AllergyIntolerance
property | table | column | foreign key |
---|---|---|---|
category | |||
substance.coding.code | |||
substance.coding.display | |||
substance.coding.system | |||
reaction[].severity.code | |||
reaction[].manifestation.coding.code | |||
reaction[].manifestation.coding.display | |||
reaction[].manifestation.coding.system | |||
reaction[].onset | allergy_intolerance | date_recorded | |
patient | allergy_intolerance | patient_id | patient.id |
reporter | allergy_intolerance | practitioner_id | practitioner.id |
Condition
property | table | column | foreign key |
---|---|---|---|
code.coding.code | observation | core_concept_id | |
code.coding.display | |||
code.coding.system | |||
code.text | |||
onsetDateTime | observation | clinical_effective_date | |
patient | observation | patient_id | patient.id |
asserter | observation | practitioner_id | practitioner.id |
encounter | observation | encounter_id | encounter.id |
Procedure
property | table | column | foreign key |
---|---|---|---|
code.coding.code | observation | core_concept_id | |
code.coding.display | |||
code.coding.system | |||
code.text | |||
performedDateTime | observation | clinical_effective_date | |
category | |||
patient | observation | patient_id | patient.id |
performer.actor | observation | practitioner_id | practitioner.id |
encounter | observation | encounter_id | encounter.id |
DiagnosticReport
property | table | column | foreign key |
---|---|---|---|
identifier.value | observation | id | |
code.coding.code | observation | core_concept_id | |
code.coding.display | |||
code.coding.system | |||
code.text | |||
effectiveDateTime | observation | clinical_effective_date | |
category | |||
patient | observation | patient_id | patient.id |
performer | observation | practitioner_id | practitioner.id |
There is no Organization on DiagnosticReport. Potentially it could come by populating the Encounter relationship | observation | organization_id | organization.id |
Observation
Observation resources are mapped differently depending upon whether or not the data represents a pathology, microbiology or a radiology result (see FHIR Store Mappings for details on how an Observation is used to store these different kinds of result)
In all cases the Observation will ultimately link back to a parent DiagnosticReport. In the Compass schema this resource is stored in the observation table. On this basis the DiagnosticReport and it's Observations are all stored as different rows in the same observation table. The observation.parent_observation_id relationship is key to preserving the hierarchy between DiagnosticReport and it's related Observations. Similarly where an Observation itself may have child Observations (i.e. within microbiology results) then this column is also used to model that relationship.
Regardless of the type of result there are some mappings that are common
property | table | column | foreign key |
---|---|---|---|
identifier.value | observation | id | |
code.coding.code | observation | core_concept_id | |
code.coding.display | |||
code.coding.system | |||
code.text | |||
effectiveDateTime | observation | clinical_effective_date | |
category | |||
patient | observation | patient_id | patient.id |
performer | observation | practitioner_id | practitioner.id |
There is no Organization on Observation. Potentially it could come by populating the Encounter relationship | observation | organization_id | organization.id |
Pathology result
A pathology result can take one of three forms -
Full textual report
property | table | column | foreign key |
---|---|---|---|
vaueString - is the column wide enough? Might be better to store the report outside of the Observation table and reference it | observation | result_text |
Numeric result
property | table | column | foreign key |
---|---|---|---|
vaueQuantity.value | observation | result_text | |
vaueQuantity.unit | observation | result_value | |
referenceRange.low.unit | observation | result_value_units | |
referenceRange.high.unit | |||
referenceRange.low.value | |||
referenceRange.high.value |
Textual result
property | table | column | foreign key |
---|---|---|---|
vaueString | observation | result_text |
Radiology result
property | table | column | foreign key |
---|---|---|---|
attachment.data | |||
attachment.contentType |
Microbiology result
TODO