CompassV2TestPages:v2.X

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

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`),