Compass 2.3.0 Schema Documentation
Jump to navigation
Jump to search
Remote Subscriber Database (RSD) Schema (Compass 2) Version: 2.3
allergy_intolerance
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the allergy' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter this allergy was record in' | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the clinical code is recorded for' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| is_review | tinyint(1) | NOT NULL | 'Is this instance of the code a review of a previous encounter' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the allergy' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the allergy' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age the patient was at the time of this event' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the allergy was recorded' | No Foreign Key reference |
| Unique index name: `allergy_intolerance_id` Index Columns: (id) | ||||
| Index name: `fk_allergy_intolerance_encounter_id` Index Columns: (encounter_id) | ||||
| Index name: `fk_allergy_intolerance_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_allergy_intolerance_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `allergy_intolerance_patient_id` Index Columns: (patient_id) | ||||
| Index name: `allergy_intolerance_core_concept_id` Index Columns: (core_concept_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
appointment
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the appointment' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | organization.id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | No Foreign Key reference |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| schedule_id | bigint | DEFAULT NULL | 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule' | No Foreign Key reference |
| start_date | datetime | DEFAULT NULL | 'The start date of the appointment' | No Foreign Key reference |
| planned_duration | int | DEFAULT NULL | 'The time allocated for the appointment, not necessarily the actual duration always in minutes' | No Foreign Key reference |
| actual_duration | int | DEFAULT NULL | 'Time between sent in and left always in minutes' | No Foreign Key reference |
| appointment_status_concept_id | int | DEFAULT NULL | 'The status of the appointment e.g. arrived/sent in/left/DNA' | No Foreign Key reference |
| patient_wait | int | DEFAULT NULL | 'How long the patient waited from being marked as arrived to being sent in' | No Foreign Key reference |
| patient_delay | int | DEFAULT NULL | 'How long the patient was delayed for' | No Foreign Key reference |
| date_time_sent_in | datetime | DEFAULT NULL | 'Date and time the patient was sent into the practitioner' | No Foreign Key reference |
| date_time_left | datetime | DEFAULT NULL | 'Date and time the patient left the practitioner' | No Foreign Key reference |
| source_id | varchar(36) | DEFAULT NULL | 'Unique reference to the source of the appointment' | No Foreign Key reference |
| cancelled_date | datetime | DEFAULT NULL | 'The date the appointment was cancelled' | No Foreign Key reference |
| Unique index name: `appointment_id` Index Columns: (id) | ||||
| Index name: `fk_appointment_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `appointment_patient_id` Index Columns: (patient_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
appointment_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'same as the id column on the patient table ' | No Foreign Key reference |
| property_id | int | NOT NULL | 'IM reference (e.g. appointment type)' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'IM reference (e.g. appointment type)' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'where there is no mapped value_id, just raw JSON' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' | No Foreign Key reference |
| Index name: `appointment_additional_value_id` Index Columns: (value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
concept
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| dbid | int | NOT NULL | 'Unique concept int DB identifier' | No Foreign Key reference |
| document | int | DEFAULT NULL | 'Document this concept originated from' | No Foreign Key reference |
| id | varchar(150) | CHARACTER SET | 'Unique human-readable concept id' | No Foreign Key reference |
| draft | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| name | varchar(255) | DEFAULT NULL | 'Short name' | No Foreign Key reference |
| description | varchar(400) | DEFAULT NULL | 'Full name (or term for ontological concepts)' | No Foreign Key reference |
| scheme | bigint | DEFAULT NULL | 'The coding scheme for the code (Read, CTV3, SNOMED etc)' | No Foreign Key reference |
| code | varchar(40) | CHARACTER SET | 'The code (non-unique unless coupled with a scheme)' | No Foreign Key reference |
| use_count | bigint | No Constraint | 'Rough indicator of number of occurences of the concept' | No Foreign Key reference |
| updated | datetime | NOT NULL | 'The timestamp of the last update to the concept' | No Foreign Key reference |
| Index name: `ix_scheme_code` Index Columns: (scheme,code) | ||||
| Index name: `ix_code` Index Columns: (code) | ||||
| Index name: `ix_dbid_code` Index Columns: (dbid,code) | ||||
| PRIMARY KEY CONSTRAINT(s) (`dbid`) | ||||
concept_map
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| legacy | int | NOT NULL | 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept' | No Foreign Key reference |
| core | int | NOT NULL | 'the core (snomed, discovery) concept that the legacy concept maps to' | No Foreign Key reference |
| updated | datetime | NOT NULL | 'Timestamp the map was last updated/added' | No Foreign Key reference |
| id | int | NOT NULL, | No comment yet added | No Foreign Key reference |
| deleted | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `concept_map_uq` Index Columns: (legacy,deleted,updated) | ||||
| Index name: `ix_legacy_core` Index Columns: (legacy,core) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
database_version
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| version | varchar(255) | NOT NULL | 'version of the database' | No Foreign Key reference |
| valid_from | date | NOT NULL | 'date that the version was updated' | No Foreign Key reference |
| valid_to | date | DEFAULT NULL | 'date that the version was made outdated' | No Foreign Key reference |
| wiki_page | varchar(255) | DEFAULT NULL | 'the wiki page where this database structure is found' | No Foreign Key reference |
| Unique index name: `valid_to_uq` Index Columns: (valid_to) | ||||
| PRIMARY KEY CONSTRAINT(s) (`valid_from`) | ||||
diagnostic_order
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the diagnostic order' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter the observation was recorded at' | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the diagnostic order was identified by a clinician' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| result_value | double | DEFAULT NULL | 'The value of the result of the observation' | No Foreign Key reference |
| result_value_units | varchar(50) | DEFAULT NULL | 'The units of the result of the observation' | No Foreign Key reference |
| result_date | date | DEFAULT NULL | 'The date of the result' | No Foreign Key reference |
| result_text | text | No Constraint | 'Any text associated with the result' | No Foreign Key reference |
| result_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the result' | No Foreign Key reference |
| is_problem | tinyint(1) | NOT NULL | 'Whether the observation is marked as a problem' | No Foreign Key reference |
| is_review | tinyint(1) | NOT NULL | 'Whether the observation is a review of an existing problem' | No Foreign Key reference |
| problem_end_date | date | DEFAULT NULL | 'The end date of the problem' | No Foreign Key reference |
| parent_observation_id | bigint | DEFAULT NULL | 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the observation' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the observation' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age of the patient at the time of the observation' | No Foreign Key reference |
| episodicity_concept_id | int | DEFAULT NULL | 'Reference to the episodicity of the problem eg First, review, flare' | No Foreign Key reference |
| is_primary | tinyint(1) | DEFAULT NULL | 'Whether the diagnostic order is a primary order' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `diagnostic_order_id` Index Columns: (id) | ||||
| Index name: `fk_diagnostic_order_encounter_id` Index Columns: (encounter_id) | ||||
| Index name: `fk_diagnostic_order_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_diagnostic_order_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `diagnostic_order_patient_id` Index Columns: (patient_id) | ||||
| Index name: `diagnostic_order_core_concept_id` Index Columns: (core_concept_id) | ||||
| Index name: `diagnostic_order_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem) | ||||
| Index name: `diagnostic_order_core_concept_id_result_value` Index Columns: (core_concept_id,result_value) | ||||
| Index name: `diagnostic_order_non_core_concept_id` Index Columns: (non_core_concept_id) | ||||
| Index name: `ix_diagnostic_order_organization_id` Index Columns: (organization_id) | ||||
| Index name: `ix_diagnostic_order_clinical_effective_date` Index Columns: (clinical_effective_date) | ||||
| Index name: `ix_diagnostic_order_person_id` Index Columns: (person_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
encounter
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the encounter' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| appointment_id | bigint | DEFAULT NULL | 'Reference to the appointment this encounter took part on' | No Foreign Key reference |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the clinical code is recorded for' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Reference to the precision of the date of the encounter' | No Foreign Key reference |
| episode_of_care_id | bigint | DEFAULT NULL | 'Is this instance of the code a review of a previous encounter' | episode_of_care.id |
| service_provider_organization_id | bigint | DEFAULT NULL | 'Reference to the service provider organisation' | organization.id |
| core_concept_id | int | DEFAULT NULL | 'Reference to the type of encounter' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the type of encounter' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age the patient was when this encounter took place' | No Foreign Key reference |
| type | text | No Constraint | 'Unused' | No Foreign Key reference |
| sub_type | text | No Constraint | 'Unused' | No Foreign Key reference |
| admission_method | varchar(40) | DEFAULT NULL | 'The admission method of the encounter' | No Foreign Key reference |
| end_date | datetime | DEFAULT NULL | 'The end date of the encounter' | No Foreign Key reference |
| institution_location_id | text | No Constraint | 'Reference to the institution the encounter took place at' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the encounter was recorded' | No Foreign Key reference |
| Unique index name: `encounter_id` Index Columns: (id) | ||||
| Index name: `fk_encounter_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `fk_encounter_episode_of_care_id` Index Columns: (episode_of_care_id) | ||||
| Index name: `fk_encounter_service_provider_organization_id` Index Columns: (service_provider_organization_id) | ||||
| Index name: `encounter_patient_id` Index Columns: (patient_id) | ||||
| Index name: `fki_encounter_appointment_id` Index Columns: (appointment_id) | ||||
| Index name: `fki_encounter_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `encounter_core_concept_id_clinical_effective_date` Index Columns: (core_concept_id,clinical_effective_date) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
encounter_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'same as the id column on the encounter table' | No Foreign Key reference |
| property_id | int | NOT NULL | 'IM concept id reference (i.e. Admission method)' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'IM concept id reference (i.e. Emergency admission)' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)' | No Foreign Key reference |
| Index name: `encounter_additional_value_id` Index Columns: (value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
encounter_event
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the encounter event' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The patient this event belongs to' | patient.id |
| person_id | bigint | NOT NULL | 'The person this event belongs to' | No Foreign Key reference |
| encounter_id | bigint | NOT NULL | 'Reference to the parent encounter record' | No Foreign Key reference |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| appointment_id | bigint | DEFAULT NULL | 'Reference to the appointment this encounter took part on' | No Foreign Key reference |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the encounter took place' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Reference to the precision of the date of the encounter' | No Foreign Key reference |
| episode_of_care_id | bigint | DEFAULT NULL | 'Reference to the episode of care this encounter belongs to' | episode_of_care.id |
| service_provider_organization_id | bigint | DEFAULT NULL | 'Reference to the service provider organisation' | organization.id |
| core_concept_id | int | DEFAULT NULL | 'Reference to the type of encounter' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the type of encounter' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age of the patient at the time of the encounter' | No Foreign Key reference |
| type | text | No Constraint | 'Unused' | No Foreign Key reference |
| sub_type | text | No Constraint | 'Unused' | No Foreign Key reference |
| admission_method | varchar(40) | DEFAULT NULL | 'The admission method of the encounter' | No Foreign Key reference |
| end_date | datetime | DEFAULT NULL | 'The end date of the encounter' | No Foreign Key reference |
| institution_location_id | bigint | DEFAULT NULL | 'Reference to the institution the encounter took place at' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the encounter was recorded' | No Foreign Key reference |
| finished | tinyint(1) | DEFAULT NULL | 'Whether the encounter is finished' | No Foreign Key reference |
| Unique index name: `encounter_event_id` Index Columns: (id) | ||||
| Index name: `fk_encounter_event_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_encounter_event_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `fk_encounter_event_episode_of_care_id` Index Columns: (episode_of_care_id) | ||||
| Index name: `fk_encounter_event_service_provider_organization_id` Index Columns: (service_provider_organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
episode_of_care
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the episode of care' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| registration_type_concept_id | int | DEFAULT NULL | 'Reference to the registration type of the patient' | No Foreign Key reference |
| registration_status_concept_id | int | DEFAULT NULL | 'Reference to the registration status of the patient' | No Foreign Key reference |
| date_registered | date | DEFAULT NULL | 'The date the registration was started for this episode of care' | No Foreign Key reference |
| date_registered_end | date | DEFAULT NULL | 'The date the registration was ended for this episode of care' | No Foreign Key reference |
| usual_gp_practitioner_id | bigint | DEFAULT NULL | 'Reference to the usual GP for this episode of care' | practitioner.id |
| Unique index name: `episode_of_care_id` Index Columns: (id) | ||||
| Index name: `fk_episode_of_care_patient_id_organisation_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_episode_of_care_practitioner_id` Index Columns: (usual_gp_practitioner_id) | ||||
| Index name: `episode_of_care_patient_id` Index Columns: (patient_id) | ||||
| Index name: `episode_of_care_registration_type_concept_id` Index Columns: (registration_type_concept_id) | ||||
| Index name: `episode_of_care_date_registered` Index Columns: (date_registered) | ||||
| Index name: `episode_of_care_date_registered_end` Index Columns: (date_registered_end) | ||||
| Index name: `episode_of_care_person_id` Index Columns: (person_id) | ||||
| Index name: `episode_of_care_organization_id` Index Columns: (organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
event_log
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| dt_change | datetime(3) | NOT NULL | 'date time the change was made to this DB' | No Foreign Key reference |
| change_type | tinyint | NOT NULL | 'type of transaction 0=insert, 1=update, 2=delete' | No Foreign Key reference |
| table_id | tinyint | NOT NULL | 'identifier of the table changed' | No Foreign Key reference |
| record_id | bigint | NOT NULL | 'id of the record changed' | No Foreign Key reference |
flag
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the flag' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| effective_date | datetime | DEFAULT NULL | 'The date the flag was entered onto the patients record' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| is_active | tinyint(1) | NOT NULL | 'Whether the flag is active or not' | No Foreign Key reference |
| flag_text | text | No Constraint | 'This is a warning set by the publisher regarding he patient' | No Foreign Key reference |
| Unique index name: `flag_id` Index Columns: (id) | ||||
| Index name: `fk_flag_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `flag_patient_id` Index Columns: (patient_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
location
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the location' | No Foreign Key reference |
| name | varchar(255) | DEFAULT NULL | 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary' | No Foreign Key reference |
| type_code | varchar(50) | DEFAULT NULL | 'The type of location' | No Foreign Key reference |
| type_desc | varchar(255) | DEFAULT NULL | 'Textual description of the type of location eg GP Practice' | No Foreign Key reference |
| postcode | varchar(10) | DEFAULT NULL | 'The postcode of the location' | No Foreign Key reference |
| managing_organization_id | bigint | DEFAULT NULL | 'Reference to the managing organisation of the location' | organization.id |
| Unique index name: `location_id` Index Columns: (id) | ||||
| Index name: `fk_location_managing_organisation_id` Index Columns: (managing_organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
medication_order
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the medication order' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter the medication order was issued in' | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the medication order was issued' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| dose | varchar(1000) | DEFAULT NULL | 'Textual description of the dose' | No Foreign Key reference |
| quantity_value | double | DEFAULT NULL | 'The value of the medication that was prescribed eg 50' | No Foreign Key reference |
| quantity_unit | varchar(255) | DEFAULT NULL | 'The unit of the medication that was prescribed eg tablets' | No Foreign Key reference |
| duration_days | int | DEFAULT NULL | 'How many days the medication is prescribed for' | No Foreign Key reference |
| estimated_cost | double | DEFAULT NULL | 'The estimated cost of the medication' | No Foreign Key reference |
| medication_statement_id | bigint | DEFAULT NULL | 'Reference to the medication statement. A medication statement can have many medication orders' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the medication' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the medication' | No Foreign Key reference |
| bnf_reference | varchar(6) | DEFAULT NULL | 'A reference to the drug in the BNF dictionary' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age the patient was at the time of this event' | No Foreign Key reference |
| issue_method | text | No Constraint | 'The issue method of the medication eg hand written' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `medication_order_id` Index Columns: (id) | ||||
| Index name: `fk_medication_order_encounter_id` Index Columns: (encounter_id) | ||||
| Index name: `fk_medication_order_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_medication_order_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `medication_order_patient_id` Index Columns: (patient_id) | ||||
| Index name: `medication_order_core_concept_id` Index Columns: (core_concept_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
medication_statement
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the medication' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter this medication was recorded in' | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the medication was clinical relevant' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| cancellation_date | date | DEFAULT NULL | 'The date the medication was cancelled' | No Foreign Key reference |
| dose | varchar(1000) | DEFAULT NULL | 'Texual description of the dose of the medication' | No Foreign Key reference |
| quantity_value | double | DEFAULT NULL | 'The value of the medication that was prescribed eg 50' | No Foreign Key reference |
| quantity_unit | varchar(255) | DEFAULT NULL | 'The unit of the medication that was prescribed eg tablets' | No Foreign Key reference |
| authorisation_type_concept_id | int | DEFAULT NULL | 'Reference to the authorisation type' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the medication' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the medication' | No Foreign Key reference |
| bnf_reference | varchar(6) | DEFAULT NULL | 'A reference to the drug in the BNF dictionary' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age the patient was at the time of this event' | No Foreign Key reference |
| issue_method | text | No Constraint | 'The issue method of the medication eg hand written' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `medication_statement_id` Index Columns: (id) | ||||
| Index name: `fk_medication_statement_encounter_id` Index Columns: (encounter_id) | ||||
| Index name: `fk_medication_statement_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_medication_statement_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `medication_statement_patient_id` Index Columns: (patient_id) | ||||
| Index name: `medication_statement_dmd_id` Index Columns: (core_concept_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
observation
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the observation' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter the observation was recorded at' | encounter.id |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the observation was identified by a clinician' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| result_value | double | DEFAULT NULL | 'The value of the result of the observation' | No Foreign Key reference |
| result_value_units | varchar(50) | DEFAULT NULL | 'The units of the result of the observation' | No Foreign Key reference |
| result_date | date | DEFAULT NULL | 'The date of the result' | No Foreign Key reference |
| result_text | text | No Constraint | 'Any text associated with the result' | No Foreign Key reference |
| result_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the result' | No Foreign Key reference |
| is_problem | tinyint(1) | NOT NULL | 'Whether the observation is marked as a problem' | No Foreign Key reference |
| is_review | tinyint(1) | NOT NULL | 'Whether the observation is a review of an existing problem' | No Foreign Key reference |
| problem_end_date | date | DEFAULT NULL | 'The end date of the problem' | No Foreign Key reference |
| parent_observation_id | bigint | DEFAULT NULL | 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the observation' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the observation' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age of the patient at the time of the observation' | No Foreign Key reference |
| episodicity_concept_id | int | DEFAULT NULL | 'Reference to the episodicity of the problem eg First, review, flare' | No Foreign Key reference |
| is_primary | tinyint(1) | DEFAULT NULL | 'Whether the observation is a primary observation' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the observation was recorded in the system' | No Foreign Key reference |
| Unique index name: `observation_id` Index Columns: (id) | ||||
| Index name: `fk_observation_encounter_id` Index Columns: (encounter_id) | ||||
| Index name: `fk_observation_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_observation_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `observation_patient_id` Index Columns: (patient_id) | ||||
| Index name: `observation_core_concept_id` Index Columns: (core_concept_id) | ||||
| Index name: `observation_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem) | ||||
| Index name: `observation_core_concept_id_result_value` Index Columns: (core_concept_id,result_value) | ||||
| Index name: `observation_non_core_concept_id` Index Columns: (non_core_concept_id) | ||||
| Index name: `ix_observation_organization_id` Index Columns: (organization_id) | ||||
| Index name: `ix_observation_clinical_effective_date` Index Columns: (clinical_effective_date) | ||||
| Index name: `ix_observation_person_id` Index Columns: (person_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
observation_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'same as the id column on the observation table' | No Foreign Key reference |
| property_id | int | NOT NULL | 'IM reference (i.e. significance)' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'IM reference (i.e. minor, significant)' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'the JSON data itself ' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'where there is no mapped value_id or raw JSON, just a basic text value' | No Foreign Key reference |
| Index name: `observation_additional_value_id` Index Columns: (value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
organization
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the organisation' | No Foreign Key reference |
| ods_code | varchar(50) | DEFAULT NULL | 'ODS Code of the organisation' | No Foreign Key reference |
| name | varchar(255) | DEFAULT NULL | 'Name of the organisation' | No Foreign Key reference |
| type_code | varchar(50) | DEFAULT NULL | 'The type of organisation' | No Foreign Key reference |
| type_desc | varchar(255) | DEFAULT NULL | 'Textual description of the type of organisation eg GP Practice' | No Foreign Key reference |
| postcode | varchar(10) | DEFAULT NULL | 'The postcode of the organisation' | No Foreign Key reference |
| parent_organization_id | bigint | DEFAULT NULL | 'The id of the parent organisation' | No Foreign Key reference |
| Unique index name: `organization_id` Index Columns: (id) | ||||
| Index name: `fki_organization_parent_organization_id` Index Columns: (parent_organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
organization_metadata
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'organisation ID, corresponds to same ID in the organizaton table' | No Foreign Key reference |
| publishing_software | varchar(50) | NOT NULL | 'software name of publishing system, i.e. SystmOne' | No Foreign Key reference |
| last_data_to_dds | datetime | DEFAULT NULL | 'date time data was last sent to DDS' | No Foreign Key reference |
| last_data_cutoff | datetime | DEFAULT NULL | 'cutoff date time of the last extract from the publishing system' | No Foreign Key reference |
| PRIMARY KEY CONSTRAINT(s) (`id`,`publishing_software`) | ||||
patient
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the patient' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | organization.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| title | varchar(255) | DEFAULT NULL | 'The title of the patient' | No Foreign Key reference |
| first_names | varchar(255) | DEFAULT NULL | 'The first names of the patient' | No Foreign Key reference |
| last_name | varchar(255) | DEFAULT NULL | 'The last name of the patient' | No Foreign Key reference |
| gender_concept_id | int | DEFAULT NULL | 'Reference to the gender of the patient' | No Foreign Key reference |
| nhs_number | varchar(255) | DEFAULT NULL | 'The NHS number of the patient' | No Foreign Key reference |
| date_of_birth | date | DEFAULT NULL | 'The date of birth of the patient' | No Foreign Key reference |
| date_of_death | date | DEFAULT NULL | 'The date of death of the patient' | No Foreign Key reference |
| current_address_id | bigint | DEFAULT NULL | 'Reference to the current address of the patient' | No Foreign Key reference |
| ethnic_code_concept_id | int | DEFAULT NULL | 'Reference to the ethnicity of the patient' | No Foreign Key reference |
| registered_practice_organization_id | bigint | DEFAULT NULL | 'Reference to the organisation the patient is registered at' | No Foreign Key reference |
| birth_year | smallint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| birth_month | tinyint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| birth_week | tinyint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `patient_id` Index Columns: (id) | ||||
| Index name: `patient_person_id` Index Columns: (person_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
patient_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'same as the id column on the patient table ' | No Foreign Key reference |
| property_id | int | NOT NULL | 'IM reference (e.g. Cause of death)' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'IM reference (e.g. COVID)' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'where there is no mapped value_id, just raw JSON' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' | No Foreign Key reference |
| Index name: `ix_patient_additional_id` Index Columns: (value_id) | ||||
| Index name: `ix_patient_additional_text_value` Index Columns: (text_value) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
patient_address
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the address' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| address_line_1 | varchar(255) | DEFAULT NULL | 'The first line of the address' | No Foreign Key reference |
| address_line_2 | varchar(255) | DEFAULT NULL | 'The second line of the address' | No Foreign Key reference |
| address_line_3 | varchar(255) | DEFAULT NULL | 'The third line of the address' | No Foreign Key reference |
| address_line_4 | varchar(255) | DEFAULT NULL | 'The fourth line of the address' | No Foreign Key reference |
| city | varchar(255) | DEFAULT NULL | 'The city' | No Foreign Key reference |
| postcode | varchar(255) | DEFAULT NULL | 'The postcode' | No Foreign Key reference |
| use_concept_id | int | NOT NULL | 'use of address (e.g. home, temporary)' | No Foreign Key reference |
| start_date | date | DEFAULT NULL | 'The start date of this address being relevant' | No Foreign Key reference |
| end_date | date | DEFAULT NULL | 'The end date of this address being relevant' | No Foreign Key reference |
| lsoa_2001_code | varchar(9) | DEFAULT NULL | 'A reference to the LSOA_2001 code' | No Foreign Key reference |
| lsoa_2011_code | varchar(9) | DEFAULT NULL | 'A reference to the LSOA_2011 code' | No Foreign Key reference |
| msoa_2001_code | varchar(9) | DEFAULT NULL | 'A reference to the MSOA_2001 code' | No Foreign Key reference |
| msoa_2011_code | varchar(9) | DEFAULT NULL | 'A reference to the MSOA_2011 code' | No Foreign Key reference |
| ward_code | varchar(9) | DEFAULT NULL | 'The ward the address belongs to' | No Foreign Key reference |
| local_authority_code | varchar(9) | DEFAULT NULL | 'The local authority the address belongs to' | No Foreign Key reference |
| townsend_deprivation_index | double | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `ux_patient_address_id` Index Columns: (id) | ||||
| Index name: `fk_patient_address_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`) | ||||
patient_address_match
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | No comment yet added | No Foreign Key reference |
| patient_address_id | bigint | NOT NULL, | No comment yet added | patient_address.id |
| uprn | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| uprn_ralf00 | varchar(255) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| status | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| uprn_property_classification | varchar(45) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| latitude | double | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| longitude | double | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| uprn_xcoordinate | double | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| uprn_ycoordinate | double | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| qualifier | varchar(50) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_rule | varchar(4096) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_date | datetime | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| abp_address_number | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| abp_address_street | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| abp_address_locality | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| abp_address_town | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| abp_address_postcode | varchar(10) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| abp_address_organization | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_pattern_postcode | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_pattern_street | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_pattern_number | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_pattern_building | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| match_pattern_flat | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| algorithm_version | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| epoch | varchar(255) | CHARACTER SET | No comment yet added | No Foreign Key reference |
| Index name: `patient_address_uprn_index` Index Columns: (uprn) | ||||
| Index name: `patient_address_patient_address_id` Index Columns: (id,uprn) | ||||
| Index name: `patient_address_uprn_patient_address_id_fk` Index Columns: (patient_address_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
patient_address_ralf
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL, | No comment yet added | No Foreign Key reference |
| organization_id | bigint | NOT NULL, | No comment yet added | organization.id |
| patient_id | bigint | NOT NULL, | No comment yet added | patient.id |
| person_id | bigint | NOT NULL, | No comment yet added | person.id |
| patient_address_id | bigint | NOT NULL, | No comment yet added | patient_address.id |
| patient_address_match_uprn_ralf00 | varchar(255) | NOT NULL, | No comment yet added | No Foreign Key reference |
| salt_name | varchar(50) | NOT NULL, | No comment yet added | No Foreign Key reference |
| ralf | varchar(255) | NOT NULL, | No comment yet added | No Foreign Key reference |
| Index name: `fk_patient_address_ralf_organization_id` Index Columns: (organization_id) | ||||
| Index name: `fk_patient_address_ralf_person_id` Index Columns: (person_id) | ||||
| Index name: `ux_patient_address_ralf_id` Index Columns: (id) | ||||
| Index name: `patient_address_ralf_patient_id` Index Columns: (patient_id) | ||||
| Index name: `patient_address_ralf_patient_address_id` Index Columns: (patient_address_id) | ||||
| Index name: `patient_address_ralf_patient_address_match_uprn_ralf_00` Index Columns: (patient_address_match_uprn_ralf00) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`patient_address_id`,`patient_address_match_uprn_ralf00`) | ||||
patient_contact
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the patient contact' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| use_concept_id | int | DEFAULT NULL | 'use of contact (e.g. mobile, home,work' | No Foreign Key reference |
| type_concept_id | int | DEFAULT NULL | 'type of contact (e.g. phone, email)' | No Foreign Key reference |
| start_date | date | DEFAULT NULL | 'The start date of the contact being valid' | No Foreign Key reference |
| end_date | date | DEFAULT NULL | 'The end date of the contact being valid' | No Foreign Key reference |
| value | varchar(255) | DEFAULT NULL | 'The value of the contact information eg phone number, email address' | No Foreign Key reference |
| Unique index name: `ux_patient_contact_id` Index Columns: (id) | ||||
| Index name: `fk_patient_contact_patient_id_organisation_id` Index Columns: (patient_id,organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`) | ||||
patient_pseudo_id
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the patient pseudo id' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'Reference to the patient this registration status history belongs to' | patient.id |
| person_id | bigint | NOT NULL | 'Reference to the person this registration status history belongs to' | No Foreign Key reference |
| salt_name | varchar(50) | NOT NULL | 'The name of the salt used to create the pseudo id' | No Foreign Key reference |
| skid | varchar(255) | NOT NULL | '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)' | No Foreign Key reference |
| is_nhs_number_valid | tinyint(1) | NOT NULL | 'Whether the nhs number is valid' | No Foreign Key reference |
| is_nhs_number_verified_by_publisher | tinyint(1) | NOT NULL | 'Whether the nhs number has been verified by the publisher' | No Foreign Key reference |
| Unique index name: `ux_patient_pseudo_id` Index Columns: (id) | ||||
| Index name: `fk_patient_pseudo_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `patient_pseudo_id_patient` Index Columns: (patient_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
patient_uprn
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| uprn | bigint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| qualifier | varchar(50) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| algorithm | varchar(255) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| match | varchar(255) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| no_address | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| invalid_address | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| missing_postcode | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| invalid_postcode | tinyint(1) | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `patient_uprn_id` Index Columns: (patient_id) | ||||
| Index name: `fk_patient_uprn_patient_id_organisation_id` Index Columns: (patient_id,organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`) | ||||
person
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the person' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | No Foreign Key reference |
| title | varchar(255) | DEFAULT NULL | 'The title of the person' | No Foreign Key reference |
| first_names | varchar(255) | DEFAULT NULL | 'The first names of the person' | No Foreign Key reference |
| last_name | varchar(255) | DEFAULT NULL | 'The last name of the person' | No Foreign Key reference |
| gender_concept_id | int | DEFAULT NULL | 'Reference to the gender of the person' | No Foreign Key reference |
| nhs_number | varchar(255) | DEFAULT NULL | 'The NHS number of the person' | No Foreign Key reference |
| date_of_birth | date | DEFAULT NULL | 'The date of birth of the person' | No Foreign Key reference |
| date_of_death | date | DEFAULT NULL | 'The date of death of the person' | No Foreign Key reference |
| current_address_id | bigint | DEFAULT NULL | 'Reference to the current address of the person' | No Foreign Key reference |
| ethnic_code_concept_id | int | DEFAULT NULL | 'Reference to the ethnicity of the person' | No Foreign Key reference |
| registered_practice_organization_id | bigint | DEFAULT NULL | 'Reference to the organisation the person is registered at' | No Foreign Key reference |
| birth_year | smallint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| birth_month | tinyint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| birth_week | tinyint | DEFAULT NULL, | No comment yet added | No Foreign Key reference |
| Unique index name: `person_id` Index Columns: (id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
practitioner
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the practitioner' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | organization.id |
| name | varchar(1024) | DEFAULT NULL | 'Name of the practitioner' | No Foreign Key reference |
| role_code | varchar(50) | DEFAULT NULL | 'The code representing the role of the practitioner' | No Foreign Key reference |
| role_desc | varchar(255) | DEFAULT NULL | 'Textual description of the role of the practitioner eg General Medical Practitioner' | No Foreign Key reference |
| gmc_code | varchar(50) | DEFAULT NULL | 'The GMC code of the practitioner' | No Foreign Key reference |
| Unique index name: `practitioner_id` Index Columns: (id) | ||||
| Index name: `fk_practitioner_organisation_id` Index Columns: (organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`) | ||||
procedure_request
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the procedure' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter the procedure was administered at' | No Foreign Key reference |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the procedure was administered by a clinician' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| status_concept_id | int | DEFAULT NULL | 'Reference to the status of the procedure' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the procedure' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the procedure' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age of the patient at the time of the procedure' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the procedure was recorded in the source system' | No Foreign Key reference |
| Unique index name: `procedure_request_id` Index Columns: (id) | ||||
| Index name: `fk_procedure_request_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_procedure_request_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `procedure_request_patient_id` Index Columns: (patient_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
referral_request
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the referral' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | patient.organization_id |
| patient_id | bigint | NOT NULL | 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | patient.id |
| person_id | bigint | NOT NULL | 'Unique individual across all organisations' | No Foreign Key reference |
| encounter_id | bigint | DEFAULT NULL | 'Reference to the encounter the referral was made in' | No Foreign Key reference |
| practitioner_id | bigint | DEFAULT NULL | 'The clinician the activity is recorded against' | practitioner.id |
| clinical_effective_date | datetime | DEFAULT NULL | 'The date the referral was made' | No Foreign Key reference |
| date_precision_concept_id | int | DEFAULT NULL | 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | No Foreign Key reference |
| requester_organization_id | bigint | DEFAULT NULL | 'Reference to the organisation that made the refereral request' | organization.id |
| recipient_organization_id | bigint | DEFAULT NULL | 'Reference to the organization receiving the referral' | organization.id |
| referral_request_priority_concept_id | int | DEFAULT NULL | 'Reference to the priority of the referral' | No Foreign Key reference |
| referral_request_type_concept_id | int | DEFAULT NULL | 'Reference to the type of referral request' | No Foreign Key reference |
| mode | varchar(50) | DEFAULT NULL | 'The mode of the referral' | No Foreign Key reference |
| outgoing_referral | tinyint(1) | DEFAULT NULL | 'Whether this is an outgoing referral' | No Foreign Key reference |
| is_review | tinyint(1) | NOT NULL | 'Whether this referral is a review' | No Foreign Key reference |
| core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the referral' | No Foreign Key reference |
| non_core_concept_id | int | DEFAULT NULL | 'Reference to the clinical coding of the referral' | No Foreign Key reference |
| age_at_event | decimal(5,2) | DEFAULT NULL | 'The age of the patient at the time of the referral' | No Foreign Key reference |
| date_recorded | datetime | DEFAULT NULL | 'The date the referral request was added to the source system' | No Foreign Key reference |
| Unique index name: `referral_request_id` Index Columns: (id) | ||||
| Index name: `fk_referral_request_patient_id_organization_id` Index Columns: (patient_id,organization_id) | ||||
| Index name: `fk_referral_request_practitioner_id` Index Columns: (practitioner_id) | ||||
| Index name: `fk_referral_request_recipient_organization_id` Index Columns: (recipient_organization_id) | ||||
| Index name: `fk_referral_request_requester_organization_id` Index Columns: (requester_organization_id) | ||||
| Index name: `referral_request_patient_id` Index Columns: (patient_id) | ||||
| Index name: `referral_request_core_concept_id` Index Columns: (core_concept_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`) | ||||
referral_request_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'the id of the referral ' | No Foreign Key reference |
| property_id | int | NOT NULL | 'concept id for the e.g. referral service offered ' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'currently unused ' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'currently unused ' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'text name of the referral service offered, e.g. Ealing North MINT ' | No Foreign Key reference |
| Index name: `ix_referral_request_additional_id` Index Columns: (value_id) | ||||
| Index name: `ix_referral_request_additional_text_value` Index Columns: (text_value) | ||||
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
registration_status_history
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the registration status history' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | No Foreign Key reference |
| patient_id | bigint | NOT NULL | 'Reference to the patient this registration status history belongs to' | No Foreign Key reference |
| person_id | bigint | NOT NULL | 'Reference to the person this registration status history belongs to' | No Foreign Key reference |
| episode_of_care_id | bigint | DEFAULT NULL | 'Reference to the episode of care this status history belongs to' | episode_of_care.id |
| registration_status_concept_id | int | DEFAULT NULL | 'Reference to the registration status' | No Foreign Key reference |
| start_date | datetime | DEFAULT NULL | 'The start date for the period this registration status history was valid' | No Foreign Key reference |
| end_date | datetime | DEFAULT NULL | 'The end date for the period this registration status history was valid' | No Foreign Key reference |
| Unique index name: `ux_registration_status_history_id` Index Columns: (id) | ||||
| Index name: `fk_registration_status_history_episode_id` Index Columns: (episode_of_care_id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`) | ||||
schedule
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'Unique Id of the schedule' | No Foreign Key reference |
| organization_id | bigint | NOT NULL | 'Owning organisation (i.e. publisher)' | organization.id |
| practitioner_id | bigint | DEFAULT NULL | 'Reference to the practitioner who owns the schedule' | No Foreign Key reference |
| start_date | datetime | DEFAULT NULL | 'The start date of the schedule' | No Foreign Key reference |
| type | varchar(255) | DEFAULT NULL | 'The type of schedule eg Timed Appointments' | No Foreign Key reference |
| location | varchar(255) | DEFAULT NULL | 'Textual description of the location the schedule was held at' | No Foreign Key reference |
| name | varchar(150) | DEFAULT NULL | 'The name of the schedule' | No Foreign Key reference |
| Unique index name: `schedule_id` Index Columns: (id) | ||||
| PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`) | ||||
schedule_additional
| Column Name | Data Type | Constraint | Comment | Foreign Key Reference |
|---|---|---|---|---|
| id | bigint | NOT NULL | 'the location id of the appointment ' | No Foreign Key reference |
| property_id | int | NOT NULL | 'concept id for the locationType' | No Foreign Key reference |
| value_id | int | DEFAULT NULL | 'unused' | No Foreign Key reference |
| json_value | json | DEFAULT NULL | 'unused' | No Foreign Key reference |
| text_value | varchar(255) | DEFAULT NULL | 'text representation of the schedule location type' | No Foreign Key reference |
| PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`) | ||||
DATABASE UPDATE INFO
| New Version | Date Updated |
|---|---|
| 2.3 | 2024-05-16T10:32:53.012 |