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
