CompassV2TestPages:v2.X
From Voror_Wiki
More actions
Remote Subscriber Database (RSD) Schema (Compass 2) Version: v2.1.2
allergy_intolerance
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter this allergy was record in', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the allergy', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the allergy', | |
| date_recorded | datetime | NOT NULL | COMMENT 'The date the allergy was recorded', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the allergy', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| is_review | tinyint(1) | NOT NULL | COMMENT 'Is this instance of the code a review of a previous encounter', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the clinical code is recorded for', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event', | |
| CONSTRAINT NAME `allergy_intolerance_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `allergy_intolerance_core_concept_id` CONSTRAINT COLUMNS core_concept_id) | ||||
appointment
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| appointment_status_concept_id | int | DEFAULT NULL | COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA', | |
| patient_wait | int | DEFAULT NULL | COMMENT 'How long the patient waited from being marked as arrived to being sent in', | |
| date_time_sent_in | datetime | DEFAULT NULL | COMMENT 'Date and time the patient was sent into the practitioner', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the appointment', | |
| source_id | varchar(36) | DEFAULT NULL | COMMENT 'Unique reference to the source of the appointment', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| actual_duration | int | DEFAULT NULL | COMMENT 'Time between sent in and left always in minutes', | |
| planned_duration | int | DEFAULT NULL | COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes', | |
| start_date | date | DEFAULT NULL | COMMENT 'The start date of the appointment', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| patient_delay | int | DEFAULT NULL | COMMENT 'How long the patient was delayed for', | |
| date_time_left | datetime | DEFAULT NULL | COMMENT 'Date and time the patient left the practitioner', | |
| schedule_id | bigint | DEFAULT NULL | COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule', | |
| cancelled_date | datetime | DEFAULT NULL | COMMENT 'The date the appointment was cancelled', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `appointment_patient_id` CONSTRAINT COLUMNS patient_id) | ||||
appointment_additional
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON', | |
| id | bigint | NOT NULL | COMMENT 'same as the id column on the patient table ', | |
| text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)', | |
| property_id | int | NOT NULL | COMMENT 'IM reference (e.g. appointment type)', | |
| value_id | int | DEFAULT NULL | COMMENT 'IM reference (e.g. Appointment Type)', | |
| CONSTRAINT NAME `appointment_additional_value_id` CONSTRAINT COLUMNS value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
concept
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| Code | varchar(40) | DEFAULT NULL | COMMENT 'The code (non-unique unless coupled with a scheme)', | |
| Id | varchar(150) | NOT NULL | COMMENT 'Unique concept identifier', | |
| dbid | int | NOT NULL | COMMENT 'Unique Id of the concept', | |
| Document | int | NOT NULL | COMMENT 'Concept grouping construct, deprecated', | |
| Scheme | bigint | DEFAULT NULL | COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)', | |
| Draft | tinyint(1) | NOT NULL | COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept', | |
| Description | varchar(400) | DEFAULT NULL | COMMENT 'Full name (or term for ontological concepts)', | |
| use_count | bigint | NOT NULL | DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept', | |
| updated | datetime | NOT NULL | COMMENT 'The timestamp of the last update to the concept', | |
| Name | varchar(255) | DEFAULT NULL | COMMENT 'Short name', | |
| CONSTRAINT NAME `ix_scheme_code` CONSTRAINT COLUMNS Scheme,Code), | ||||
| PRIMARY KEY CONSTRAINT(s) `dbid`), | ||||
| CONSTRAINT NAME `ix_dbid_code` CONSTRAINT COLUMNS dbid,Code) | ||||
| CONSTRAINT NAME `ix_code` CONSTRAINT COLUMNS Code), | ||||
concept_map
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| legacy | int | NOT NULL | COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept', | |
| updated | datetime | NOT NULL | COMMENT 'Timestamp the map was last updated/added', | |
| core | int | NOT NULL | COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to', | |
| id | int | NOT NULL, | ||
| deleted | tinyint(1) | DEFAULT NULL, | ||
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
| CONSTRAINT NAME `ix_legacy_core` CONSTRAINT COLUMNS legacy,core) | ||||
diagnostic_order
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| problem_end_date | date | DEFAULT NULL | COMMENT 'The end date of the problem', | |
| is_primary | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the diagnostic order is a primary order', | |
| result_value_units | varchar(50) | DEFAULT NULL | COMMENT 'The units of the result of the observation', | |
| episodicity_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the episodicity of the problem eg First, review, flare', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the diagnostic order was identified by a clinician', | |
| result_text | text | COMMENT 'Any | text associated with the result', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the diagnostic order', | |
| result_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the result', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| is_problem | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is marked as a problem', | |
| result_value | double | DEFAULT NULL | COMMENT 'The value of the result of the observation', | |
| result_date | date | DEFAULT NULL | COMMENT 'The date of the result', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| date_recorded | datetime | DEFAULT NULL, | ||
| is_review | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is a review of an existing problem', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| parent_observation_id | bigint | DEFAULT NULL | COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the observation', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the observation was recorded at', | |
| CONSTRAINT NAME `diagnostic_order_core_concept_id_result_value` CONSTRAINT COLUMNS core_concept_id,result_value), | ||||
| CONSTRAINT NAME `diagnostic_order_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| CONSTRAINT NAME `ix_diagnostic_order_person_id` CONSTRAINT COLUMNS person_id) | ||||
| CONSTRAINT NAME `ix_diagnostic_order_clinical_effective_date` CONSTRAINT COLUMNS clinical_effective_date), | ||||
| CONSTRAINT NAME `diagnostic_order_core_concept_id` CONSTRAINT COLUMNS core_concept_id), | ||||
| CONSTRAINT NAME `ix_diagnostic_order_organization_id` CONSTRAINT COLUMNS organization_id), | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `diagnostic_order_non_core_concept_id` CONSTRAINT COLUMNS non_core_concept_id), | ||||
| CONSTRAINT NAME `diagnostic_order_core_concept_id_is_problem` CONSTRAINT COLUMNS core_concept_id,is_problem), | ||||
encounter
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| institution_location_id | text | COMMENT 'Reference | to the institution the encounter took place at', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the encounter was recorded', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the precision of the date of the encounter', | |
| episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Is this instance of the code a review of a previous encounter', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the encounter', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was when this encounter took place', | |
| service_provider_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the service provider organisation', | |
| appointment_id | bigint | DEFAULT NULL | COMMENT 'Reference to the appointment this encounter took part on', | |
| sub_type | text | COMMENT 'Unused', | ||
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter', | |
| admission_method | varchar(40) | DEFAULT NULL | COMMENT 'The admission method of the encounter', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the clinical code is recorded for', | |
| end_date | date | DEFAULT NULL | COMMENT 'The end date of the encounter', | |
| Type | text | COMMENT 'Unused', | ||
| CONSTRAINT NAME `fki_encounter_appointment_id` CONSTRAINT COLUMNS appointment_id), | ||||
| CONSTRAINT NAME `fki_encounter_patient_id_organization_id` CONSTRAINT COLUMNS patient_id,organization_id), | ||||
| CONSTRAINT NAME `encounter_core_concept_id_clinical_effective_date` CONSTRAINT COLUMNS core_concept_id,clinical_effective_date) | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `encounter_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
encounter_additional
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)', | |
| json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)', | |
| id | bigint | NOT NULL | COMMENT 'same as the id column on the encounter table', | |
| value_id | int | DEFAULT NULL | COMMENT 'IM concept id reference (i.e. Emergency admission)', | |
| property_id | int | NOT NULL | COMMENT 'IM concept id reference (i.e. Admission method)', | |
| CONSTRAINT NAME `encounter_additional_value_id` CONSTRAINT COLUMNS value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
encounter_event
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| appointment_id | bigint | DEFAULT NULL | COMMENT 'Reference to the appointment this encounter took part on', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the encounter event', | |
| sub_type | text | COMMENT 'Unused', | ||
| episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Reference to the episode of care this encounter belongs to', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter', | |
| clinical_effective_date | datetime | DEFAULT NULL | COMMENT 'The date the encounter took place', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the encounter was recorded', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the precision of the date of the encounter', | |
| admission_method | varchar(40) | DEFAULT NULL | COMMENT 'The admission method of the encounter', | |
| institution_location_id | bigint | DEFAULT NULL | COMMENT 'Reference to the institution the encounter took place at', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the encounter', | |
| type | text | COMMENT 'Unused', | ||
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| person_id | bigint | NOT NULL | COMMENT 'The person this event belongs to', | |
| service_provider_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the service provider organisation', | |
| end_date | date | DEFAULT NULL | COMMENT 'The end date of the encounter', | |
| encounter_id | bigint | NOT NULL | COMMENT 'Reference to the parent encounter record', | |
| patient_id | bigint | NOT NULL | COMMENT 'The patient this event belongs to', | |
| finished | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the encounter is finished', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
episode_of_care
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| usual_gp_practitioner_id | bigint | DEFAULT NULL | COMMENT 'Reference to the usual GP for this episode of care', | |
| registration_status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration status of the patient', | |
| date_registered | date | DEFAULT NULL | COMMENT 'The date the registration was started for this episode of care', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| date_registered_end | date | DEFAULT NULL | COMMENT 'The date the registration was ended for this episode of care', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the episode of care', | |
| registration_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration type of the patient', | |
| CONSTRAINT NAME `episode_of_care_person_id` CONSTRAINT COLUMNS person_id), | ||||
| CONSTRAINT NAME `episode_of_care_date_registered_end` CONSTRAINT COLUMNS date_registered_end), | ||||
| CONSTRAINT NAME `episode_of_care_registration_type_concept_id` CONSTRAINT COLUMNS registration_type_concept_id), | ||||
| CONSTRAINT NAME `episode_of_care_date_registered` CONSTRAINT COLUMNS date_registered), | ||||
| CONSTRAINT NAME `episode_of_care_organization_id` CONSTRAINT COLUMNS organization_id) | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `episode_of_care_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
event_log
| Column Name | Type | Constraint | Comment |
|---|---|---|---|
| table_id | tinyint | NOT NULL | COMMENT 'identifier of the table changed', |
| dt_change | datetime(3) | NOT NULL | COMMENT 'date time the change was made to this DB', |
| change_type | tinyint | NOT NULL | COMMENT 'type of transaction 0=insert, 1=update, 2=delete', |
| record_id | bigint | NOT NULL | COMMENT 'id of the record changed' |
flag
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| flag_text | text | COMMENT 'This | is a warning set by the publisher regarding he patient', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| is_active | tinyint(1) | NOT NULL | COMMENT 'Whether the flag is active or not', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| effective_date | date | DEFAULT NULL | COMMENT 'The date the flag was entered onto the patients record', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the flag', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `flag_patient_id` CONSTRAINT COLUMNS patient_id) | ||||
location
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| type_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the type of location eg GP Practice', | |
| managing_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the managing organisation of the location', | |
| name | varchar(255) | DEFAULT NULL | COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary', | |
| postcode | varchar(10) | DEFAULT NULL | COMMENT 'The postcode of the location', | |
| type_code | varchar(50) | DEFAULT NULL | COMMENT 'The type of location', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the location', | |
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
| CONSTRAINT NAME `fk_location_managing_organisation_id` CONSTRAINT COLUMNS managing_organization_id) | ||||
medication_order
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication', | |
| duration_days | int | DEFAULT NULL | COMMENT 'How many days the medication is prescribed for', | |
| bnf_reference | varchar(6) | DEFAULT NULL | COMMENT 'A reference to the drug in the BNF dictionary', | |
| issue_method | text | COMMENT 'The | issue method of the medication eg hand written', | |
| dose | varchar(1000) | DEFAULT NULL | COMMENT 'Textual description of the dose', | |
| estimated_cost | double | DEFAULT NULL | COMMENT 'The estimated cost of the medication', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| medication_statement_id | bigint | DEFAULT NULL | COMMENT 'Reference to the medication statement. A medication statement can have many medication orders', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the medication order', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication', | |
| quantity_value | double | DEFAULT NULL | COMMENT 'The value of the medication that was prescribed eg 50', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the medication order was issued', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the medication order was issued in', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event', | |
| quantity_unit | varchar(255) | DEFAULT NULL | COMMENT 'The unit of the medication that was prescribed eg tablets', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `medication_order_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| CONSTRAINT NAME `medication_order_core_concept_id` CONSTRAINT COLUMNS core_concept_id) | ||||
medication_statement
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| bnf_reference | varchar(6) | DEFAULT NULL | COMMENT 'A reference to the drug in the BNF dictionary', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the medication was clinical relevant', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication', | |
| quantity_value | double | DEFAULT NULL | COMMENT 'The value of the medication that was prescribed eg 50', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter this medication was recorded in', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event', | |
| quantity_unit | varchar(255) | DEFAULT NULL | COMMENT 'The unit of the medication that was prescribed eg tablets', | |
| cancellation_date | date | DEFAULT NULL | COMMENT 'The date the medication was cancelled', | |
| Issue_method | text | COMMENT 'The | issue method of the medication eg hand written', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| date_recorded | datetime | DEFAULT NULL, | ||
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication', | |
| dose | varchar(1000) | DEFAULT NULL | COMMENT 'Texual description of the dose of the medication', | |
| authorisation_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the authorisation type', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT '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) | DEFAULT NULL | COMMENT 'Whether the medication is active or not', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the medication', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `medication_statement_dmd_id` CONSTRAINT COLUMNS patient_id) | ||||
| CONSTRAINT NAME `medication_statement_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
observation
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| problem_end_date | date | DEFAULT NULL | COMMENT 'The end date of the problem', | |
| is_primary | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the observation is a primary observation', | |
| episodicity_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the episodicity of the problem eg First, review, flare', | |
| result_value_units | varchar(50) | DEFAULT NULL | COMMENT 'The units of the result of the observation', | |
| result_text | text | COMMENT 'Any | text associated with the result', | |
| result_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the result', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| is_problem | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is marked as a problem', | |
| result_value | double | DEFAULT NULL | COMMENT 'The value of the result of the observation', | |
| result_date | date | DEFAULT NULL | COMMENT 'The date of the result', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the observation was recorded in the system', | |
| is_review | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is a review of an existing problem', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| parent_observation_id | bigint | DEFAULT NULL | COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the observation', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the observation', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the observation was recorded at', | |
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the observation was identified by a clinician', | |
| CONSTRAINT NAME `ix_observation_organization_id` CONSTRAINT COLUMNS organization_id), | ||||
| CONSTRAINT NAME `observation_core_concept_id_is_problem` CONSTRAINT COLUMNS core_concept_id,is_problem), | ||||
| CONSTRAINT NAME `ix_observation_person_id` CONSTRAINT COLUMNS person_id) | ||||
| CONSTRAINT NAME `observation_core_concept_id_result_value` CONSTRAINT COLUMNS core_concept_id,result_value), | ||||
| CONSTRAINT NAME `observation_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `ix_observation_clinical_effective_date` CONSTRAINT COLUMNS clinical_effective_date), | ||||
| CONSTRAINT NAME `observation_core_concept_id` CONSTRAINT COLUMNS core_concept_id), | ||||
| CONSTRAINT NAME `observation_non_core_concept_id` CONSTRAINT COLUMNS non_core_concept_id), | ||||
observation_additional
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| property_id | int | NOT NULL | COMMENT 'IM reference (i.e. significance)', | |
| text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value', | |
| id | bigint | NOT NULL | COMMENT 'same as the id column on the observation table', | |
| json_value | json | DEFAULT NULL | COMMENT 'the JSON data itself ', | |
| value_id | int | DEFAULT NULL | COMMENT 'IM reference (i.e. minor, significant)', | |
| CONSTRAINT NAME `observation_additional_value_id` CONSTRAINT COLUMNS value_id) | ||||
| PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
organization
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| id | bigint | NOT NULL | COMMENT 'Unique Id of the organisation', | |
| Name | varchar(255) | DEFAULT NULL | COMMENT 'Name of the organisation', | |
| type_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the type of organisation eg GP Practice', | |
| ods_code | varchar(50) | DEFAULT NULL | COMMENT 'ODS Code of the organisation', | |
| type_code | varchar(50) | DEFAULT NULL | COMMENT 'The type of organisation', | |
| Postcode | varchar(10) | DEFAULT NULL | COMMENT 'The postcode of the organisation', | |
| parent_organization_id | bigint | DEFAULT NULL | COMMENT 'The id of the parent organisation', | |
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
| CONSTRAINT NAME `fki_organization_parent_organization_id` CONSTRAINT COLUMNS parent_organization_id) | ||||
organization_metadata
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| last_data_to_dds | datetime | DEFAULT NULL | COMMENT 'date time data was last sent to DDS', | |
| publishing_software | varchar(50) | NOT NULL | COMMENT 'software name of publishing system, i.e. SystmOne', | |
| id | bigint | NOT NULL | COMMENT 'organisation ID, corresponds to same ID in the organizaton table', | |
| last_data_cutoff | datetime | DEFAULT NULL | COMMENT 'cutoff date time of the last extract from the publishing system', | |
| PRIMARY KEY CONSTRAINT(s) `id`,`publishing_software`) | ||||
patient
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| date_of_death | date | DEFAULT NULL | COMMENT 'The date of death of the patient', | |
| current_address_id | bigint | DEFAULT NULL | COMMENT 'Reference to the current address of the patient', | |
| date_of_birth | date | DEFAULT NULL | COMMENT 'The date of birth of the patient', | |
| title | varchar(255) | DEFAULT NULL | COMMENT 'The title of the patient', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| nhs_number | varchar(255) | DEFAULT NULL | COMMENT 'The NHS number of the patient', | |
| gender_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the gender of the patient', | |
| birth_week | tinyint | DEFAULT NULL, | ||
| first_names | varchar(255) | DEFAULT NULL | COMMENT 'The first names of the patient', | |
| last_name | varchar(255) | DEFAULT NULL | COMMENT 'The last name of the patient', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the patient', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| birth_year | smallint | DEFAULT NULL, | ||
| ethnic_code_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the ethnicity of the patient', | |
| birth_month | tinyint | DEFAULT NULL, | ||
| registered_practice_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation the patient is registered at', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `patient_person_id` CONSTRAINT COLUMNS person_id) | ||||
patient_additional
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON', | |
| id | bigint | NOT NULL | COMMENT 'same as the id column on the patient table ', | |
| property_id | int | NOT NULL | COMMENT 'IM reference (e.g. Cause of death)', | |
| value_id | int | DEFAULT NULL | COMMENT 'IM reference (e.g. COVID)', | |
| text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)', | |
| PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
| CONSTRAINT NAME `ix_patient_additional_id` CONSTRAINT COLUMNS value_id) | ||||
patient_address
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| address_line_2 | varchar(255) | DEFAULT NULL | COMMENT 'The second line of the address', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| lsoa_2001_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the LSOA_2001 code', | |
| city | varchar(255) | DEFAULT NULL | COMMENT 'The city', | |
| address_line_4 | varchar(255) | DEFAULT NULL | COMMENT 'The fourth line of the address', | |
| msoa_2001_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the MSOA_2001 code', | |
| postcode | varchar(255) | DEFAULT NULL | COMMENT 'The postcode', | |
| Id | bigint | NOT NULL | COMMENT 'Unique Id of the address', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| address_line_3 | varchar(255) | DEFAULT NULL | COMMENT 'The third line of the address', | |
| townsend_deprivation_index | double | DEFAULT NULL, | ||
| msoa_2011_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the MSOA_2011 code', | |
| start_date | date | NOT NULL | COMMENT 'The start date of this address being relevant', | |
| end_date | date | DEFAULT NULL | COMMENT 'The end date of this address being relevant', | |
| ward_code | varchar(9) | DEFAULT NULL | COMMENT 'The ward the address belongs to', | |
| use_concept_id | int | NOT NULL | COMMENT 'use of address (e.g. home, temporary)', | |
| address_line_1 | varchar(255) | DEFAULT NULL | COMMENT 'The first line of the address', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| lsoa_2011_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the LSOA_2011 code', | |
| local_authority_code | varchar(9) | DEFAULT NULL | COMMENT 'The local authority the address belongs to', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`Id`,`patient_id`,`person_id`), | ||||
patient_address_match
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| latitude | double | DEFAULT NULL, | ||
| match_pattern_street | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| abp_address_postcode | varchar(10) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| abp_address_organization | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| abp_address_street | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| longitude | double | DEFAULT NULL, | ||
| match_date | datetime | DEFAULT NULL, | ||
| match_pattern_postcode | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| patient_address_id | bigint | NOT NULL, | ||
| qualifier | varchar(50) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| algorithm_version | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| uprn_xcoordinate | double | DEFAULT NULL, | ||
| uprn_ycoordinate | double | DEFAULT NULL, | ||
| match_pattern_building | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| uprn_property_classification | varchar(45) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| status | tinyint(1) | DEFAULT NULL, | ||
| abp_address_locality | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| uprn | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, | |
| id | bigint | NOT NULL | AUTO_INCREMENT, | |
| match_pattern_number | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| match_rule | varchar(4096) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| uprn_ralf00 | varchar(255) | DEFAULT NULL, | ||
| abp_address_town | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| epoch | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, | |
| match_pattern_flat | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| abp_address_number | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, | |
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
| CONSTRAINT NAME `patient_address_patient_address_id` CONSTRAINT COLUMNS id,uprn) | ||||
| CONSTRAINT NAME `patient_address_uprn_index` CONSTRAINT COLUMNS uprn), | ||||
patient_address_ralf
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| organization_id | bigint | NOT NULL, | ||
| patient_id | bigint | NOT NULL, | ||
| id | bigint | NOT NULL, | ||
| patient_address_match_uprn_ralf00 | varchar(255) | NOT NULL, | ||
| ralf | varchar(255) | NOT NULL, | ||
| person_id | bigint | NOT NULL, | ||
| patient_address_id | bigint | NOT NULL, | ||
| salt_name | varchar(50) | NOT NULL, | ||
| CONSTRAINT NAME `patient_address_ralf_patient_address_match_uprn_ralf_00` CONSTRAINT COLUMNS patient_address_match_uprn_ralf00) | ||||
| CONSTRAINT NAME `ux_patient_address_ralf_id` CONSTRAINT COLUMNS id), | ||||
| CONSTRAINT NAME `patient_address_ralf_patient_address_id` CONSTRAINT COLUMNS patient_address_id), | ||||
| CONSTRAINT NAME `patient_address_ralf_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| PRIMARY KEY CONSTRAINT(s) `id`,`patient_address_id`,`patient_address_match_uprn_ralf00`), | ||||
patient_contact
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| value | varchar(255) | DEFAULT NULL | COMMENT 'The value of the contact information eg phone number, email address', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| end_date | date | DEFAULT NULL | COMMENT 'The end date of the contact being valid', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the patient contact', | |
| use_concept_id | int | DEFAULT NULL | COMMENT 'use of contact (e.g. mobile, home,work', | |
| type_concept_id | int | DEFAULT NULL | COMMENT 'type of contact (e.g. phone, email)', | |
| start_date | date | DEFAULT NULL | COMMENT 'The start date of the contact being valid', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`), | ||||
patient_pseudo_id
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| person_id | bigint | NOT NULL | COMMENT 'Reference to the person this registration status history belongs to', | |
| is_nhs_number_verified_by_publisher | tinyint(1) | NOT NULL | COMMENT 'Whether the nhs number has been verified by the publisher', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the patient pseudo id', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| Skid | varchar(255) | NOT NULL | COMMENT '"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)', | |
| patient_id | bigint | NOT NULL | COMMENT 'Reference to the patient this registration status history belongs to', | |
| salt_name | varchar(50) | NOT NULL | COMMENT 'The name of the salt used to create the pseudo id', | |
| is_nhs_number_valid | tinyint(1) | NOT NULL | COMMENT 'Whether the nhs number is valid', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `patient_pseudo_id_patient` CONSTRAINT COLUMNS patient_id) | ||||
patient_uprn
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| match | varchar(255) | DEFAULT NULL, | ||
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| algorithm | varchar(255) | DEFAULT NULL, | ||
| no_address | tinyint(1) | DEFAULT NULL, | ||
| uprn | bigint | DEFAULT NULL, | ||
| qualifier | varchar(50) | DEFAULT NULL, | ||
| missing_postcode | tinyint(1) | DEFAULT NULL, | ||
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| invalid_address | tinyint(1) | DEFAULT NULL, | ||
| invalid_postcode | tinyint(1) | DEFAULT NULL, | ||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`patient_id`), | ||||
person
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| last_name | varchar(255) | DEFAULT NULL | COMMENT 'The last name of the person', | |
| title | varchar(255) | DEFAULT NULL | COMMENT 'The title of the person', | |
| date_of_death | date | DEFAULT NULL | COMMENT 'The date of death of the person', | |
| registered_practice_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation the person is registered at', | |
| date_of_birth | date | DEFAULT NULL | COMMENT 'The date of birth of the person', | |
| birth_week | tinyint | DEFAULT NULL, | ||
| nhs_number | varchar(255) | DEFAULT NULL | COMMENT 'The NHS number of the person', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| first_names | varchar(255) | DEFAULT NULL | COMMENT 'The first names of the person', | |
| birth_year | smallint | DEFAULT NULL, | ||
| ethnic_code_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the ethnicity of the person', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the person', | |
| current_address_id | bigint | NOT NULL | COMMENT 'Reference to the current address of the person', | |
| gender_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the gender of the person', | |
| birth_month | tinyint | DEFAULT NULL, | ||
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
practitioner
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| id | bigint | NOT NULL | COMMENT 'Unique Id of the practitioner', | |
| name | varchar(1024) | DEFAULT NULL | COMMENT 'Name of the practitioner', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| gmc_code | varchar(50) | DEFAULT NULL | COMMENT 'The GMC code of the practitioner', | |
| role_code | varchar(50) | DEFAULT NULL | COMMENT 'The code representing the role of the practitioner', | |
| role_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner', | |
| PRIMARY KEY CONSTRAINT(s) `id`), | ||||
procedure_request
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the procedure was administered by a clinician', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the procedure', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the procedure was administered at', | |
| status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the status of the procedure', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the procedure', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the procedure', | |
| date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the procedure was recorded in the source system', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the procedure', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
| CONSTRAINT NAME `procedure_request_patient_id` CONSTRAINT COLUMNS patient_id) | ||||
referral_request
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the referral was made', | |
| is_review | tinyint(1) | DEFAULT NULL | COMMENT 'Whether this referral is a review', | |
| patient_id | bigint | NOT NULL | COMMENT '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', | |
| requester_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation that made the refereral request', | |
| outgoing_referral | tinyint(1) | DEFAULT NULL | COMMENT 'Whether this is an outgoing referral', | |
| non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the referral', | |
| referral_request_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of referral request', | |
| referral_request_priority_concept_id | smallint | DEFAULT NULL | COMMENT 'Reference to the priority of the referral', | |
| encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the referral was made in', | |
| person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations', | |
| Mode | varchar(50) | DEFAULT NULL | COMMENT 'The mode of the referral', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against', | |
| recipient_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organization receiving the referral', | |
| date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the referral request was added to the source system', | |
| age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the referral', | |
| date_precision_concept_id | smallint | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the referral', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the referral', | |
| CONSTRAINT NAME `referral_request_patient_id` CONSTRAINT COLUMNS patient_id), | ||||
| CONSTRAINT NAME `referral_request_core_concept_id` CONSTRAINT COLUMNS core_concept_id) | ||||
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
registration_status_history
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| person_id | bigint | NOT NULL | COMMENT 'Reference to the person this registration status history belongs to', | |
| start_date | datetime | DEFAULT NULL | COMMENT 'The start date for the period this registration status history was valid', | |
| id | bigint | NOT NULL | COMMENT 'Unique Id of the registration status history', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| registration_status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration status', | |
| patient_id | bigint | NOT NULL | COMMENT 'Reference to the patient this registration status history belongs to', | |
| end_date | datetime | DEFAULT NULL | COMMENT 'The end date for the period this registration status history was valid', | |
| episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Reference to the episode of care this status history belongs to', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`), | ||||
schedule
| Column Name | Type | Constraint | Comment | |
|---|---|---|---|---|
| id | bigint | NOT NULL | COMMENT 'Unique Id of the schedule', | |
| practitioner_id | bigint | DEFAULT NULL | COMMENT 'Reference to the practitioner who owns the schedule', | |
| start_date | date | DEFAULT NULL | COMMENT 'The start date of the schedule', | |
| organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)', | |
| name | varchar(150) | DEFAULT NULL | COMMENT 'The name of the schedule', | |
| type | varchar(255) | DEFAULT NULL | COMMENT 'The type of schedule eg Timed Appointments', | |
| location | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the location the schedule was held at', | |
| PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`), | ||||