Compass Database Mappings

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