Compass 2.1 Schema

From Discovery Data Service
Revision as of 10:04, 22 December 2022 by JoshuA (talk | contribs) (Created page with " <h1 id="firstHeading" class="firstHeading" lang="en-GB">Remote Subscriber Database (RSD) Schema Compass 2.1</h1> <div id="bodyContent" class="mw-body-content"> <div id="...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Remote Subscriber Database (RSD) Schema Compass 2.1

From Discovery Data Service

allergy_intolerance

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the allergy' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter this allergy was record in' encounter.id
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date date DEFAULT NULL 'The date the clinical code is recorded for' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
is_review tinyint(1) NOT NULL 'Is this instance of the code a review of a previous encounter' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the allergy' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the allergy' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age the patient was at the time of this event' No Foreign Key reference
date_recorded datetime NOT NULL 'The date the allergy was recorded' No Foreign Key reference
Unique index name: `allergy_intolerance_id` Index Columns: (id)
Index name: `fk_allergy_intolerance_encounter_id` Index Columns: (encounter_id)
Index name: `fk_allergy_intolerance_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_allergy_intolerance_practitioner_id` Index Columns: (practitioner_id)
Index name: `allergy_intolerance_patient_id` Index Columns: (patient_id)
Index name: `allergy_intolerance_core_concept_id` Index Columns: (core_concept_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

appointment

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the appointment' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' organization.id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' No Foreign Key reference
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
schedule_id bigint DEFAULT NULL 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule' No Foreign Key reference
start_date date DEFAULT NULL 'The start date of the appointment' No Foreign Key reference
planned_duration int DEFAULT NULL 'The time allocated for the appointment, not necessarily the actual duration always in minutes' No Foreign Key reference
actual_duration int DEFAULT NULL 'Time between sent in and left always in minutes' No Foreign Key reference
appointment_status_concept_id int DEFAULT NULL 'The status of the appointment e.g. arrived/sent in/left/DNA' No Foreign Key reference
patient_wait int DEFAULT NULL 'How long the patient waited from being marked as arrived to being sent in' No Foreign Key reference
patient_delay int DEFAULT NULL 'How long the patient was delayed for' No Foreign Key reference
date_time_sent_in datetime DEFAULT NULL 'Date and time the patient was sent into the practitioner' No Foreign Key reference
date_time_left datetime DEFAULT NULL 'Date and time the patient left the practitioner' No Foreign Key reference
source_id varchar(36) DEFAULT NULL 'Unique reference to the source of the appointment' No Foreign Key reference
cancelled_date datetime DEFAULT NULL 'The date the appointment was cancelled' No Foreign Key reference
Unique index name: `appointment_id` Index Columns: (id)
Index name: `fk_appointment_practitioner_id` Index Columns: (practitioner_id)
Index name: `appointment_patient_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

appointment_additional

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'same as the id column on the patient table ' No Foreign Key reference
property_id int NOT NULL 'IM reference (e.g. appointment type)' No Foreign Key reference
value_id int DEFAULT NULL 'IM reference (e.g. appointment type)' No Foreign Key reference
json_value json DEFAULT NULL 'where there is no mapped value_id, just raw JSON' No Foreign Key reference
text_value varchar(255) DEFAULT NULL 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' No Foreign Key reference
Index name: `appointment_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

concept

Column Name Data Type Constraint Comment Foreign Key Reference
dbid int NOT NULL 'Unique concept int DB identifier' No Foreign Key reference
document int DEFAULT NULL 'Document this concept originated from' No Foreign Key reference
id varchar(150) CHARACTER SET 'Unique human-readable concept id' No Foreign Key reference
draft tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
name varchar(255) DEFAULT NULL 'Short name' No Foreign Key reference
description varchar(400) DEFAULT NULL 'Full name (or term for ontological concepts)' No Foreign Key reference
scheme bigint DEFAULT NULL 'The coding scheme for the code (Read, CTV3, SNOMED etc)' No Foreign Key reference
code varchar(40) CHARACTER SET 'The code (non-unique unless coupled with a scheme)' No Foreign Key reference
use_count bigint No Constraint 'Rough indicator of number of occurences of the concept' No Foreign Key reference
updated datetime NOT NULL 'The timestamp of the last update to the concept' No Foreign Key reference
Index name: `ix_scheme_code` Index Columns: (scheme,code)
Index name: `ix_code` Index Columns: (code)
Index name: `ix_dbid_code` Index Columns: (dbid,code)
PRIMARY KEY CONSTRAINT(s) (`dbid`)

concept_map

Column Name Data Type Constraint Comment Foreign Key Reference
legacy int NOT NULL 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept' No Foreign Key reference
core int NOT NULL 'the core (snomed, discovery) concept that the legacy concept maps to' No Foreign Key reference
updated datetime NOT NULL 'Timestamp the map was last updated/added' No Foreign Key reference
id int NOT NULL, No comment yet added No Foreign Key reference
deleted tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `concept_map_uq` Index Columns: (legacy,deleted,updated)
Index name: `ix_legacy_core` Index Columns: (legacy,core)
PRIMARY KEY CONSTRAINT(s) (`id`)

database_version

Column Name Data Type Constraint Comment Foreign Key Reference
version varchar(255) NOT NULL 'version of the database' No Foreign Key reference
valid_from date NOT NULL 'date that the version was updated' No Foreign Key reference
valid_to date DEFAULT NULL 'date that the version was made outdated' No Foreign Key reference
Unique index name: `valid_to_uq` Index Columns: (valid_to)
PRIMARY KEY CONSTRAINT(s) (`valid_from`)

diagnostic_order

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the diagnostic order' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter the observation was recorded at' encounter.id
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date datetime DEFAULT NULL 'The date the diagnostic order was identified by a clinician' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
result_value double DEFAULT NULL 'The value of the result of the observation' No Foreign Key reference
result_value_units varchar(50) DEFAULT NULL 'The units of the result of the observation' No Foreign Key reference
result_date date DEFAULT NULL 'The date of the result' No Foreign Key reference
result_text text No Constraint 'Any text associated with the result' No Foreign Key reference
result_concept_id int DEFAULT NULL 'Reference to the clinical coding of the result' No Foreign Key reference
is_problem tinyint(1) NOT NULL 'Whether the observation is marked as a problem' No Foreign Key reference
is_review tinyint(1) NOT NULL 'Whether the observation is a review of an existing problem' No Foreign Key reference
problem_end_date date DEFAULT NULL 'The end date of the problem' No Foreign Key reference
parent_observation_id bigint DEFAULT NULL 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the observation' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the observation' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age of the patient at the time of the observation' No Foreign Key reference
episodicity_concept_id int DEFAULT NULL 'Reference to the episodicity of the problem eg First, review, flare' No Foreign Key reference
is_primary tinyint(1) DEFAULT NULL 'Whether the diagnostic order is a primary order' No Foreign Key reference
date_recorded datetime DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `diagnostic_order_id` Index Columns: (id)
Index name: `fk_diagnostic_order_encounter_id` Index Columns: (encounter_id)
Index name: `fk_diagnostic_order_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_diagnostic_order_practitioner_id` Index Columns: (practitioner_id)
Index name: `diagnostic_order_patient_id` Index Columns: (patient_id)
Index name: `diagnostic_order_core_concept_id` Index Columns: (core_concept_id)
Index name: `diagnostic_order_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)
Index name: `diagnostic_order_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)
Index name: `diagnostic_order_non_core_concept_id` Index Columns: (non_core_concept_id)
Index name: `ix_diagnostic_order_organization_id` Index Columns: (organization_id)
Index name: `ix_diagnostic_order_clinical_effective_date` Index Columns: (clinical_effective_date)
Index name: `ix_diagnostic_order_person_id` Index Columns: (person_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

encounter

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the encounter' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
appointment_id bigint DEFAULT NULL 'Reference to the appointment this encounter took part on' No Foreign Key reference
clinical_effective_date date DEFAULT NULL 'The date the clinical code is recorded for' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Reference to the precision of the date of the encounter' No Foreign Key reference
episode_of_care_id bigint DEFAULT NULL 'Is this instance of the code a review of a previous encounter' episode_of_care.id
service_provider_organization_id bigint DEFAULT NULL 'Reference to the service provider organisation' organization.id
core_concept_id int DEFAULT NULL 'Reference to the type of encounter' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the type of encounter' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age the patient was when this encounter took place' No Foreign Key reference
type text No Constraint 'Unused' No Foreign Key reference
sub_type text No Constraint 'Unused' No Foreign Key reference
admission_method varchar(40) DEFAULT NULL 'The admission method of the encounter' No Foreign Key reference
end_date date DEFAULT NULL 'The end date of the encounter' No Foreign Key reference
institution_location_id text No Constraint 'Reference to the institution the encounter took place at' No Foreign Key reference
date_recorded datetime DEFAULT NULL 'The date the encounter was recorded' No Foreign Key reference
Unique index name: `encounter_id` Index Columns: (id)
Index name: `fk_encounter_practitioner_id` Index Columns: (practitioner_id)
Index name: `fk_encounter_episode_of_care_id` Index Columns: (episode_of_care_id)
Index name: `fk_encounter_service_provider_organization_id` Index Columns: (service_provider_organization_id)
Index name: `encounter_patient_id` Index Columns: (patient_id)
Index name: `fki_encounter_appointment_id` Index Columns: (appointment_id)
Index name: `fki_encounter_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `encounter_core_concept_id_clinical_effective_date` Index Columns: (core_concept_id,clinical_effective_date)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

encounter_additional

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'same as the id column on the encounter table' No Foreign Key reference
property_id int NOT NULL 'IM concept id reference (i.e. Admission method)' No Foreign Key reference
value_id int DEFAULT NULL 'IM concept id reference (i.e. Emergency admission)' No Foreign Key reference
json_value json DEFAULT NULL 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)' No Foreign Key reference
text_value varchar(255) DEFAULT NULL 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)' No Foreign Key reference
Index name: `encounter_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

encounter_event

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the encounter event' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The patient this event belongs to' patient.id
person_id bigint NOT NULL 'The person this event belongs to' No Foreign Key reference
encounter_id bigint NOT NULL 'Reference to the parent encounter record' No Foreign Key reference
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
appointment_id bigint DEFAULT NULL 'Reference to the appointment this encounter took part on' No Foreign Key reference
clinical_effective_date datetime DEFAULT NULL 'The date the encounter took place' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Reference to the precision of the date of the encounter' No Foreign Key reference
episode_of_care_id bigint DEFAULT NULL 'Reference to the episode of care this encounter belongs to' episode_of_care.id
service_provider_organization_id bigint DEFAULT NULL 'Reference to the service provider organisation' organization.id
core_concept_id int DEFAULT NULL 'Reference to the type of encounter' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the type of encounter' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age of the patient at the time of the encounter' No Foreign Key reference
type text No Constraint 'Unused' No Foreign Key reference
sub_type text No Constraint 'Unused' No Foreign Key reference
admission_method varchar(40) DEFAULT NULL 'The admission method of the encounter' No Foreign Key reference
end_date date DEFAULT NULL 'The end date of the encounter' No Foreign Key reference
institution_location_id bigint DEFAULT NULL 'Reference to the institution the encounter took place at' No Foreign Key reference
date_recorded datetime DEFAULT NULL 'The date the encounter was recorded' No Foreign Key reference
finished tinyint(1) DEFAULT NULL 'Whether the encounter is finished' No Foreign Key reference
Unique index name: `encounter_event_id` Index Columns: (id)
Index name: `fk_encounter_event_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_encounter_event_practitioner_id` Index Columns: (practitioner_id)
Index name: `fk_encounter_event_episode_of_care_id` Index Columns: (episode_of_care_id)
Index name: `fk_encounter_event_service_provider_organization_id` Index Columns: (service_provider_organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

episode_of_care

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the episode of care' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
registration_type_concept_id int DEFAULT NULL 'Reference to the registration type of the patient' No Foreign Key reference
registration_status_concept_id int DEFAULT NULL 'Reference to the registration status of the patient' No Foreign Key reference
date_registered date DEFAULT NULL 'The date the registration was started  for this episode of care' No Foreign Key reference
date_registered_end date DEFAULT NULL 'The date the registration was ended  for this episode of care' No Foreign Key reference
usual_gp_practitioner_id bigint DEFAULT NULL 'Reference to the usual GP for this episode of care' practitioner.id
Unique index name: `episode_of_care_id` Index Columns: (id)
Index name: `fk_episode_of_care_patient_id_organisation_id` Index Columns: (patient_id,organization_id)
Index name: `fk_episode_of_care_practitioner_id` Index Columns: (usual_gp_practitioner_id)
Index name: `episode_of_care_patient_id` Index Columns: (patient_id)
Index name: `episode_of_care_registration_type_concept_id` Index Columns: (registration_type_concept_id)
Index name: `episode_of_care_date_registered` Index Columns: (date_registered)
Index name: `episode_of_care_date_registered_end` Index Columns: (date_registered_end)
Index name: `episode_of_care_person_id` Index Columns: (person_id)
Index name: `episode_of_care_organization_id` Index Columns: (organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

event_log

Column Name Data Type Constraint Comment Foreign Key Reference
dt_change datetime(3) NOT NULL 'date time the change was made to this DB' No Foreign Key reference
change_type tinyint NOT NULL 'type of transaction 0=insert, 1=update, 2=delete' No Foreign Key reference
table_id tinyint NOT NULL 'identifier of the table changed' No Foreign Key reference
record_id bigint NOT NULL 'id of the record changed' No Foreign Key reference

flag

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the flag' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
effective_date datetime DEFAULT NULL 'The date the flag was entered onto the patients record' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
is_active tinyint(1) NOT NULL 'Whether the flag is active or not' No Foreign Key reference
flag_text text No Constraint 'This is a warning set by the publisher regarding he patient' No Foreign Key reference
Unique index name: `flag_id` Index Columns: (id)
Index name: `fk_flag_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `flag_patient_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

location

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the location' No Foreign Key reference
name varchar(255) DEFAULT NULL 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary' No Foreign Key reference
type_code varchar(50) DEFAULT NULL 'The type of location' No Foreign Key reference
type_desc varchar(255) DEFAULT NULL 'Textual description of the type of location eg GP Practice' No Foreign Key reference
postcode varchar(10) DEFAULT NULL 'The postcode of the location' No Foreign Key reference
managing_organization_id bigint DEFAULT NULL 'Reference to the managing organisation of the location' organization.id
Unique index name: `location_id` Index Columns: (id)
Index name: `fk_location_managing_organisation_id` Index Columns: (managing_organization_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

medication_order

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the medication order' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter the medication order was issued in' encounter.id
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date date DEFAULT NULL 'The date the medication order was issued' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
dose varchar(1000) DEFAULT NULL 'Textual description of the dose' No Foreign Key reference
quantity_value double DEFAULT NULL 'The value of the medication that was prescribed eg 50' No Foreign Key reference
quantity_unit varchar(255) DEFAULT NULL 'The unit of the medication that was prescribed eg tablets' No Foreign Key reference
duration_days int DEFAULT NULL 'How many days the medication is prescribed for' No Foreign Key reference
estimated_cost double DEFAULT NULL 'The estimated cost of the medication' No Foreign Key reference
medication_statement_id bigint DEFAULT NULL 'Reference to the medication statement.  A medication statement can have many medication orders' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the medication' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the medication' No Foreign Key reference
bnf_reference varchar(6) DEFAULT NULL 'A reference to the drug in the BNF dictionary' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age the patient was at the time of this event' No Foreign Key reference
issue_method text No Constraint 'The issue method of the medication eg hand written' No Foreign Key reference
date_recorded datetime DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `medication_order_id` Index Columns: (id)
Index name: `fk_medication_order_encounter_id` Index Columns: (encounter_id)
Index name: `fk_medication_order_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_medication_order_practitioner_id` Index Columns: (practitioner_id)
Index name: `medication_order_patient_id` Index Columns: (patient_id)
Index name: `medication_order_core_concept_id` Index Columns: (core_concept_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

medication_statement

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the medication' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter this medication was recorded in' encounter.id
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date date DEFAULT NULL 'The date the medication was clinical relevant' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
is_active tinyint(1) DEFAULT NULL 'Whether the medication is active or not' No Foreign Key reference
cancellation_date date DEFAULT NULL 'The date the medication was cancelled' No Foreign Key reference
dose varchar(1000) DEFAULT NULL 'Texual description of the dose of the medication' No Foreign Key reference
quantity_value double DEFAULT NULL 'The value of the medication that was prescribed eg 50' No Foreign Key reference
quantity_unit varchar(255) DEFAULT NULL 'The unit of the medication that was prescribed eg tablets' No Foreign Key reference
authorisation_type_concept_id int DEFAULT NULL 'Reference to the authorisation type' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the medication' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the medication' No Foreign Key reference
bnf_reference varchar(6) DEFAULT NULL 'A reference to the drug in the BNF dictionary' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age the patient was at the time of this event' No Foreign Key reference
issue_method text No Constraint 'The issue method of the medication eg hand written' No Foreign Key reference
date_recorded datetime DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `medication_statement_id` Index Columns: (id)
Index name: `fk_medication_statement_encounter_id` Index Columns: (encounter_id)
Index name: `fk_medication_statement_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_medication_statement_practitioner_id` Index Columns: (practitioner_id)
Index name: `medication_statement_patient_id` Index Columns: (patient_id)
Index name: `medication_statement_dmd_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

observation

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the observation' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter the observation was recorded at' encounter.id
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date date DEFAULT NULL 'The date the observation was identified by a clinician' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
result_value double DEFAULT NULL 'The value of the result of the observation' No Foreign Key reference
result_value_units varchar(50) DEFAULT NULL 'The units of the result of the observation' No Foreign Key reference
result_date date DEFAULT NULL 'The date of the result' No Foreign Key reference
result_text text No Constraint 'Any text associated with the result' No Foreign Key reference
result_concept_id int DEFAULT NULL 'Reference to the clinical coding of the result' No Foreign Key reference
is_problem tinyint(1) NOT NULL 'Whether the observation is marked as a problem' No Foreign Key reference
is_review tinyint(1) NOT NULL 'Whether the observation is a review of an existing problem' No Foreign Key reference
problem_end_date date DEFAULT NULL 'The end date of the problem' No Foreign Key reference
parent_observation_id bigint DEFAULT NULL 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the observation' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the observation' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age of the patient at the time of the observation' No Foreign Key reference
episodicity_concept_id int DEFAULT NULL 'Reference to the episodicity of the problem eg First, review, flare' No Foreign Key reference
is_primary tinyint(1) DEFAULT NULL 'Whether the observation is a primary observation' No Foreign Key reference
date_recorded datetime DEFAULT NULL 'The date the observation was recorded in the system' No Foreign Key reference
Unique index name: `observation_id` Index Columns: (id)
Index name: `fk_observation_encounter_id` Index Columns: (encounter_id)
Index name: `fk_observation_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_observation_practitioner_id` Index Columns: (practitioner_id)
Index name: `observation_patient_id` Index Columns: (patient_id)
Index name: `observation_core_concept_id` Index Columns: (core_concept_id)
Index name: `observation_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)
Index name: `observation_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)
Index name: `observation_non_core_concept_id` Index Columns: (non_core_concept_id)
Index name: `ix_observation_organization_id` Index Columns: (organization_id)
Index name: `ix_observation_clinical_effective_date` Index Columns: (clinical_effective_date)
Index name: `ix_observation_person_id` Index Columns: (person_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

observation_additional

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'same as the id column on the observation table' No Foreign Key reference
property_id int NOT NULL 'IM reference (i.e. significance)' No Foreign Key reference
value_id int DEFAULT NULL 'IM reference (i.e. minor, significant)' No Foreign Key reference
json_value json DEFAULT NULL 'the JSON data itself ' No Foreign Key reference
text_value varchar(255) DEFAULT NULL 'where there is no mapped value_id or raw JSON, just a basic text value' No Foreign Key reference
Index name: `observation_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

organization

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the organisation' No Foreign Key reference
ods_code varchar(50) DEFAULT NULL 'ODS Code of the organisation' No Foreign Key reference
name varchar(255) DEFAULT NULL 'Name of the organisation' No Foreign Key reference
type_code varchar(50) DEFAULT NULL 'The type of organisation' No Foreign Key reference
type_desc varchar(255) DEFAULT NULL 'Textual description of the type of organisation eg GP Practice' No Foreign Key reference
postcode varchar(10) DEFAULT NULL 'The postcode of the organisation' No Foreign Key reference
parent_organization_id bigint DEFAULT NULL 'The id of the parent organisation' No Foreign Key reference
Unique index name: `organization_id` Index Columns: (id)
Index name: `fki_organization_parent_organization_id` Index Columns: (parent_organization_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

organization_metadata

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'organisation ID, corresponds to same ID in the organizaton table' No Foreign Key reference
publishing_software varchar(50) NOT NULL 'software name of publishing system, i.e. SystmOne' No Foreign Key reference
last_data_to_dds datetime DEFAULT NULL 'date time data was last sent to DDS' No Foreign Key reference
last_data_cutoff datetime DEFAULT NULL 'cutoff date time of the last extract from the publishing system' No Foreign Key reference
PRIMARY KEY CONSTRAINT(s) (`id`,`publishing_software`)

patient

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the patient' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' organization.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
title varchar(255) DEFAULT NULL 'The title of the patient' No Foreign Key reference
first_names varchar(255) DEFAULT NULL 'The first names of the patient' No Foreign Key reference
last_name varchar(255) DEFAULT NULL 'The last name of the patient' No Foreign Key reference
gender_concept_id int DEFAULT NULL 'Reference to the gender of the patient' No Foreign Key reference
nhs_number varchar(255) DEFAULT NULL 'The NHS number of the patient' No Foreign Key reference
date_of_birth date DEFAULT NULL 'The date of birth of the patient' No Foreign Key reference
date_of_death date DEFAULT NULL 'The date of death of the patient' No Foreign Key reference
current_address_id bigint DEFAULT NULL 'Reference to the current address of the patient' No Foreign Key reference
ethnic_code_concept_id int DEFAULT NULL 'Reference to the ethnicity of the patient' No Foreign Key reference
registered_practice_organization_id bigint DEFAULT NULL 'Reference to the organisation the patient is registered at' No Foreign Key reference
birth_year smallint DEFAULT NULL, No comment yet added No Foreign Key reference
birth_month tinyint DEFAULT NULL, No comment yet added No Foreign Key reference
birth_week tinyint DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `patient_id` Index Columns: (id)
Index name: `patient_person_id` Index Columns: (person_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

patient_additional

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'same as the id column on the patient table ' No Foreign Key reference
property_id int NOT NULL 'IM reference (e.g. Cause of death)' No Foreign Key reference
value_id int DEFAULT NULL 'IM reference (e.g. COVID)' No Foreign Key reference
json_value json DEFAULT NULL 'where there is no mapped value_id, just raw JSON' No Foreign Key reference
text_value varchar(255) DEFAULT NULL 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' No Foreign Key reference
Index name: `ix_patient_additional_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

patient_address

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the address' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
address_line_1 varchar(255) DEFAULT NULL 'The first line of the address' No Foreign Key reference
address_line_2 varchar(255) DEFAULT NULL 'The second line of the address' No Foreign Key reference
address_line_3 varchar(255) DEFAULT NULL 'The third line of the address' No Foreign Key reference
address_line_4 varchar(255) DEFAULT NULL 'The fourth line of the address' No Foreign Key reference
city varchar(255) DEFAULT NULL 'The city' No Foreign Key reference
postcode varchar(255) DEFAULT NULL 'The postcode' No Foreign Key reference
use_concept_id int NOT NULL 'use of address (e.g. home, temporary)' No Foreign Key reference
start_date date NOT NULL 'The start date of this address being relevant' No Foreign Key reference
end_date date DEFAULT NULL 'The end date of this address being relevant' No Foreign Key reference
lsoa_2001_code varchar(9) DEFAULT NULL 'A reference to the LSOA_2001 code' No Foreign Key reference
lsoa_2011_code varchar(9) DEFAULT NULL 'A reference to the LSOA_2011 code' No Foreign Key reference
msoa_2001_code varchar(9) DEFAULT NULL 'A reference to the MSOA_2001 code' No Foreign Key reference
msoa_2011_code varchar(9) DEFAULT NULL 'A reference to the MSOA_2011 code' No Foreign Key reference
ward_code varchar(9) DEFAULT NULL 'The ward the address belongs to' No Foreign Key reference
local_authority_code varchar(9) DEFAULT NULL 'The local authority the address belongs to' No Foreign Key reference
townsend_deprivation_index double DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `ux_patient_address_id` Index Columns: (id)
Index name: `fk_patient_address_patient_id_organization_id` Index Columns: (patient_id,organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

patient_address_match

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL No comment yet added No Foreign Key reference
patient_address_id bigint NOT NULL, No comment yet added patient_address.id
uprn varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
uprn_ralf00 varchar(255) DEFAULT NULL, No comment yet added No Foreign Key reference
status tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
uprn_property_classification varchar(45) CHARACTER SET No comment yet added No Foreign Key reference
latitude double DEFAULT NULL, No comment yet added No Foreign Key reference
longitude double DEFAULT NULL, No comment yet added No Foreign Key reference
uprn_xcoordinate double DEFAULT NULL, No comment yet added No Foreign Key reference
uprn_ycoordinate double DEFAULT NULL, No comment yet added No Foreign Key reference
qualifier varchar(50) CHARACTER SET No comment yet added No Foreign Key reference
match_rule varchar(4096) CHARACTER SET No comment yet added No Foreign Key reference
match_date datetime DEFAULT NULL, No comment yet added No Foreign Key reference
abp_address_number varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
abp_address_street varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
abp_address_locality varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
abp_address_town varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
abp_address_postcode varchar(10) CHARACTER SET No comment yet added No Foreign Key reference
abp_address_organization varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
match_pattern_postcode varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
match_pattern_street varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
match_pattern_number varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
match_pattern_building varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
match_pattern_flat varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
algorithm_version varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
epoch varchar(255) CHARACTER SET No comment yet added No Foreign Key reference
Index name: `patient_address_uprn_index` Index Columns: (uprn)
Index name: `patient_address_patient_address_id` Index Columns: (id,uprn)
Index name: `patient_address_uprn_patient_address_id_fk` Index Columns: (patient_address_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

patient_address_ralf

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL, No comment yet added No Foreign Key reference
organization_id bigint NOT NULL, No comment yet added organization.id
patient_id bigint NOT NULL, No comment yet added patient.id
person_id bigint NOT NULL, No comment yet added person.id
patient_address_id bigint NOT NULL, No comment yet added patient_address.id
patient_address_match_uprn_ralf00 varchar(255) NOT NULL, No comment yet added No Foreign Key reference
salt_name varchar(50) NOT NULL, No comment yet added No Foreign Key reference
ralf varchar(255) NOT NULL, No comment yet added No Foreign Key reference
Index name: `fk_patient_address_ralf_organization_id` Index Columns: (organization_id)
Index name: `fk_patient_address_ralf_person_id` Index Columns: (person_id)
Index name: `ux_patient_address_ralf_id` Index Columns: (id)
Index name: `patient_address_ralf_patient_id` Index Columns: (patient_id)
Index name: `patient_address_ralf_patient_address_id` Index Columns: (patient_address_id)
Index name: `patient_address_ralf_patient_address_match_uprn_ralf_00` Index Columns: (patient_address_match_uprn_ralf00)
PRIMARY KEY CONSTRAINT(s) (`id`,`patient_address_id`,`patient_address_match_uprn_ralf00`)

patient_contact

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the patient contact' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
use_concept_id int DEFAULT NULL 'use of contact (e.g. mobile, home,work' No Foreign Key reference
type_concept_id int DEFAULT NULL 'type of contact (e.g. phone, email)' No Foreign Key reference
start_date date DEFAULT NULL 'The start date of the contact being valid' No Foreign Key reference
end_date date DEFAULT NULL 'The end date of the contact being valid' No Foreign Key reference
value varchar(255) DEFAULT NULL 'The value of the contact information eg phone number, email address' No Foreign Key reference
Unique index name: `ux_patient_contact_id` Index Columns: (id)
Index name: `fk_patient_contact_patient_id_organisation_id` Index Columns: (patient_id,organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

patient_pseudo_id

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the patient pseudo id' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'Reference to the patient this registration status history belongs to' patient.id
person_id bigint NOT NULL 'Reference to the person this registration status history belongs to' No Foreign Key reference
salt_name varchar(50) NOT NULL 'The name of the salt used to create the pseudo id' No Foreign Key reference
Skid varchar(255) NOT NULL '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)' No Foreign Key reference
is_nhs_number_valid tinyint(1) NOT NULL 'Whether the nhs number is valid' No Foreign Key reference
is_nhs_number_verified_by_publisher tinyint(1) NOT NULL 'Whether the nhs number has been verified by the publisher' No Foreign Key reference
Unique index name: `ux_patient_pseudo_id` Index Columns: (id)
Index name: `fk_patient_pseudo_id` Index Columns: (patient_id,organization_id)
Index name: `patient_pseudo_id_patient` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

patient_uprn

Column Name Data Type Constraint Comment Foreign Key Reference
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
uprn bigint DEFAULT NULL, No comment yet added No Foreign Key reference
qualifier varchar(50) DEFAULT NULL, No comment yet added No Foreign Key reference
algorithm varchar(255) DEFAULT NULL, No comment yet added No Foreign Key reference
match varchar(255) DEFAULT NULL, No comment yet added No Foreign Key reference
no_address tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
invalid_address tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
missing_postcode tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
invalid_postcode tinyint(1) DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `patient_uprn_id` Index Columns: (patient_id)
Index name: `fk_patient_uprn_patient_id_organisation_id` Index Columns: (patient_id,organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`)

person

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the person' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' No Foreign Key reference
title varchar(255) DEFAULT NULL 'The title of the person' No Foreign Key reference
first_names varchar(255) DEFAULT NULL 'The first names of the person' No Foreign Key reference
last_name varchar(255) DEFAULT NULL 'The last name of the person' No Foreign Key reference
gender_concept_id int DEFAULT NULL 'Reference to the gender of the person' No Foreign Key reference
nhs_number varchar(255) DEFAULT NULL 'The NHS number of the person' No Foreign Key reference
date_of_birth date DEFAULT NULL 'The date of birth of the person' No Foreign Key reference
date_of_death date DEFAULT NULL 'The date of death of the person' No Foreign Key reference
current_address_id bigint NOT NULL 'Reference to the current address of the person' No Foreign Key reference
ethnic_code_concept_id int DEFAULT NULL 'Reference to the ethnicity of the person' No Foreign Key reference
registered_practice_organization_id bigint DEFAULT NULL 'Reference to the organisation the person is registered at' No Foreign Key reference
birth_year smallint DEFAULT NULL, No comment yet added No Foreign Key reference
birth_month tinyint DEFAULT NULL, No comment yet added No Foreign Key reference
birth_week tinyint DEFAULT NULL, No comment yet added No Foreign Key reference
Unique index name: `person_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`id`)

practitioner

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the practitioner' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' organization.id
name varchar(1024) DEFAULT NULL 'Name of the practitioner' No Foreign Key reference
role_code varchar(50) DEFAULT NULL 'The code representing the role of the practitioner' No Foreign Key reference
role_desc varchar(255) DEFAULT NULL 'Textual description of the role of the practitioner eg General Medical Practitioner' No Foreign Key reference
gmc_code varchar(50) DEFAULT NULL 'The GMC code of the practitioner' No Foreign Key reference
Unique index name: `practitioner_id` Index Columns: (id)
Index name: `fk_practitioner_organisation_id` Index Columns: (organization_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

procedure_request

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the procedure' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter the procedure was administered at' No Foreign Key reference
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date datetime DEFAULT NULL 'The date the procedure was administered by a clinician' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
status_concept_id int DEFAULT NULL 'Reference to the status of the procedure' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the procedure' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the procedure' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age of the patient at the time of the procedure' No Foreign Key reference
date_recorded datetime DEFAULT NULL 'The date the procedure was recorded in the source system' No Foreign Key reference
Unique index name: `procedure_request_id` Index Columns: (id)
Index name: `fk_procedure_request_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_procedure_request_practitioner_id` Index Columns: (practitioner_id)
Index name: `procedure_request_patient_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

referral_request

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the referral' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' patient.organization_id
patient_id bigint NOT NULL 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' patient.id
person_id bigint NOT NULL 'Unique individual across all organisations' No Foreign Key reference
encounter_id bigint DEFAULT NULL 'Reference to the encounter the referral was made in' No Foreign Key reference
practitioner_id bigint DEFAULT NULL 'The clinician the activity is recorded against' practitioner.id
clinical_effective_date datetime DEFAULT NULL 'The date the referral was made' No Foreign Key reference
date_precision_concept_id int DEFAULT NULL 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' No Foreign Key reference
requester_organization_id bigint DEFAULT NULL 'Reference to the organisation that made the refereral request' organization.id
recipient_organization_id bigint DEFAULT NULL 'Reference to the organization receiving the referral' organization.id
referral_request_priority_concept_id int DEFAULT NULL 'Reference to the priority of the referral' No Foreign Key reference
referral_request_type_concept_id int DEFAULT NULL 'Reference to the type of referral request' No Foreign Key reference
mode varchar(50) DEFAULT NULL 'The mode of the referral' No Foreign Key reference
outgoing_referral tinyint(1) DEFAULT NULL 'Whether this is an outgoing referral' No Foreign Key reference
is_review tinyint(1) NOT NULL 'Whether this referral is a review' No Foreign Key reference
core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the referral' No Foreign Key reference
non_core_concept_id int DEFAULT NULL 'Reference to the clinical coding of the referral' No Foreign Key reference
age_at_event decimal(5,2) DEFAULT NULL 'The age of the patient at the time of the referral' No Foreign Key reference
date_recorded datetime DEFAULT NULL 'The date the referral request was added to the source system' No Foreign Key reference
Unique index name: `referral_request_id` Index Columns: (id)
Index name: `fk_referral_request_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_referral_request_practitioner_id` Index Columns: (practitioner_id)
Index name: `fk_referral_request_recipient_organization_id` Index Columns: (recipient_organization_id)
Index name: `fk_referral_request_requester_organization_id` Index Columns: (requester_organization_id)
Index name: `referral_request_patient_id` Index Columns: (patient_id)
Index name: `referral_request_core_concept_id` Index Columns: (core_concept_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

registration_status_history

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the registration status history' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' No Foreign Key reference
patient_id bigint NOT NULL 'Reference to the patient this registration status history belongs to' No Foreign Key reference
person_id bigint NOT NULL 'Reference to the person this registration status history belongs to' No Foreign Key reference
episode_of_care_id bigint DEFAULT NULL 'Reference to the episode of care this status history belongs to' episode_of_care.id
registration_status_concept_id int DEFAULT NULL 'Reference to the registration status' No Foreign Key reference
start_date datetime DEFAULT NULL 'The start date for the period this registration status history was valid' No Foreign Key reference
end_date datetime DEFAULT NULL 'The end date for the period this registration status history was valid' No Foreign Key reference
Unique index name: `ux_registration_status_history_id` Index Columns: (id)
Index name: `fk_registration_status_history_episode_id` Index Columns: (episode_of_care_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

schedule

Column Name Data Type Constraint Comment Foreign Key Reference
id bigint NOT NULL 'Unique Id of the schedule' No Foreign Key reference
organization_id bigint NOT NULL 'Owning organisation (i.e. publisher)' organization.id
practitioner_id bigint DEFAULT NULL 'Reference to the practitioner who owns the schedule' No Foreign Key reference
start_date datetime DEFAULT NULL 'The start date of the schedule' No Foreign Key reference
type varchar(255) DEFAULT NULL 'The type of schedule eg Timed Appointments' No Foreign Key reference
location varchar(255) DEFAULT NULL 'Textual description of the location the schedule was held at' No Foreign Key reference
name varchar(150) DEFAULT NULL 'The name of the schedule' No Foreign Key reference
Unique index name: `schedule_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`)

DATABASE UPDATE INFO

New Version Date Updated
V2.1 2022-12-15T17:05:49.557