Remote Subscriber Database (RSD) Schema (Compass 2): Difference between revisions
(DRAFT: Remote Subscriber Database (RSD) Schema (Compass 2)) |
mNo edit summary |
||
Line 935: | Line 935: | ||
== Table: encounter_additional == | == Table: encounter_additional == | ||
PRIMARY KEY (id, property_id, value_id) | PRIMARY KEY (id, property_id, value_id) | ||
{| class="wikitable" | {| class="wikitable sortable mw-collapsible mw-collapsed" | ||
|'''Column name''' | |'''Column name''' | ||
|'''Data type''' | |'''Data type''' | ||
Line 965: | Line 965: | ||
== Table: allergy_intolerance == | == Table: allergy_intolerance == | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
{| class="wikitable" | {| class="wikitable sortable mw-collapsible mw-collapsed" | ||
|'''Column name''' | |'''Column name''' | ||
|'''Data type''' | |'''Data type''' | ||
Line 1,073: | Line 1,073: | ||
== Table: medication_statement == | == Table: medication_statement == | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
{| class="wikitable" | {| class="wikitable sortable mw-collapsible mw-collapsed" | ||
|'''Column name''' | |'''Column name''' | ||
|'''Data type''' | |'''Data type''' | ||
Line 1,217: | Line 1,217: | ||
== Table: medication_order == | == Table: medication_order == | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
{| class="wikitable" | {| class="wikitable sortable mw-collapsible mw-collapsed" | ||
|'''Column name''' | |'''Column name''' | ||
|'''Data type''' | |'''Data type''' |
Revision as of 11:37, 29 July 2020
Table: organisation
PRIMARY KEY (pk_organization_id PRIMARY KEY (id))
Column name | Data type | Constraint | Comments | References |
Id | bigint | NOT NULL | ||
ods_code | varchar(50), | |||
Name | varchar(255) | |||
type_code | varchar(50) | |||
type_desc | varchar(255) | |||
Postcode | varchar(10) | |||
parent_organization_id | bigint | |||
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), | |||
type_code, | varchar(50) | |||
type_desc | varchar(255) | |||
postcode | varchar10), | |||
managing_organization_id | bigint | |||
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 | ||
Name | varchar(1024) | |||
role_code | varchar(50) | |||
role_desc | varchar(255) | |||
gmc_code | varchar(50) | |||
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 | ||
practitioner_id | bigint | |||
start_date | date | |||
type | varchar(255) | |||
Location | varchar(255) | |||
Name | varchar(150) | |||
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), | |||
first_names | varchar(255), | |||
last_name | varchar(255), | |||
gender_concept_id | int | |||
nhs_number | varchar(255), | |||
date_of_birth date, | date | |||
date_of_death date, | date | |||
current_address_id, | bigint | NOT NULL | ||
ethnic_code_concept_id | int | |||
registered_practice_organization_id | bigint | |||
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 | ||
person_id | bigint | NOT NULL | ||
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 | ||
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 | ||
patient_id | varchar(36) | NOT NULL | ||
person_id | bigint | NOT NULL | ||
practitioner_id | bigint | |||
schedule_id | bigint | |||
start_date | date | |||
planned_duration | integer | |||
actual_duration | integer | |||
appointment_status_concept_id | int | |||
patient_wait | integer | |||
patient_delay | integer | |||
date_time_sent_in | datetime | |||
date_time_left | datetime | |||
source_id | varchar(36) | |||
cancelled_date | Datetime | |||
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 | The clinician the activity is recorded against | ||
appointment_id | bigint | |||
clinical_effective_date | date | The date the clinical code is recorded for | ||
|
int | |||
episode_of_care_id | bigint | Is this instance of the code a review of a previous encounter | ||
service_provider_organization_id | bigint | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
age_at_event | decimal(5,2) | |||
Type | text | |||
sub_type | text | |||
admission_method | varchar(40) | |||
end_date | date | |||
institution_location_id | text | |||
date_recorded | datetime | |||
service_provider_organization_id | bigint(20) | DEFAULT NULL | ||
| ||||
/*known examples of Emis consultations referring to unknown appointments, so removed this | ||||
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 | |||
appointment_id | bigint | |||
clinical_effective_date | datetime | |||
date_precision_concept_id | int | |||
episode_of_care_id | bigint | |||
service_provider_organization_id | bigint | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
age_at_event | decimal(5,2) | |||
type | text | |||
sub_type | text | |||
admission_method | varchar(40) | |||
end_date | date | |||
institution_location_id | bigint | |||
date_recorded | datetime | |||
Finished | boolean | |||
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 | ||
date_precision_concept_id | Int | DEFAULT NULL | ||
is_review | boolean | NOT NULL | Is this instance of the code a review of a previous encounter | |
core_concept_id | Int | |||
non_core_concept_id | Int | |||
age_at_event | decimal(5,2) | |||
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 | ||||
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 | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
bnf_reference | varchar(6) | |||
age_at_event | decimal(5,2) | |||
Issue_method | text | |||
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 | encounter.id | ||
practitioner_id | bigint | The clinician the activity is recorded against | practitioner.id | |
clinical_effective_date | date | |||
date_precision_concept_id | int | |||
dose | varchar(1000) | |||
quantity_value | real | |||
quantity_unit | varchar(255) | |||
duration_days | integer | |||
estimated_cost | real | |||
medication_statement_id | bigint | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
bnf_reference | varchar(6) | |||
age_at_event | decimal(5,2) | |||
issue_method | text | |||
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 | ||
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 | ||
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 | |||
non_core_concept_id | int | |||
age_at_event | decimal (5,2) | |||
episodicity_concept_id | int | |||
is_primary | boolean | |||
date_recorded | datetime | |||
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 | |||
practitioner_id | bigint | |||
clinical_effective_date | date | |||
date_precision_concept_id | int | |||
result_value | real | |||
result_value_units | varchar(50) | |||
result_date | date | |||
result_text | text | |||
result_concept_id | int | |||
is_problem | boolean | NOT NULL | ||
is_review | boolean | NOT NULL | ||
problem_end_date | date | |||
parent_observation_id | bigint | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
age_at_event | decimal (5,2) | |||
episodicity_concept_id | int | |||
is_primary | boolean | |||
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 | |||
non_core_concept_id | int | |||
age_at_event | decimal(5,2) | |||
date_recorded | datetime | |||
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 | ||
requester_organization_id | bigint(20) | DEFAULT NULL | ||
recipient_organization_id | bigint(20) | DEFAULT NULL | ||
referral_request_priority_concept_id | ||||
referral_request_type_concept_id | int | referral.request.type | ||
Mode | varchar(50) | |||
outgoing_referral | boolean | |||
is_review | boolean | |||
core_concept_id | int | |||
non_core_concept_id | int | |||
age_at_event | decimal(5,2) | |||
date_recorded | datetime | |||
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 | |||
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 | ||
patient_id | bigint | NOT NULL | ||
person_id bigint | bigint | |||
use_concept_id | int | use of contact (e.g. mobile, home,work | ||
type_concept_id | int | type of contact (e.g. phone, email) | ||
start_date date | date | |||
end_date date | date | |||
value | varchar(255) | |||
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 | ||
patient_id | bigint | NOT NULL | ||
person_id | bigint | |||
address_line_1 | varchar(255) | |||
address_line_2 | varchar(255) | |||
address_line_3 | varchar(255) | |||
address_line_4 | varchar(255) | |||
city | varchar(255) | |||
postcode | varchar(255) | |||
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) |