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