CompassV2TestPages:v2.X

From Discovery Data Service
Revision as of 13:28, 23 November 2022 by DavidHesketh (talk | contribs)
Jump to navigation Jump to search

Remote Subscriber Database (RSD) Schema (Compass 2) Version: v2.1.2

From Discovery Data Service

allergy_intolerance

<tbody> </tbody>
Column Name Type Constraint Comment
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this allergy was record in',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',
id bigint NOT NULL COMMENT 'Unique Id of the allergy',
date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

appointment

<tbody> </tbody>
Column Name Type Constraint Comment
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
appointment_status_concept_id int DEFAULT NULL COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',
patient_wait int DEFAULT NULL COMMENT 'How long the patient waited from being marked as arrived to being sent in',
date_time_sent_in datetime DEFAULT NULL COMMENT 'Date and time the patient was sent into the practitioner',
id bigint NOT NULL COMMENT 'Unique Id of the appointment',
source_id varchar(36) DEFAULT NULL COMMENT 'Unique reference to the source of the appointment',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
actual_duration int DEFAULT NULL COMMENT 'Time between sent in and left always in minutes',
planned_duration int DEFAULT NULL COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',
start_date date DEFAULT NULL COMMENT 'The start date of the appointment',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
patient_delay int DEFAULT NULL COMMENT 'How long the patient was delayed for',
date_time_left datetime DEFAULT NULL COMMENT 'Date and time the patient left the practitioner',
schedule_id bigint DEFAULT NULL COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule',
cancelled_date datetime DEFAULT NULL COMMENT 'The date the appointment was cancelled',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

appointment_additional

<tbody> </tbody>
Column Name Type Constraint Comment
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',
id bigint NOT NULL COMMENT 'same as the id column on the patient table ',
text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',
property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)',
value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)',
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),

concept

<tbody> </tbody>
Column Name Type Constraint Comment
Code varchar(40) DEFAULT NULL COMMENT 'The code (non-unique unless coupled with a scheme)',
Id varchar(150) NOT NULL COMMENT 'Unique concept identifier',
dbid int NOT NULL COMMENT 'Unique Id of the concept',
Document int NOT NULL COMMENT 'Concept grouping construct, deprecated',
Scheme bigint DEFAULT NULL COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',
Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',
Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)',
use_count bigint NOT NULL DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept',
updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept',
Name varchar(255) DEFAULT NULL COMMENT 'Short name',
PRIMARY KEY CONSTRAINT(s) `dbid`),

concept_map

<tbody> </tbody>
Column Name Type Constraint Comment
legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',
updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added',
core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',
id int NOT NULL,
deleted tinyint(1) DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `id`),

diagnostic_order

<tbody> </tbody>
Column Name Type Constraint Comment
problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',
is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the diagnostic order is a primary order',
result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',
episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician',
result_text text COMMENT 'Any text associated with the result',
id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order',
result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',
result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',
result_date date DEFAULT NULL COMMENT 'The date of the result',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
date_recorded datetime DEFAULT NULL,
is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
parent_observation_id bigint DEFAULT NULL COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

encounter

<tbody> </tbody>
Column Name Type Constraint Comment
appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',
sub_type text COMMENT 'Unused',
institution_location_id text COMMENT 'Reference to the institution the encounter took place at',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',
date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',
admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',
episode_of_care_id bigint DEFAULT NULL COMMENT 'Is this instance of the code a review of a previous encounter',
id bigint NOT NULL COMMENT 'Unique Id of the encounter',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was when this encounter took place',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',
service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',
end_date date DEFAULT NULL COMMENT 'The end date of the encounter',
Type text COMMENT 'Unused',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

encounter_additional

<tbody> </tbody>
Column Name Type Constraint Comment
text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)',
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',
id bigint NOT NULL COMMENT 'same as the id column on the encounter table',
value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)',
property_id int NOT NULL COMMENT 'IM concept id reference (i.e. Admission method)',
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),

encounter_event

<tbody> </tbody>
Column Name Type Constraint Comment
appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',
id bigint NOT NULL COMMENT 'Unique Id of the encounter event',
sub_type text COMMENT 'Unused',
episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this encounter belongs to',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',
clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',
date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',
admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',
institution_location_id bigint DEFAULT NULL COMMENT 'Reference to the institution the encounter took place at',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the encounter',
type text COMMENT 'Unused',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
person_id bigint NOT NULL COMMENT 'The person this event belongs to',
service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',
end_date date DEFAULT NULL COMMENT 'The end date of the encounter',
encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record',
patient_id bigint NOT NULL COMMENT 'The patient this event belongs to',
finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

episode_of_care

<tbody> </tbody>
Column Name Type Constraint Comment
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
usual_gp_practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the usual GP for this episode of care',
registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient',
date_registered_end date DEFAULT NULL COMMENT 'The date the registration was ended  for this episode of care',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
date_registered date DEFAULT NULL COMMENT 'The date the registration was started  for this episode of care',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
id bigint NOT NULL COMMENT 'Unique Id of the episode of care',
registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

event_log

<tbody> </tbody>
Column Name Type Constraint Comment
table_id tinyint NOT NULL COMMENT 'identifier of the table changed',
dt_change datetime(3) NOT NULL COMMENT 'date time the change was made to this DB',
change_type tinyint NOT NULL COMMENT 'type of transaction 0=insert, 1=update, 2=delete',
record_id bigint NOT NULL COMMENT 'id of the record changed'

flag

<tbody> </tbody>
Column Name Type Constraint Comment
flag_text text COMMENT 'This is a warning set by the publisher regarding he patient',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
id bigint NOT NULL COMMENT 'Unique Id of the flag',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

location

<tbody> </tbody>
Column Name Type Constraint Comment
type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of location eg GP Practice',
managing_organization_id bigint DEFAULT NULL COMMENT 'Reference to the managing organisation of the location',
name varchar(255) DEFAULT NULL COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',
postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the location',
type_code varchar(50) DEFAULT NULL COMMENT 'The type of location',
id bigint NOT NULL COMMENT 'Unique Id of the location',
PRIMARY KEY CONSTRAINT(s) `id`),

medication_order

<tbody> </tbody>
Column Name Type Constraint Comment
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',
duration_days int DEFAULT NULL COMMENT 'How many days the medication is prescribed for',
bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',
issue_method text COMMENT 'The issue method of the medication eg hand written',
dose varchar(1000) DEFAULT NULL COMMENT 'Textual description of the dose',
estimated_cost double DEFAULT NULL COMMENT 'The estimated cost of the medication',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
medication_statement_id bigint DEFAULT NULL COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders',
id bigint NOT NULL COMMENT 'Unique Id of the medication order',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',
quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication order was issued',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the medication order was issued in',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',
quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

medication_statement

<tbody> </tbody>
Column Name Type Constraint Comment
cancellation_date date DEFAULT NULL COMMENT 'The date the medication was cancelled',
Issue_method text COMMENT 'The issue method of the medication eg hand written',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
date_recorded datetime DEFAULT NULL,
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',
bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',
dose varchar(1000) DEFAULT NULL COMMENT 'Texual description of the dose of the medication',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
authorisation_type_concept_id int DEFAULT NULL COMMENT 'Reference to the authorisation type',
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)',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',
id bigint NOT NULL COMMENT 'Unique Id of the medication',
is_active tinyint(1) DEFAULT NULL COMMENT 'Whether the medication is active or not',
quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this medication was recorded in',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',
quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

observation

<tbody> </tbody>
Column Name Type Constraint Comment
problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',
is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation',
episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',
result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',
result_text text COMMENT 'Any text associated with the result',
result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',
result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',
result_date date DEFAULT NULL COMMENT 'The date of the result',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
date_recorded datetime DEFAULT NULL COMMENT 'The date the observation was recorded in the system',
is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
parent_observation_id bigint DEFAULT NULL COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
id bigint NOT NULL COMMENT 'Unique Id of the observation',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

observation_additional

<tbody> </tbody>
Column Name Type Constraint Comment
property_id int NOT NULL COMMENT 'IM reference (i.e. significance)',
text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',
id bigint NOT NULL COMMENT 'same as the id column on the observation table',
json_value json DEFAULT NULL COMMENT 'the JSON data itself ',
value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)',
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),

organization

<tbody> </tbody>
Column Name Type Constraint Comment
id bigint NOT NULL COMMENT 'Unique Id of the organisation',
Name varchar(255) DEFAULT NULL COMMENT 'Name of the organisation',
type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice',
ods_code varchar(50) DEFAULT NULL COMMENT 'ODS Code of the organisation',
type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation',
Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation',
parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation',
PRIMARY KEY CONSTRAINT(s) `id`),

organization_metadata

<tbody> </tbody>
Column Name Type Constraint Comment
last_data_to_dds datetime DEFAULT NULL COMMENT 'date time data was last sent to DDS',
publishing_software varchar(50) NOT NULL COMMENT 'software name of publishing system, i.e. SystmOne',
id bigint NOT NULL COMMENT 'organisation ID, corresponds to same ID in the organizaton table',
last_data_cutoff datetime DEFAULT NULL COMMENT 'cutoff date time of the last extract from the publishing system',
PRIMARY KEY CONSTRAINT(s) `id`,`publishing_software`)

patient

<tbody> </tbody>
Column Name Type Constraint Comment
date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient',
current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient',
date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the patient',
title varchar(255) DEFAULT NULL COMMENT 'The title of the patient',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the patient',
gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the patient',
birth_week tinyint DEFAULT NULL,
first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient',
last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient',
id bigint NOT NULL COMMENT 'Unique Id of the patient',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
birth_year smallint DEFAULT NULL,
ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient',
birth_month tinyint DEFAULT NULL,
registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

patient_additional

<tbody> </tbody>
Column Name Type Constraint Comment
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',
id bigint NOT NULL COMMENT 'same as the id column on the patient table ',
property_id int NOT NULL COMMENT 'IM reference (e.g. Cause of death)',
value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)',
text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),

patient_address

<tbody> </tbody>
Column Name Type Constraint Comment
address_line_2 varchar(255) DEFAULT NULL COMMENT 'The second line of the address',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
lsoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2001 code',
city varchar(255) DEFAULT NULL COMMENT 'The city',
address_line_4 varchar(255) DEFAULT NULL COMMENT 'The fourth line of the address',
msoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2001 code',
postcode varchar(255) DEFAULT NULL COMMENT 'The postcode',
Id bigint NOT NULL COMMENT 'Unique Id of the address',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
address_line_3 varchar(255) DEFAULT NULL COMMENT 'The third line of the address',
townsend_deprivation_index double DEFAULT NULL,
msoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2011 code',
start_date date NOT NULL COMMENT 'The start date of this address being relevant',
end_date date DEFAULT NULL COMMENT 'The end date of this address being relevant',
ward_code varchar(9) DEFAULT NULL COMMENT 'The ward the address belongs to',
use_concept_id int NOT NULL COMMENT 'use of address (e.g. home, temporary)',
address_line_1 varchar(255) DEFAULT NULL COMMENT 'The first line of the address',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
lsoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2011 code',
local_authority_code varchar(9) DEFAULT NULL COMMENT 'The local authority the address belongs to',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`Id`,`patient_id`,`person_id`),

patient_address_match

<tbody> </tbody>
Column Name Type Constraint Comment
latitude double DEFAULT NULL,
match_pattern_street varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
abp_address_postcode varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
abp_address_organization varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
abp_address_street varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
longitude double DEFAULT NULL,
match_date datetime DEFAULT NULL,
match_pattern_postcode varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
patient_address_id bigint NOT NULL,
qualifier varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
algorithm_version varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
uprn_xcoordinate double DEFAULT NULL,
uprn_ycoordinate double DEFAULT NULL,
match_pattern_building varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
uprn_property_classification varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
status tinyint(1) DEFAULT NULL,
abp_address_locality varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
uprn varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
id bigint NOT NULL AUTO_INCREMENT,
match_pattern_number varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
match_rule varchar(4096) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
uprn_ralf00 varchar(255) DEFAULT NULL,
abp_address_town varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
epoch varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
match_pattern_flat varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
abp_address_number varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `id`),

patient_address_ralf

<tbody> </tbody>
Column Name Type Constraint Comment
organization_id bigint NOT NULL,
patient_address_match_uprn_ralf00 varchar(255) NOT NULL,
person_id bigint NOT NULL,
ralf varchar(255) NOT NULL,
patient_id bigint NOT NULL,
patient_address_id bigint NOT NULL,
salt_name varchar(50) NOT NULL,
id bigint NOT NULL,
PRIMARY KEY CONSTRAINT(s) `id`,`patient_address_id`,`patient_address_match_uprn_ralf00`),

patient_contact

<tbody> </tbody>
Column Name Type Constraint Comment
value varchar(255) DEFAULT NULL COMMENT 'The value of the contact information eg phone number, email address',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
end_date date DEFAULT NULL COMMENT 'The end date of the contact being valid',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
id bigint NOT NULL COMMENT 'Unique Id of the patient contact',
use_concept_id int DEFAULT NULL COMMENT 'use of contact (e.g. mobile, home,work',
type_concept_id int DEFAULT NULL COMMENT 'type of contact (e.g. phone, email)',
start_date date DEFAULT NULL COMMENT 'The start date of the contact being valid',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`),

patient_pseudo_id

<tbody> </tbody>
Column Name Type Constraint Comment
person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',
is_nhs_number_verified_by_publisher tinyint(1) NOT NULL COMMENT 'Whether the nhs number has been verified by the publisher',
id bigint NOT NULL COMMENT 'Unique Id of the patient pseudo id',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
Skid varchar(255) NOT NULL COMMENT '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)',
patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',
salt_name varchar(50) NOT NULL COMMENT 'The name of the salt used to create the pseudo id',
is_nhs_number_valid tinyint(1) NOT NULL COMMENT 'Whether the nhs number is valid',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

patient_uprn

<tbody> </tbody>
Column Name Type Constraint Comment
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
match varchar(255) DEFAULT NULL,
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
algorithm varchar(255) DEFAULT NULL,
no_address tinyint(1) DEFAULT NULL,
uprn bigint DEFAULT NULL,
qualifier varchar(50) DEFAULT NULL,
missing_postcode tinyint(1) DEFAULT NULL,
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
invalid_address tinyint(1) DEFAULT NULL,
invalid_postcode tinyint(1) DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`patient_id`),

person

<tbody> </tbody>
Column Name Type Constraint Comment
last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person',
title varchar(255) DEFAULT NULL COMMENT 'The title of the person',
date_of_death date DEFAULT NULL COMMENT 'The date of death of the person',
registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the person is registered at',
date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person',
birth_week tinyint DEFAULT NULL,
nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the person',
birth_year smallint DEFAULT NULL,
ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the person',
id bigint NOT NULL COMMENT 'Unique Id of the person',
current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',
gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person',
birth_month tinyint DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `id`),

practitioner

<tbody> </tbody>
Column Name Type Constraint Comment
id bigint NOT NULL COMMENT 'Unique Id of the practitioner',
name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner',
role_code varchar(50) DEFAULT NULL COMMENT 'The code representing the role of the practitioner',
role_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',
PRIMARY KEY CONSTRAINT(s) `id`),

procedure_request

<tbody> </tbody>
Column Name Type Constraint Comment
clinical_effective_date date DEFAULT NULL COMMENT 'The date the procedure was administered by a clinician',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
date_precision_concept_id int DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the procedure was administered at',
status_concept_id int DEFAULT NULL COMMENT 'Reference to the status of the procedure',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the procedure',
date_recorded datetime DEFAULT NULL COMMENT 'The date the procedure was recorded in the source system',
id bigint NOT NULL COMMENT 'Unique Id of the procedure',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

referral_request

<tbody> </tbody>
Column Name Type Constraint Comment
clinical_effective_date date DEFAULT NULL COMMENT 'The date the referral was made',
is_review tinyint(1) DEFAULT NULL COMMENT 'Whether this referral is a review',
patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
requester_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation that made the refereral request',
outgoing_referral tinyint(1) DEFAULT NULL COMMENT 'Whether this is an outgoing referral',
non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',
referral_request_type_concept_id int DEFAULT NULL COMMENT 'Reference to the type of referral request',
referral_request_priority_concept_id smallint DEFAULT NULL COMMENT 'Reference to the priority of the referral',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the referral was made in',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
Mode varchar(50) DEFAULT NULL COMMENT 'The mode of the referral',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
recipient_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organization receiving the referral',
date_recorded datetime DEFAULT NULL COMMENT 'The date the referral request was added to the source system',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the referral',
date_precision_concept_id smallint DEFAULT NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',
id bigint NOT NULL COMMENT 'Unique Id of the referral',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

registration_status_history

<tbody> </tbody>
Column Name Type Constraint Comment
person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',
start_date datetime DEFAULT NULL COMMENT 'The start date for the period this registration status history was valid',
id bigint NOT NULL COMMENT 'Unique Id of the registration status history',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status',
patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',
end_date datetime DEFAULT NULL COMMENT 'The end date for the period this registration status history was valid',
episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this status history belongs to',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`),

schedule

<tbody> </tbody>
Column Name Type Constraint Comment
id bigint NOT NULL COMMENT 'Unique Id of the schedule',
practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the practitioner who owns the schedule',
start_date date DEFAULT NULL COMMENT 'The start date of the schedule',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule',
type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments',
location varchar(255) DEFAULT NULL COMMENT 'Textual description of the location the schedule was held at',
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`),
Retrieved from ‘https://wiki.discoverydataservice.org/index.php?title=CompassV2TestPages:v2.X&oldid=6088’