Remote Subscriber Database (RSD) Schema (Compass 2)
DRAFT
Table: organization
PRIMARY KEY (pk_organization_id PRIMARY KEY (id))
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
ods_code | varchar(50) | DEFAULT NULL | ||
Name | varchar(255) | DEFAULT NULL | ||
type_code | varchar(50) | DEFAULT NULL | ||
type_desc | varchar(255) | DEFAULT NULL | ||
Postcode | varchar(10) | DEFAULT NULL | ||
parent_organization_id | bigint | DEFAULT NULL | ||
PRIMARY KEY pk_organization_id PRIMARY KEY (id) |
Table: location
PRIMARY KEY (pk_location_id PRIMARY KEY id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
name | varchar(255) | DEFAULT NULL | The name of a location set by the publisher. E.g. ward, clinic, domiciliary | |
type_code, | varchar(50) | DEFAULT NULL | ||
type_desc | varchar(255) | DEFAULT NULL | ||
postcode | varchar10) | DEFAULT NULL | ||
managing_organization_id | bigint | DEFAULT NULL | ||
CONSTRAINT pk_location_id PRIMARY KEY (id) | ||||
CONSTRAINT fk_location_organisation_id FOREIGN KEY (managing_organization_id) | ||||
REFERENCES organization (id) | ||||
MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: practitioner
PRIMARY KEY (id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organisation.id |
name | varchar(1024) | DEFAULT NULL | ||
role_code | varchar(50) | DEFAULT NULL | ||
role_desc | varchar(255) | DEFAULT NULL | ||
gmc_code | varchar(50) | DEFAULT NULL | ||
CONSTRAINT pk_practitioner_id PRIMARY KEY (id), | ||||
CONSTRAINT fk_practitioner_organisation_id FOREIGN KEY (organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: schedule
PRIMARY KEY (organization_id, id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
practitioner_id | bigint | DEFAULT NULL | practitioner.id | |
start_date | date | DEFAULT NULL | ||
type | varchar(255) | DEFAULT NULL | ||
Location | varchar(255) | DEFAULT NULL | location.id | |
Name | varchar(150) | DEFAULT NULL | ||
CONSTRAINT pk_schedule_id PRIMARY KEY (organization_id, id), | ||||
CONSTRAINT fk_schedule_organization_id FOREIGN KEY (organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: person
PRIMARY KEY (id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | ||
title | varchar(50) | DEFAULT NULL | ||
first_names | varchar(255) | DEFAULT NULL | ||
last_name | varchar(255) | DEFAULT NULL | ||
gender_concept_id | int | DEFAULT NULL | ||
nhs_number | varchar(255) | DEFAULT NULL | ||
date_of_birth date, | date | DEFAULT NULL | ||
date_of_death date, | date | DEFAULT NULL | ||
current_address_id, | bigint | NOT NULL | ||
ethnic_code_concept_id | int | DEFAULT NULL | ||
registered_practice_organization_id | bigint | DEFAULT NULL | ||
CONSTRAINT pk_person_id PRIMARY KEY (id) |
Table: patient
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
person_id | bigint | NOT NULL | Unique individual across all organisations | person.id |
title | varchar(50) | |||
first_names | varchar(255) | |||
last_name | varchar(255) | |||
gender_concept_id | int | |||
nhs_number | varchar(255) | |||
date_of_birth | date | |||
date_of_death | date | |||
current_address_id | bigint | |||
ethnic_code_concept_id | int | |||
registered_practice_organization_id | bigint | |||
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_patient_organization_id FOREIGN KEY (organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE |
Table: episode_of_care
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
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 | patient.id |
person_id | bigint | NOT NULL | Unique individual across all organisations | person.id |
registration_type_concept_id | int | DEFAULT NULL | registration.type.id | |
registration_status_concept_id | int | DEFAULT NULL | ||
date_registered date | date | DEFAULT NULL | ||
date_registered_end date | date | DEFAULT NULL | ||
usual_gp_practitioner_id | bigint | DEFAULT NULL | Practitioner.id | |
CONSTRAINT pk_episode_of_care_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_episode_of_care_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_episode_of_care_practitioner_id FOREIGN KEY (usual_gp_practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: appointment
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | varchar(36) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
patient_id | varchar(36) | 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 | person.id |
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 | schedule.id |
start_date | date | DEFAULT NULL | ||
planned_duration | int | DEFAULT NULL | The time allocated for the appointment, not necessarily the actual duration always in minutes | |
actual_duration | int | DEFAULT NULL | Time between sent in and left always in minutes | |
appointment_status_concept_id | int | DEFAULT NULL | The status of the appointment e.g. arrived/sent in/left/DNA | |
patient_wait | int | DEFAULT NULL | How long the patient waited from being marked as arrived to being sent in | |
patient_delay | int | DEFAULT NULL | ||
date_time_sent_in | datetime | DEFAULT NULL | Date and time the patient was sent into the practitioner | |
date_time_left | datetime | DEFAULT NULL | Date and time the patient left the practitioner | |
source_id | varchar(36) | DEFAULT NULL | ||
cancelled_date | datetime | DEFAULT NULL | ||
CONSTRAINT pk_appointment_id PRIMARY KEY (organization_id,person_id,id), | ||||
CONSTRAINT fk_appointment_organization_id FOREIGN KEY (organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_appointment_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: encounter
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | |
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 | |
person_id | bigint | NOT NULL | Unique individual across all organisations | |
practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | |
appointment_id | bigint | DEFAULT NULL | ||
clinical_effective_date | date | DEFAULT NULL | The date the clinical code is recorded for | |
|
int | DEFAULT NULL | ||
episode_of_care_id | bigint | DEFAULT NULL | Is this instance of the code a review of a previous encounter | |
service_provider_organization_id | bigint | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
Type | text | DEFAULT NULL | ||
sub_type | text | DEFAULT NULL | ||
admission_method | varchar(40) | DEFAULT NULL | ||
end_date | date | DEFAULT NULL | ||
institution_location_id | text | DEFAULT NULL | ||
date_recorded | datetime | DEFAULT NULL | ||
service_provider_organization_id | bigint(20) | DEFAULT NULL | ||
| ||||
CONSTRAINT fk_encounter_appointment_id FOREIGN KEY (appointment_id) | ||||
REFERENCES appointment (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION,*/ | ||||
CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_episode_of_care_id FOREIGN KEY (episode_of_care_id) | ||||
REFERENCES episode_of_care (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_service_provider_organization_id FOREIGN KEY (service_provider_organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: encounter_event
PRIMARY KEY (organization_id, person_id, id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | ||
patient_id | bigint | NOT NULL | ||
person_id | bigint | NOT NULL | ||
encounter_id | bigint | NOT NULL | parent encounter record | |
practitioner_id | bigint | DEFAULT NULL | ||
appointment_id | bigint | DEFAULT NULL | ||
clinical_effective_date | datetime | DEFAULT NULL | ||
date_precision_concept_id | int | DEFAULT NULL | ||
episode_of_care_id | bigint | DEFAULT NULL | ||
service_provider_organization_id | bigint | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
type | text | DEFAULT NULL | ||
sub_type | text | DEFAULT NULL | ||
admission_method | varchar(40) | DEFAULT NULL | ||
end_date | date | DEFAULT NULL | ||
institution_location_id | bigint | DEFAULT NULL | ||
date_recorded | datetime | DEFAULT NULL | ||
finished | boolean | DEFAULT NULL | ||
CONSTRAINT pk_encounter_event_id PRIMARY KEY (organization_id, person_id, id), | ||||
CONSTRAINT fk_encounter_event_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_event_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_event_episode_of_care_id FOREIGN KEY (episode_of_care_id) | ||||
REFERENCES episode_of_care (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_encounter_event_service_provider_organization_id FOREIGN KEY (service_provider_organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: encounter_additional
PRIMARY KEY (id, property_id, value_id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | same as the id column on the encounter table | |
property_id | bigint | NOT NULL | IM reference (i.e. Admission method) | |
value_id | bigint | NOT NULL | IM reference (i.e. Emergency admission) | |
CONSTRAINT pk_encounter_additional_id PRIMARY KEY (id, property_id, value_id) |
Table: allergy_intolerance
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
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 | patient.id |
person_id | bigint | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint | DEFAULT NULL | 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 | |
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) | |
is_review | boolean | NOT NULL | Is this instance of the code a review of a previous encounter | |
core_concept_id | Int | DEFAULT NULL | ||
non_core_concept_id | Int | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
date_recorded | datetime | NOT NULL | Is this instance of the code a review of a previous encounter | |
CONSTRAINT pk_allergy_intolerance_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_allergy_intolerance_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_allergy_intolerance_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_allergy_intolerance_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: medication_statement
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
patient_id | bigint(20) | 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(20) | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint(20) | DEFAULT NULL | encounter.id | |
practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
clinical_effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) | |
is_active | tinyint(4) | DEFAULT NULL | ||
cancellation_date | date | DEFAULT NULL | ||
dose | varchar(1000) | DEFAULT NULL | ||
quantity_value | double | DEFAULT NULL | ||
quantity_unit | varchar(255) | DEFAULT NULL | ||
authorisation_type_concept_id | int | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
bnf_reference | varchar(6) | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
Issue_method | text | DEFAULT NULL | ||
CONSTRAINT pk_medication_statement_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_medication_statement_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_medication_statement_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_medication_statement_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: medication_order
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
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 | patient.id |
person_id | bigint | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint | DEFAULT NULL | encounter.id | |
practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
clinical_effective_date | date | DEFAULT NULL | ||
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) | |
dose | varchar(1000) | DEFAULT NULL | ||
quantity_value | real | DEFAULT NULL | ||
quantity_unit | varchar(255) | DEFAULT NULL | ||
duration_days | int | DEFAULT NULL | ||
estimated_cost | real | DEFAULT NULL | ||
medication_statement_id | bigint | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
bnf_reference | varchar(6) | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
issue_method | text | DEFAULT NULL | ||
CONSTRAINT pk_medication_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_medication_order_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_medication_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_medication_order_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: flag
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | |
patient_id | bigint(20) | 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(20) | NOT NULL | Unique individual across all organisations | person.id |
effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) | |
is_active | tinyint(1) | NOT NULL | ||
flag_text | text | This is a warning set by the publisher regarding he patient | ||
CONSTRAINT pk_flag_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: observation
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
patient_id | bigint(20) | 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(20) | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint(20) | DEFAULT NULL | encounter.id | |
practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
clinical_effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) | |
result_value | double | DEFAULT NULL | ||
result_value_units | varchar(50) | DEFAULT NULL | ||
result_date | date | DEFAULT NULL | ||
result_text | text | |||
result_concept_id | bigint(20) | DEFAULT NULL | ||
is_problem | tinyint(1) | NOT NULL | ||
is_review | tinyint(1) | NOT NULL | ||
problem_end_date | date | DEFAULT NULL | ||
parent_observation_id | bigint(20) | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal (5,2) | DEFAULT NULL | ||
episodicity_concept_id | int | DEFAULT NULL | ||
is_primary | boolean | DEFAULT NULL | ||
date_recorded | datetime | DEFAULT NULL | ||
CONSTRAINT pk_observation_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_observation_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_observation_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_observation_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: diagnostic_order
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | ||
patient_id | bigint | NOT NULL | ||
person_id | bigint | NOT NULL | ||
encounter_id | bigint | DEFAULT NULL | ||
practitioner_id | bigint | DEFAULT NULL | ||
clinical_effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | int | DEFAULT NULL | ||
result_value | real | DEFAULT NULL | ||
result_value_units | varchar(50) | DEFAULT NULL | ||
result_date | date | DEFAULT NULL | ||
result_text | text | DEFAULT NULL | ||
result_concept_id | int | DEFAULT NULL | ||
is_problem | boolean | NOT NULL | ||
is_review | boolean | NOT NULL | ||
problem_end_date | date | DEFAULT NULL | ||
parent_observation_id | bigint | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal (5,2) | DEFAULT NULL | ||
episodicity_concept_id | int | DEFAULT NULL | ||
is_primary | boolean | DEFAULT NULL | ||
CONSTRAINT pk_diagnostic_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
CONSTRAINT fk_diagnostic_order_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_diagnostic_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_diagnostic_order_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: procedure_request
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
patient_id | bigint(20) | 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(20) | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint(20) | DEFAULT NULL | encounter.id | |
practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
clinical_effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | int | DEFAULT NULL | ||
status_concept_id | int | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
date_recorded | datetime | DEFAULT NULL | ||
CONSTRAINT pk_procedure_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
/*got an Emis procedure request referring to a confidential encounter, so can't enforce this | ||||
CONSTRAINT fk_procedure_request_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION,*/ | ||||
CONSTRAINT fk_procedure_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_procedure_request_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: referral_request
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
patient_id | bigint(20) | 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(20) | NOT NULL | Unique individual across all organisations | person.id |
encounter_id | bigint(20) | DEFAULT NULL | encounter.id | |
practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
clinical_effective_date | date | DEFAULT NULL | ||
date_precision_concept_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) | |
requester_organization_id | bigint(20) | DEFAULT NULL | ||
recipient_organization_id | bigint(20) | DEFAULT NULL | ||
referral_request_priority_concept_id | smallint(6) | DEFAULT NULL | referral.request.priority | |
referral_request_type_concept_id | int | DEFAULT NULL | referral.request.type | |
Mode | varchar(50) | DEFAULT NULL | ||
outgoing_referral | boolean | DEFAULT NULL | ||
is_review | boolean | DEFAULT NULL | ||
core_concept_id | int | DEFAULT NULL | ||
non_core_concept_id | int | DEFAULT NULL | ||
age_at_event | decimal(5,2) | DEFAULT NULL | ||
date_recorded | datetime | DEFAULT NULL | ||
CONSTRAINT pk_referral_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`), | ||||
/*got an Emis referral request referring to a confidential encounter, so can't enforce this | ||||
CONSTRAINT fk_referral_request_encounter_id FOREIGN KEY (encounter_id) | ||||
REFERENCES encounter (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION,*/ | ||||
CONSTRAINT fk_referral_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_referral_request_practitioner_id FOREIGN KEY (practitioner_id) | ||||
REFERENCES practitioner (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_referral_request_recipient_organization_id FOREIGN KEY (recipient_organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION, | ||||
CONSTRAINT fk_referral_request_requester_organization_id FOREIGN KEY (requester_organization_id) | ||||
REFERENCES organization (id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: pseudo_id
PRIMARY KEY (patient_id, salt_key_name)
Column name | Data type | Constraint | Comments | References |
Id | bigint | NOT NULL | ||
patient_id | bigint | NOT NULL | ||
salt_key_name | varchar(50) | NOT NULL | ||
pseudo_id | varchar(255) | DEFAULT NULL | ||
CONSTRAINT pk_pseudo_id PRIMARY KEY (patient_id, salt_key_name) |
Table: patient_uprn
PRIMARY KEY (organization_id,person_id,id)
Column name | Data type | Constraint | Comments | References |
patient_id | bigint(20) | 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(20) | NOT NULL | Owning organisation (i.e. publisher) | organisation.id |
person_id | bigint(20) | NOT NULL | Unique individual across all organisations | person.id |
Uprn | bigint | DEFAULT NULL | ||
qualifier | varchar(50) | DEFAULT NULL | ||
algorithm | varchar(255) | DEFAULT NULL | ||
match | varchar(255) | DEFAULT NULL | ||
no_address | tinyint(1) | DEFAULT NULL | ||
invalid_address | tinyint(1) | DEFAULT NULL | ||
missing_postcode | tinyint(1) | DEFAULT NULL | ||
invalid_postcode | tinyint(1) | DEFAULT NULL | ||
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`patient_id`), | ||||
CONSTRAINT fk_patient_uprn_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) MATCH SIMPLE | ||||
ON UPDATE NO ACTION ON DELETE NO ACTION |
Table: patient_contact
PRIMARY KEY (organization_id,id,patient_id,person_id)
Column name | Data type | Constraint | Comments | References |
id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | |
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 | DEFAULT NULL | Unique individual across all organisations | person.id |
use_concept_id | int | DEFAULT NULL | use of contact (e.g. mobile, home,work | |
type_concept_id | int | DEFAULT NULL | type of contact (e.g. phone, email) | |
start_date date | date | DEFAULT NULL | ||
end_date date | date | DEFAULT NULL | ||
value | varchar(255) | DEFAULT NULL | ||
CONSTRAINT pk_organization_id_id_patient_id_person_id PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`), | ||||
CONSTRAINT fk_patient_contact_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) | ||||
COMMENT 'stores contact details (e.g. phone) for patients' |
Table: patient_address
PRIMARY KEY (organization_id,id,patient_id,person_id)
Column name | Data type | Constraint | Comments | References |
Id | bigint | NOT NULL | ||
organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organisation.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 | DEFAULT NULL | Unique individual across all organisations | person.id |
address_line_1 | varchar(255) | DEFAULT NULL | ||
address_line_2 | varchar(255) | DEFAULT NULL | ||
address_line_3 | varchar(255) | DEFAULT NULL | ||
address_line_4 | varchar(255) | DEFAULT NULL | ||
city | varchar(255) | DEFAULT NULL | ||
postcode | varchar(255) | DEFAULT NULL | ||
use_concept_id | int | NOT NULL | use of address (e.g. home, temporary) | |
start_date | Date | NOT NULL | ||
end_date | Date | DEFAULT NULL | ||
lsoa_2001_code | varchar(9) | DEFAULT NULL | ||
lsoa_2011_code | varchar(9) | DEFAULT NULL | ||
msoa_2001_code | varchar(9) | DEFAULT NULL | ||
msoa_2011_code | varchar(9) | DEFAULT NULL | ||
ward_code | varchar(9) | DEFAULT NULL | ||
local_authority_code | varchar(9) | DEFAULT NULL | ||
CONSTRAINT pk_organization_id_id_patient_id_person_id | ||||
PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`) | ||||
CONSTRAINT fk_patient_address_patient_id_organization_id | ||||
FOREIGN KEY (patient_id, organization_id) | ||||
REFERENCES patient (id, organization_id) | ||||
COMMENT 'stores address details for patients' |
Table: event_log
Column name | Data type | Constraint | Comments | References |
dt_change | datetime(3) | NOT NULL | date time the change was made to this DB | |
change_type | tinyint | NOT NULL | type of transaction 0=insert, 1=update, 2=delete | |
table_id | tinyint | NOT NULL | identifier of the table changed | |
record_id | bigint | NOT NULL | id of the record changed |
Table: appointment_status
PRIMARY KEY (id)
Column name | Data type | Constraint | Comments | References |
id | smallint(6) | NOT NULL | ||
Value | varchar(50) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: concept
PRIMARY KEY (dbid)
Column name | Data type | Constraint | Comments | References |
Dbid | int(11) | NOT NULL | ||
Document | int(11) | NOT NULL | ||
Id | varchar(150) | NOT NULL | ||
Draft | tinyint(1) | NOT NULL | ||
Name | varchar(255) | DEFAULT NULL | ||
Description | varchar(400) | DEFAULT NULL | ||
Scheme | bigint(20) | DEFAULT NULL | ||
Code | varchar(40) | DEFAULT NULL | ||
use_count | bigint(20) | NOT NULL DEFAULT 0 | ||
updated datetime | datetime | NOT NULL | ||
CONSTRAINT PRIMARY KEY (dbid), |
Table: concept_map
PRIMARY KEY (legacy)
Column name | Data type | Constraint | Comments | References |
Legacy | int(11) | NOT NULL | ||
Core | int(11) | NOT NULL | ||
Updated | datetime | NOT NULL | ||
CONSTRAINT PRIMARY KEY (legacy) |
Table: concept_property_object
Column name | Data type | Constraint | Comments | References |
dbid | int(11) | NOT NULL | ||
group | int(11) | NOT NULL | ||
property | int(11) | NOT NULL | ||
value | int(11) | NOT NULL | ||
updated | datetime | NOT NULL |
Table: concept_tct
Column name | Data type | Constraint | Comments | References |
Source | int(11) | DEFAULT NULL | ||
Property | int(11) | DEFAULT NULL | ||
Level | int(11) | DEFAULT NULL | ||
Target | int(11) | DEFAULT NULL | ||
Status | int(11) | DEFAULT NULL | ||
created_date | datetime | DEFAULT NULL |
Table: consent_code
Column name | Data type | Constraint | Comments | References |
STATUS | varchar(10) | DEFAULT NULL | ||
DESCRIPTION | varchar(100) | DEFAULT NULL | ||
CODE | varchar(20) | DEFAULT NULL | ||
TERM_CODE | varchar(20) | DEFAULT NULL | ||
TERM | varchar(100) | DEFAULT NULL |
Table: date_precision
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Value | varchar(11) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: ethnicity_lookup
PRIMARY KEY(ethnic_code)
Column name | Data type | Constraint | Comments | References |
ethnic_code | char(1) | NOT NULL | ||
ethnic_name | varchar(100) | DEFAULT NULL | ||
CONSTRAINT PRIMARY KEY (ethnic_code) |
Table: patient_address_match
PRIMARY KEY(id, uprn)
Column name | Data type | Constraint | Comments | References |
Id | bigint(20) | NOT NULL | ||
Uprn | varchar(255) | NOT NULL | ||
Status | smallint(6) | DEFAULT NULL | ||
Classification | varchar(45) | DEFAULT NULL | ||
Latitude | double | DEFAULT NULL | ||
Longitude | double | DEFAULT NULL | ||
Xcoordinate | double | DEFAULT NULL | ||
Ycoordinate | double | DEFAULT NULL | ||
Qualifier | varchar(50) | DEFAULT NULL | ||
Algorithm | varchar(255) | DEFAULT NULL | ||
match_date | datetime | DEFAULT NULL | ||
abp_address_number | varchar(255) | DEFAULT NULL | ||
abp_address_street | varchar(255) | DEFAULT NULL | ||
abp_address_locality | varchar(255) | DEFAULT NULL | ||
abp_address_town | varchar(10) | DEFAULT NULL | ||
abp_address_postcode | varchar(10) | DEFAULT NULL | ||
abp_address_organization | varchar(255) | DEFAULT NULL | ||
match_pattern_postcode | varchar(255) | DEFAULT NULL | ||
match_pattern_street | varchar(255) | DEFAULT NULL | ||
match_pattern_number | varchar(255) | DEFAULT NULL | ||
match_pattern_building | varchar(255) | DEFAULT NULL | ||
match_pattern_flat | varchar(255) | DEFAULT NULL | ||
algorithm_version | varchar(255) | DEFAULT NULL | ||
Epoc | varchar(255) | DEFAULT NULL | ||
CONSTRAINT PRIMARY KEY (id,uprn) |
Table: patient_gender
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Value | varchar(10) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: patient_pseudo_id
PRIMARY KEY(organization_id)
Column name | Data type | Constraint | Comments | References |
Id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | ||
patient_id | bigint(20) | NOT NULL | ||
person_id | bigint(20) | NOT NULL | ||
salt_name | varchar(50) | NOT NULL | ||
Skid | varchar(255) | NOT NULL | ||
is_nhs_number_valid | tinyint(1) | NOT NULL | ||
is_nhs_number_verified_by_publisher | tinyint(1) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (organization_id) |
Table: procedure_request_status
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Value | varchar(50) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: referral_request_priority
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Value | varchar(50) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: referral_request_type
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Value | varchar(50) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: registration_status
PRIMARY KEY(id)
Column name | Data type | Constraint | Comments | References |
Id | smallint(6) | NOT NULL | ||
Code | varchar(10) | NOT NULL | ||
Description | varchar(50) | NOT NULL | ||
is_active | tinyint(1) | NOT NULL | ||
CONSTRAINT PRIMARY KEY (id) |
Table: registration_status_history
PRIMARY KEY(organization_id,id,patient_id,person_id)
Column name | Data type | Constraint | Comments | References |
Id | bigint(20) | NOT NULL | ||
organization_id | bigint(20) | NOT NULL | ||
patient_id | bigint(20) | NOT NULL | ||
person_id | bigint(20) | NOT NULL | ||
episode_of_care_id | bigint(20) | DEFAULT NULL | ||
registration_status_concept_id | int(11) | DEFAULT NULL | ||
start_date | datetime | DEFAULT NULL | ||
end_date | datetime | DEFAULT NULL | ||
CONSTRAINT PRIMARY KEY (organization_id,id,patient_id,person_id) |