Remote Subscriber Database (RSD) Schema (Compass 2)
From Voror_Wiki
More actions
Table: allergy_intolerance
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the allergy | |
| 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 | Reference to the encounter this allergy was record in | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | Date | DEFAULT NULL | The date the clinical code is recorded for | |
| 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 | Reference to the clinical coding of the allergy | |
| non_core_concept_id | Int | DEFAULT NULL | Reference to the clinical coding of the allergy | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age the patient was at the time of this event | |
| date_recorded | datetime | NOT NULL | The date the allergy was recorded | |
| 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: appointment
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the appointment | |
| 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 |
| 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 | The start date of the appointment | |
| 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 | How long the patient was delayed for | |
| 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 | Unique reference to the source of the appointment | |
| cancelled_date | datetime | DEFAULT NULL | The date the appointment was cancelled | |
| 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: appointment_status
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | smallint(6) | NOT NULL | Unique Id of the appointment status | |
| Value | varchar(50) | NOT NULL | The value of the appointment status | |
| CONSTRAINT PRIMARY KEY (id) | ||||
Table: concept
PRIMARY KEY (dbid)
| Column name | Data type | Constraint | Comments | References |
| Dbid | int(11) | NOT NULL | Unique Id of the concept | |
| Document | int(11) | NOT NULL | Concept grouping construct, deprecated | |
| Id | varchar(150) | NOT NULL | Unique concept identifier | |
| Draft | tinyint(1) | NOT NULL | Whether its draft/autocreated or confirmed as a "proper" concept | |
| Name | varchar(255) | DEFAULT NULL | Short name | |
| Description | varchar(400) | DEFAULT NULL | Full name (or term for ontological concepts) | |
| Scheme | bigint(20) | DEFAULT NULL | The coding scheme for the code (Read, CTV3, SNOMED etc) | |
| Code | varchar(40) | DEFAULT NULL | The code (non-unique unless coupled with a scheme) | |
| use_count | bigint(20) | NOT NULL DEFAULT 0 | Rough indicator of number of occurences of the concept | |
| updated datetime | datetime | NOT NULL | The timestamp of the last update to the concept | |
| CONSTRAINT PRIMARY KEY (dbid), | ||||
Table: concept_map
PRIMARY KEY (legacy)
| Column name | Data type | Constraint | Comments | References |
| Legacy | int(11) | NOT NULL | the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept | |
| Core | int(11) | NOT NULL | the core (snomed, discovery) concept that the legacy concept maps to | |
| Updated | datetime | NOT NULL | Timestamp the map was last updated/added | |
| CONSTRAINT PRIMARY KEY (legacy) | ||||
Table: concept_property_object
| Column name | Data type | Constraint | Comments | References |
| dbid | int(11) | NOT NULL | Unique Id of the concept | |
| group | int(11) | NOT NULL | Grouping identifier | |
| property | int(11) | NOT NULL | The property concept | |
| value | int(11) | NOT NULL | The value concept for the given concepts property | |
| updated | datetime | NOT NULL | Timestamp the property value was updated/added |
Table: concept_tct
| Column name | Data type | Constraint | Comments | References |
| Source | int(11) | DEFAULT NULL | Source concept | |
| Property | int(11) | DEFAULT NULL | Property concept | |
| Level | int(11) | DEFAULT NULL | The "distance" of the from source (1=parent, 2=grandparent, etc) | |
| Target | int(11) | DEFAULT NULL | The target | |
| Status | int(11) | DEFAULT NULL | Whether the concept is active | |
| created_date | datetime | DEFAULT NULL | When the concept was created |
Table: consent_code
| Column name | Data type | Constraint | Comments | References |
| STATUS | varchar(10) | DEFAULT NULL | Whether the consent code is active | |
| DESCRIPTION | varchar(100) | DEFAULT NULL | Description of the consent code | |
| CODE | varchar(20) | DEFAULT NULL | The actual consent code | |
| TERM_CODE | varchar(20) | DEFAULT NULL | The clinical term code | |
| TERM | varchar(100) | DEFAULT NULL | The clinical term |
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: diagnostic_order
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the diagnostic order | |
| 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 | |
| encounter_id | bigint | DEFAULT NULL | Reference to the encounter the observation was recorded at | |
| practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | |
| clinical_effective_date | date | DEFAULT NULL | The date the diagnostic order was identified by a clinician | |
| 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) | |
| result_value | real | DEFAULT NULL | The value of the result of the observation | |
| result_value_units | varchar(50) | DEFAULT NULL | The units of the result of the observation | |
| result_date | date | DEFAULT NULL | The date of the result | |
| result_text | text | DEFAULT NULL | Any text associated with the result | |
| result_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the result | |
| is_problem | boolean | NOT NULL | Whether the observation is marked as a problem | |
| is_review | boolean | NOT NULL | Whether the observation is a review of an existing problem | |
| problem_end_date | date | DEFAULT NULL | The end date of the problem | |
| parent_observation_id | bigint | DEFAULT NULL | Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure | |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the observation | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the observation | |
| age_at_event | decimal (5,2) | DEFAULT NULL | The age of the patient at the time of the observation | |
| episodicity_concept_id | int | DEFAULT NULL | Reference to the episodicity of the problem eg First, review, flare | |
| is_primary | boolean | DEFAULT NULL | Whether the diagnostic order is a primary order | |
| 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: encounter
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the encounter | |
| 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 | Reference to the appointment this encounter took part on | |
| clinical_effective_date | date | DEFAULT NULL | The date the clinical code is recorded for | |
|
date_precision_concept_id |
int | DEFAULT NULL | Reference to the precision of the date of the encounter | |
| 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 | Reference to the service provider organisation | |
| core_concept_id | int | DEFAULT NULL | Reference to the type of encounter | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the type of encounter | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age the patient was when this encounter took place | |
| Type | text | DEFAULT NULL | Unused | |
| sub_type | text | DEFAULT NULL | Unused | |
| admission_method | varchar(40) | DEFAULT NULL | The admission method of the encounter | |
| end_date | date | DEFAULT NULL | The end date of the encounter | |
| institution_location_id | text | DEFAULT NULL | Reference to the institution the encounter took place at | |
| date_recorded | datetime | DEFAULT NULL | The date the encounter was recorded | |
|
CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id), | ||||
| 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_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: encounter_event
PRIMARY KEY (organization_id, person_id, id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the encounter event | |
| organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | |
| patient_id | bigint | NOT NULL | The patient this event belongs to | |
| person_id | bigint | NOT NULL | The person this event belongs to | |
| encounter_id | bigint | NOT NULL | Reference to the parent encounter record | |
| practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | |
| appointment_id | bigint | DEFAULT NULL | Reference to the appointment this encounter took part on | |
| clinical_effective_date | datetime | DEFAULT NULL | The date the encounter took place | |
| date_precision_concept_id | int | DEFAULT NULL | Reference to the precision of the date of the encounter | |
| episode_of_care_id | bigint | DEFAULT NULL | Reference to the episode of care this encounter belongs to | |
| service_provider_organization_id | bigint | DEFAULT NULL | Reference to the service provider organisation | |
| core_concept_id | int | DEFAULT NULL | Reference to the type of encounter | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the type of encounter | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age of the patient at the time of the encounter | |
| type | text | DEFAULT NULL | Unused | |
| sub_type | text | DEFAULT NULL | Unused | |
| admission_method | varchar(40) | DEFAULT NULL | The admission method of the encounter | |
| end_date | date | DEFAULT NULL | The end date of the encounter | |
| institution_location_id | bigint | DEFAULT NULL | Reference to the institution the encounter took place at | |
| date_recorded | datetime | DEFAULT NULL | The date the encounter was recorded | |
| finished | boolean | DEFAULT NULL | Whether the encounter is finished | |
| 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: episode_of_care
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the episode of care | |
| 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 | Reference to the registration type of the patient | registration.type.id |
| registration_status_concept_id | int | DEFAULT NULL | Reference to the registration status of the patient | |
| date_registered | date | DEFAULT NULL | The date the registration was started for this episode of care | |
| date_registered_end | date | DEFAULT NULL | The date the registration was ended for this episode of care | |
| usual_gp_practitioner_id | bigint | DEFAULT NULL | Reference to the usual GP for this episode of care | 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: ethnicity_lookup
PRIMARY KEY(ethnic_code)
| Column name | Data type | Constraint | Comments | References |
| ethnic_code | char(1) | NOT NULL | Ethnic code | |
| ethnic_name | varchar(100) | DEFAULT NULL | Corresponding ethnicity name | |
| CONSTRAINT PRIMARY KEY (ethnic_code) | ||||
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: flag
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL | Unique Id of the flag | |
| 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 | The date the flag was entered onto the patients record | |
| 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 | Whether the flag is active or not | |
| 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: location
PRIMARY KEY (pk_location_id PRIMARY KEY id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the location | |
| 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 | The type of location | |
| type_desc | varchar(255) | DEFAULT NULL | Textual description of the type of location eg GP Practice | |
| postcode | varchar10) | DEFAULT NULL | The postcode of the location | |
| managing_organization_id | bigint | DEFAULT NULL | Reference to the managing organisation of the location | |
| 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: medication_order
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the medication order | |
| 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 | Reference to the encounter the medication order was issued in | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the medication order was issued | |
| 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 | Textual description of the dose | |
| quantity_value | real | DEFAULT NULL | The value of the medication that was prescribed eg 50 | |
| quantity_unit | varchar(255) | DEFAULT NULL | The unit of the medication that was prescribed eg tablets | |
| duration_days | int | DEFAULT NULL | How many days the medication is prescribed for | |
| estimated_cost | real | DEFAULT NULL | The estimated cost of the medication | |
| medication_statement_id | bigint | DEFAULT NULL | Reference to the medication statement. A medication statement can have many medication orders | medication.statement.id |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the medication | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the medication | |
| bnf_reference | varchar(6) | DEFAULT NULL | A reference to the drug in the BNF dictionary | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age the patient was at the time of this event | |
| issue_method | text | DEFAULT NULL | The issue method of the medication eg hand written | |
| 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: medication_statement
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL | Unique Id of the medication | |
| 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 | Reference to the encounter this medication was recorded in | encounter.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the medication was clinical relevant | |
| 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 | Whether the medication is active or not | |
| cancellation_date | date | DEFAULT NULL | The date the medication was cancelled | |
| dose | varchar(1000) | DEFAULT NULL | Texual description of the dose of the medication | |
| quantity_value | double | DEFAULT NULL | The value of the medication that was prescribed eg 50 | |
| quantity_unit | varchar(255) | DEFAULT NULL | The unit of the medication that was prescribed eg tablets | |
| authorisation_type_concept_id | int | DEFAULT NULL | Reference to the authorisation type | |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the medication | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the medication | |
| bnf_reference | varchar(6) | DEFAULT NULL | A reference to the drug in the BNF dictionary | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age the patient was at the time of this event | |
| Issue_method | text | DEFAULT NULL | The issue method of the medication eg hand written | |
| 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: observation
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL | Unique Id of the observation | |
| 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 | Reference to the encounter the observation was recorded at | encounter.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the observation was identified by a clinician | |
| 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 | The value of the result of the observation | |
| result_value_units | varchar(50) | DEFAULT NULL | The units of the result of the observation | |
| result_date | date | DEFAULT NULL | The date of the result | |
| result_text | text | Any text associated with the result | ||
| result_concept_id | bigint(20) | DEFAULT NULL | Reference to the clinical coding of the result | |
| is_problem | tinyint(1) | NOT NULL | Whether the observation is marked as a problem | |
| is_review | tinyint(1) | NOT NULL | Whether the observation is a review of an existing problem | |
| problem_end_date | date | DEFAULT NULL | The end date of the problem | |
| parent_observation_id | bigint(20) | DEFAULT NULL | Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure | |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the observation | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the observation | |
| age_at_event | decimal (5,2) | DEFAULT NULL | The age of the patient at the time of the observation | |
| episodicity_concept_id | int | DEFAULT NULL | Reference to the episodicity of the problem eg First, review, flare | |
| is_primary | boolean | DEFAULT NULL | Whether the observation is a primary observation | |
| date_recorded | datetime | DEFAULT NULL | The date the observation was recorded in the system | |
| 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: organization
PRIMARY KEY (pk_organization_id PRIMARY KEY (id))
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the organisation | |
| ods_code | varchar(50) | DEFAULT NULL | ODS Code of the organisation | |
| Name | varchar(255) | DEFAULT NULL | Name of the organisation | |
| type_code | varchar(50) | DEFAULT NULL | The type of organisation | |
| type_desc | varchar(255) | DEFAULT NULL | Textual description of the type of organisation eg GP Practice | |
| Postcode | varchar(10) | DEFAULT NULL | The postcode of the organisation | |
| parent_organization_id | bigint | DEFAULT NULL | The id of the parent organisation | |
| PRIMARY KEY pk_organization_id PRIMARY KEY (id) | ||||
Table: patient
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the patient | |
| 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(255) | The title of the patient | ||
| first_names | varchar(255) | The first names of the patient | ||
| last_name | varchar(255) | The last name of the patient | ||
| gender_concept_id | int | Reference to the gender of the patient | patient.gender.id | |
| nhs_number | varchar(255) | The NHS number of the patient | ||
| date_of_birth | date | The date of birth of the patient | ||
| date_of_death | date | The date of death of the patient | ||
| current_address_id | bigint | Reference to the current address of the patient | ||
| ethnic_code_concept_id | int | Reference to the ethnicity of the patient | ||
| registered_practice_organization_id | bigint | Reference to the organisation the patient is registered at | ||
| birth_year | smallint | |||
| birth_month | tinyint | |||
| birth_week | tinyint | |||
| 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: patient_additional
PRIMARY KEY (id, property_id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the patient | |
| property_id | int | NOT NULL | IM reference (e.g. cause of death) | |
| value_id | int | NULL | IM reference (e.g. COVID) | person.id |
| json_value | json | NULL | Where there is no mapped value_id, just raw JSON | |
| text_value | varchar(255) | NULL | Where there is no mapped value_id or raw JSON, just a basic text value | |
| CONSTRAINT pk_patient_additional_id PRIMARY KEY (id, property_id) | ||||
Table: patient_address
PRIMARY KEY (organization_id,id,patient_id,person_id)
| Column name | Data type | Constraint | Comments | References |
| Id | bigint | NOT NULL | Unique Id of the address | |
| 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 | The first line of the address | |
| address_line_2 | varchar(255) | DEFAULT NULL | The second line of the address | |
| address_line_3 | varchar(255) | DEFAULT NULL | The third line of the address | |
| address_line_4 | varchar(255) | DEFAULT NULL | The fourth line of the address | |
| city | varchar(255) | DEFAULT NULL | The city | |
| postcode | varchar(255) | DEFAULT NULL | The postcode | |
| use_concept_id | int | NOT NULL | use of address (e.g. home, temporary) | |
| start_date | Date | NOT NULL | The start date of this address being relevant | |
| end_date | Date | DEFAULT NULL | The end date of this address being relevant | |
| lsoa_2001_code | varchar(9) | DEFAULT NULL | A reference to the LSOA_2001 code | |
| lsoa_2011_code | varchar(9) | DEFAULT NULL | A reference to the LSOA_2011 code | |
| msoa_2001_code | varchar(9) | DEFAULT NULL | A reference to the MSOA_2001 code | |
| msoa_2011_code | varchar(9) | DEFAULT NULL | A reference to the MSOA_2011 code | |
| ward_code | varchar(9) | DEFAULT NULL | The ward the address belongs to | |
| local_authority_code | varchar(9) | DEFAULT NULL | The local authority the address belongs to | |
| 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: patient_address_match
PRIMARY KEY(id, uprn)
| Column name | Data type | Constraint | Comments | References |
| Id | bigint(20) | NOT NULL | Reference to the patient_address table | |
| Uprn | varchar(255) | NOT NULL | The Unique Propery Reference Number of the address | |
| Status | smallint(6) | DEFAULT NULL | Whether the UPRN is active | |
| Classification | varchar(45) | DEFAULT NULL | Building type eg flat, pub, house etc | |
| Latitude | double | DEFAULT NULL | The latitude of the UPRN | |
| Longitude | double | DEFAULT NULL | The longitude of the UPRN | |
| Xcoordinate | double | DEFAULT NULL | The x coordinate of the UPRN | |
| Ycoordinate | double | DEFAULT NULL | The y coordinate of the UPRN | |
| Qualifier | varchar(50) | DEFAULT NULL | How the match is determined eg equivalent match, near match etc | |
| Algorithm | varchar(255) | DEFAULT NULL | Which algorithm was used to match the address to the UPRN | |
| match_date | datetime | DEFAULT NULL | The date the match was made | |
| abp_address_number | varchar(255) | DEFAULT NULL | The number value of the address in the ABP dictionary | |
| abp_address_street | varchar(255) | DEFAULT NULL | The street value of the address in the ABP dictionary | |
| abp_address_locality | varchar(255) | DEFAULT NULL | The locality value of the address in the ABP dictionary | |
| abp_address_town | varchar(10) | DEFAULT NULL | The town value of the address in the ABP dictionary | |
| abp_address_postcode | varchar(10) | DEFAULT NULL | The postcode value of the address in the ABP dictionary | |
| abp_address_organization | varchar(255) | DEFAULT NULL | The organization value of the address in the ABP dictionary | |
| match_pattern_postcode | varchar(255) | DEFAULT NULL | The qualifier used to match the postcode | |
| match_pattern_street | varchar(255) | DEFAULT NULL | The qualifier used to match the street | |
| match_pattern_number | varchar(255) | DEFAULT NULL | The qualifier used to match the number | |
| match_pattern_building | varchar(255) | DEFAULT NULL | The qualifier used to match the building | |
| match_pattern_flat | varchar(255) | DEFAULT NULL | The qualifier used to match the flat | |
| algorithm_version | varchar(255) | DEFAULT NULL | What version of the algorithm was used to make the match | |
| Epoc | varchar(255) | DEFAULT NULL | The version of the ABP files the addresses were matched against | |
| CONSTRAINT PRIMARY KEY (id,uprn) | ||||
Table: patient_contact
PRIMARY KEY (organization_id,id,patient_id,person_id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the patient contact | |
| 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 | The start date of the contact being valid | |
| end_date date | date | DEFAULT NULL | The end date of the contact being valid | |
| value | varchar(255) | DEFAULT NULL | The value of the contact information eg phone number, email address | |
| 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_gender
PRIMARY KEY(id)
| Column name | Data type | Constraint | Comments | References |
| Id | smallint(6) | NOT NULL | Unique Id of the gender lookup | |
| Value | varchar(10) | NOT NULL | Value of the gender lookup | |
| CONSTRAINT PRIMARY KEY (id) | ||||
Table: patient_pseudo_id
PRIMARY KEY(organization_id)
| Column name | Data type | Constraint | Comments | References |
| Id | bigint(20) | NOT NULL | Unique Id of the patient pseudo id | |
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | |
| patient_id | bigint(20) | NOT NULL | Reference to the patient this registration status history belongs to | |
| person_id | bigint(20) | NOT NULL | Reference to the person this registration status history belongs to | |
| salt_name | varchar(50) | NOT NULL | The name of the salt used to create the pseudo id | |
| Skid | varchar(255) | NOT NULL | "Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth) | |
| is_nhs_number_valid | tinyint(1) | NOT NULL | Whether the nhs number is valid | |
| is_nhs_number_verified_by_publisher | tinyint(1) | NOT NULL | Whether the nhs number has been verified by the publisher | |
| CONSTRAINT PRIMARY KEY (organization_id) | ||||
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: person
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the person | |
| organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | |
| title | varchar(255) | DEFAULT NULL | The title of the person | |
| first_names | varchar(255) | DEFAULT NULL | The first names of the person | |
| last_name | varchar(255) | DEFAULT NULL | The last name of the person | |
| gender_concept_id | int | DEFAULT NULL | Reference to the gender of the person | |
| nhs_number | varchar(255) | DEFAULT NULL | The NHS number of the person | |
| date_of_birth date, | date | DEFAULT NULL | The date of birth of the person | |
| date_of_death date, | date | DEFAULT NULL | The date of death of the person | |
| current_address_id, | bigint | NOT NULL | Reference to the current address of the person | |
| ethnic_code_concept_id | int | DEFAULT NULL | Reference to the ethnicity of the person | |
| registered_practice_organization_id | bigint | DEFAULT NULL | Reference to the organisation the person is registered at | |
| birth_year | smallint | DEFAULT NULL | ||
| birth_month | tinyint | DEFAULT NULL | ||
| birth_week | tinyint | DEFAULT NULL | ||
| CONSTRAINT pk_person_id PRIMARY KEY (id) | ||||
Table: practitioner
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the practitioner | |
| organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organisation.id |
| name | varchar(1024) | DEFAULT NULL | Name of the practitioner | |
| role_code | varchar(50) | DEFAULT NULL | The code representing the role of the practitioner | |
| role_desc | varchar(255) | DEFAULT NULL | Textual description of the role of the practitioner eg General Medical Practitioner | |
| gmc_code | varchar(50) | DEFAULT NULL | The GMC code of the practitioner | |
| 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: procedure_request
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL | Unique Id of the procedure | |
| 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 | Reference to the encounter the procedure was administered at | encounter.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the procedure was administered by a clinician | |
| 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) | |
| status_concept_id | int | DEFAULT NULL | Reference to the status of the procedure | |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the procedure | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the procedure | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age of the patient at the time of the procedure | |
| date_recorded | datetime | DEFAULT NULL | The date the procedure was recorded in the source system | |
| 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: procedure_request_status
PRIMARY KEY(id)
| Column name | Data type | Constraint | Comments | References |
| Id | smallint(6) | NOT NULL | Unique Id of the procedure request status lookup | |
| Value | varchar(50) | NOT NULL | The value of the procedure request status lookup | |
| CONSTRAINT PRIMARY KEY (id) | ||||
Table: pseudo_id
PRIMARY KEY (patient_id, salt_key_name)
| Column name | Data type | Constraint | Comments | References |
| Id | bigint | NOT NULL | Unique Id of the pseudo Id | |
| patient_id | bigint | NOT NULL | Reference to the patient the pseudo Id belongs to | |
| salt_key_name | varchar(50) | NOT NULL | The name of the salt key used to create the pseudo id | |
| pseudo_id | varchar(255) | DEFAULT NULL | The pseudo id | |
| CONSTRAINT pk_pseudo_id PRIMARY KEY (patient_id, salt_key_name) | ||||
Table: referral_request
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL | Unique Id of the referral | |
| 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 | Reference to the encounter the referral was made in | encounter.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the referral was made | |
| 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 | Reference to the organisation that made the refereral request | |
| recipient_organization_id | bigint(20) | DEFAULT NULL | Reference to the organization receiving the referral | |
| referral_request_priority_concept_id | smallint(6) | DEFAULT NULL | Reference to the priority of the referral | referral.request.priority |
| referral_request_type_concept_id | int | DEFAULT NULL | Reference to the type of referral request | referral.request.type |
| Mode | varchar(50) | DEFAULT NULL | The mode of the referral | |
| outgoing_referral | boolean | DEFAULT NULL | Whether this is an outgoing referral | |
| is_review | boolean | DEFAULT NULL | Whether this referral is a review | |
| core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the referral | |
| non_core_concept_id | int | DEFAULT NULL | Reference to the clinical coding of the referral | |
| age_at_event | decimal(5,2) | DEFAULT NULL | The age of the patient at the time of the referral | |
| date_recorded | datetime | DEFAULT NULL | The date the referral request was added to the source system | |
| 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: referral_request_priority
PRIMARY KEY(id)
| Column name | Data type | Constraint | Comments | References |
| Id | smallint(6) | NOT NULL | Unique Id of the referral request priority lookup | |
| Value | varchar(50) | NOT NULL | The value of the referral request priority lookup | |
| CONSTRAINT PRIMARY KEY (id) | ||||
Table: referral_request_type
PRIMARY KEY(id)
| Column name | Data type | Constraint | Comments | References |
| Id | smallint(6) | NOT NULL | Unique Id of the referral request type | |
| Value | varchar(50) | NOT NULL | The value of the referral request lookup | |
| CONSTRAINT PRIMARY KEY (id) | ||||
Table: registration_status
PRIMARY KEY(id)
| Column name | Data type | Constraint | Comments | References |
| Id | smallint(6) | NOT NULL | Unique Id of the registration status | |
| Code | varchar(10) | NOT NULL | The registration status code | |
| Description | varchar(50) | NOT NULL | The description of the registration status | |
| is_active | tinyint(1) | NOT NULL | Whether the registration status is active | |
| 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 | Unique Id of the registration status history | |
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | |
| patient_id | bigint(20) | NOT NULL | Reference to the patient this registration status history belongs to | |
| person_id | bigint(20) | NOT NULL | Reference to the person this registration status history belongs to | |
| episode_of_care_id | bigint(20) | DEFAULT NULL | Reference to the episode of care this status history belongs to | |
| registration_status_concept_id | int(11) | DEFAULT NULL | Reference to the registration status | |
| start_date | datetime | DEFAULT NULL | The start date for the period this registration status history was valid | |
| end_date | datetime | DEFAULT NULL | The end date for the period this registration status history was valid | |
| PRIMARY KEY (organization_id,id,patient_id,person_id) | ||||
| CONSTRAINT fk_registration_status_history_episode_id FOREIGN KEY (episode_of_care_id) | ||||
Table: schedule
PRIMARY KEY (organization_id, id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint | NOT NULL | Unique Id of the schedule | |
| organization_id | bigint | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
| practitioner_id | bigint | DEFAULT NULL | Reference to the practitioner who owns the schedule | practitioner.id |
| start_date | date | DEFAULT NULL | The start date of the schedule | |
| type | varchar(255) | DEFAULT NULL | The type of schedule eg Timed Appointments | |
| Location | varchar(255) | DEFAULT NULL | Textual description of the location the schedule was held at | location.id |
| Name | varchar(150) | DEFAULT NULL | The name of the schedule | |
| 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 | ||||