Compass Database Mappings: Difference between revisions

From Discovery Data Service
Jump to navigation Jump to search
 
Line 536: Line 536:
|practitioner_id
|practitioner_id
|practitioner.id
|practitioner.id
|-style='background-color: #da614e;'
|There is no Organization on DiagnosticReport. Potentially it could come by populating the Encounter relationship
|observation
|organization_id
|organization.id
|}
|}


Line 599: Line 594:
|practitioner_id
|practitioner_id
|practitioner.id
|practitioner.id
|- style="background-color: #da614e;"
|-
|There is no Organization on Observation. Potentially it could come by populating the Encounter relationship
|extension = PARENT_RESOURCE
|observation
|observation
|organization_id
|parent_observation_id
|organization.id
|
|}
|}


Line 616: Line 611:
!foreign key
!foreign key
|-
|-
|text OR mediumtext
|valueString
|observation
|observation
|result_text
|result_text*
|
|
|}
|}
<nowiki>*</nowiki>if the valuestring is significantly longer than text allows, consider changing this to mediumtext. 
<u>Numeric result</u>
<u>Numeric result</u>
{| class="wikitable"
{| class="wikitable"

Latest revision as of 09:16, 27 April 2022

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

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
extension = PARENT_RESOURCE observation parent_observation_id

Pathology result

A pathology result can take one of three forms -

Full textual report

property table column foreign key
valueString observation result_text*

*if the valuestring is significantly longer than text allows, consider changing this to mediumtext.

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