DRAFT: Remote Subscriber Database (RSD) Schema (Compass 2)
From Voror_Wiki
More actions
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) | ||||