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