CompassV2TestPages:v2.X

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

allergy_intolerance

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the allergy'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter this allergy was record in'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the clinical code is recorded for'
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)'
is_review	tinyint(1)	NOT NULL	'Is this instance of the code a review of a previous encounter'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the allergy'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the allergy'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age the patient was at the time of this event'
date_recorded	datetime	NOT NULL	'The date the allergy was recorded'
Unique index name: `allergy_intolerance_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the appointment'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
schedule_id	bigint	DEFAULT NULL	'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule'
start_date	date	DEFAULT NULL	'The start date of the appointment'
planned_duration	int	DEFAULT NULL	'The time allocated for the appointment, not necessarily the actual duration always in minutes'
actual_duration	int	DEFAULT NULL	'Time between sent in and left always in minutes'
appointment_status_concept_id	int	DEFAULT NULL	'The status of the appointment e.g. arrived/sent in/left/DNA'
patient_wait	int	DEFAULT NULL	'How long the patient waited from being marked as arrived to being sent in'
patient_delay	int	DEFAULT NULL	'How long the patient was delayed for'
date_time_sent_in	datetime	DEFAULT NULL	'Date and time the patient was sent into the practitioner'
date_time_left	datetime	DEFAULT NULL	'Date and time the patient left the practitioner'
source_id	varchar(36)	DEFAULT NULL	'Unique reference to the source of the appointment'
cancelled_date	datetime	DEFAULT NULL	'The date the appointment was cancelled'
Unique index name: `appointment_id` Index Columns: (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
id	bigint	NOT NULL	'same as the id column on the patient table '
property_id	int	NOT NULL	'IM reference (e.g. appointment type)'
value_id	int	DEFAULT NULL	'IM reference (e.g. Appointment Type)'
json_value	json	DEFAULT NULL	'where there is no mapped value_id, just raw JSON'
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)'
Index name: `appointment_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

concept

Column Name	Data Type	Constraint	Comment
dbid	int	NOT NULL	'Unique Id of the concept'
Document	int	NOT NULL	'Concept grouping construct, deprecated'
Id	varchar(150)	NOT NULL	'Unique concept identifier'
Draft	tinyint(1)	NOT NULL	'Whether its draft/autocreated or confirmed as a "proper" concept'
Name	varchar(255)	DEFAULT NULL	'Short name'
Description	varchar(400)	DEFAULT NULL	'Full name (or term for ontological concepts)'
Scheme	bigint	DEFAULT NULL	'The coding scheme for the code (Read, CTV3, SNOMED etc)'
Code	varchar(40)	DEFAULT NULL	'The code (non-unique unless coupled with a scheme)'
use_count	bigint	NOT NULL	'Rough indicator of number of occurences of the concept'
updated	datetime	NOT NULL	'The timestamp of the last update to the concept'
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
legacy	int	NOT NULL	'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'
core	int	NOT NULL	'the core (snomed, discovery) concept that the legacy concept maps to'
updated	datetime	NOT NULL	'Timestamp the map was last updated/added'
id	int	NOT NULL,	No comment yet added
deleted	tinyint(1)	DEFAULT NULL,	No comment yet added
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`)

diagnostic_order

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the diagnostic order'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter the observation was recorded at'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the diagnostic order was identified by a clinician'
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)'
result_value	double	DEFAULT NULL	'The value of the result of the observation'
result_value_units	varchar(50)	DEFAULT NULL	'The units of the result of the observation'
result_date	date	DEFAULT NULL	'The date of the result'
result_text	text	No Constraint	'Any text associated with the result'
result_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the result'
is_problem	tinyint(1)	NOT NULL	'Whether the observation is marked as a problem'
is_review	tinyint(1)	NOT NULL	'Whether the observation is a review of an existing problem'
problem_end_date	date	DEFAULT NULL	'The end date of the problem'
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'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the observation'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the observation'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age of the patient at the time of the observation'
episodicity_concept_id	int	DEFAULT NULL	'Reference to the episodicity of the problem eg First, review, flare'
is_primary	tinyint(1)	DEFAULT NULL	'Whether the diagnostic order is a primary order'
date_recorded	datetime	DEFAULT NULL,	No comment yet added
Unique index name: `diagnostic_order_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the encounter'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
appointment_id	bigint	DEFAULT NULL	'Reference to the appointment this encounter took part on'
clinical_effective_date	date	DEFAULT NULL	'The date the clinical code is recorded for'
date_precision_concept_id	int	DEFAULT NULL	'Reference to the precision of the date of the encounter'
episode_of_care_id	bigint	DEFAULT NULL	'Is this instance of the code a review of a previous encounter'
service_provider_organization_id	bigint	DEFAULT NULL	'Reference to the service provider organisation'
core_concept_id	int	DEFAULT NULL	'Reference to the type of encounter'
non_core_concept_id	int	DEFAULT NULL	'Reference to the type of encounter'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age the patient was when this encounter took place'
Type	text	No Constraint	'Unused'
sub_type	text	No Constraint	'Unused'
admission_method	varchar(40)	DEFAULT NULL	'The admission method of the encounter'
end_date	date	DEFAULT NULL	'The end date of the encounter'
institution_location_id	text	No Constraint	'Reference to the institution the encounter took place at'
date_recorded	datetime	DEFAULT NULL	'The date the encounter was recorded'
Unique index name: `encounter_id` Index Columns: (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
id	bigint	NOT NULL	'same as the id column on the encounter table'
property_id	int	NOT NULL	'IM concept id reference (i.e. Admission method)'
value_id	int	DEFAULT NULL	'IM concept id reference (i.e. Emergency admission)'
json_value	json	DEFAULT NULL	'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)'
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)'
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
id	bigint	NOT NULL	'Unique Id of the encounter event'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	'The patient this event belongs to'
person_id	bigint	NOT NULL	'The person this event belongs to'
encounter_id	bigint	NOT NULL	'Reference to the parent encounter record'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
appointment_id	bigint	DEFAULT NULL	'Reference to the appointment this encounter took part on'
clinical_effective_date	datetime	DEFAULT NULL	'The date the encounter took place'
date_precision_concept_id	int	DEFAULT NULL	'Reference to the precision of the date of the encounter'
episode_of_care_id	bigint	DEFAULT NULL	'Reference to the episode of care this encounter belongs to'
service_provider_organization_id	bigint	DEFAULT NULL	'Reference to the service provider organisation'
core_concept_id	int	DEFAULT NULL	'Reference to the type of encounter'
non_core_concept_id	int	DEFAULT NULL	'Reference to the type of encounter'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age of the patient at the time of the encounter'
type	text	No Constraint	'Unused'
sub_type	text	No Constraint	'Unused'
admission_method	varchar(40)	DEFAULT NULL	'The admission method of the encounter'
end_date	date	DEFAULT NULL	'The end date of the encounter'
institution_location_id	bigint	DEFAULT NULL	'Reference to the institution the encounter took place at'
date_recorded	datetime	DEFAULT NULL	'The date the encounter was recorded'
finished	tinyint(1)	DEFAULT NULL	'Whether the encounter is finished'
Unique index name: `encounter_event_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

episode_of_care

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the episode of care'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
registration_type_concept_id	int	DEFAULT NULL	'Reference to the registration type of the patient'
registration_status_concept_id	int	DEFAULT NULL	'Reference to the registration status of the patient'
date_registered	date	DEFAULT NULL	'The date the registration was started  for this episode of care'
date_registered_end	date	DEFAULT NULL	'The date the registration was ended  for this episode of care'
usual_gp_practitioner_id	bigint	DEFAULT NULL	'Reference to the usual GP for this episode of care'
Unique index name: `episode_of_care_id` Index Columns: (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
dt_change	datetime(3)	NOT NULL	'date time the change was made to this DB'
change_type	tinyint	NOT NULL	'type of transaction 0=insert, 1=update, 2=delete'
table_id	tinyint	NOT NULL	'identifier of the table changed'
record_id	bigint	NOT NULL	'id of the record changed'

flag

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the flag'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
effective_date	date	DEFAULT NULL	'The date the flag was entered onto the patients record'
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)'
is_active	tinyint(1)	NOT NULL	'Whether the flag is active or not'
flag_text	text	No Constraint	'This is a warning set by the publisher regarding he patient'
Unique index name: `flag_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the location'
name	varchar(255)	DEFAULT NULL	'The name of a location set by the publisher. E.g. ward, clinic, domiciliary'
type_code	varchar(50)	DEFAULT NULL	'The type of location'
type_desc	varchar(255)	DEFAULT NULL	'Textual description of the type of location eg GP Practice'
postcode	varchar(10)	DEFAULT NULL	'The postcode of the location'
managing_organization_id	bigint	DEFAULT NULL	'Reference to the managing organisation of the location'
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
id	bigint	NOT NULL	'Unique Id of the medication order'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter the medication order was issued in'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the medication order was issued'
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)'
dose	varchar(1000)	DEFAULT NULL	'Textual description of the dose'
quantity_value	double	DEFAULT NULL	'The value of the medication that was prescribed eg 50'
quantity_unit	varchar(255)	DEFAULT NULL	'The unit of the medication that was prescribed eg tablets'
duration_days	int	DEFAULT NULL	'How many days the medication is prescribed for'
estimated_cost	double	DEFAULT NULL	'The estimated cost of the medication'
medication_statement_id	bigint	DEFAULT NULL	'Reference to the medication statement.  A medication statement can have many medication orders'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'
bnf_reference	varchar(6)	DEFAULT NULL	'A reference to the drug in the BNF dictionary'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age the patient was at the time of this event'
issue_method	text	No Constraint	'The issue method of the medication eg hand written'
Unique index name: `medication_order_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the medication'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter this medication was recorded in'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the medication was clinical relevant'
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)'
is_active	tinyint(1)	DEFAULT NULL	'Whether the medication is active or not'
cancellation_date	date	DEFAULT NULL	'The date the medication was cancelled'
dose	varchar(1000)	DEFAULT NULL	'Texual description of the dose of the medication'
quantity_value	double	DEFAULT NULL	'The value of the medication that was prescribed eg 50'
quantity_unit	varchar(255)	DEFAULT NULL	'The unit of the medication that was prescribed eg tablets'
authorisation_type_concept_id	int	DEFAULT NULL	'Reference to the authorisation type'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'
bnf_reference	varchar(6)	DEFAULT NULL	'A reference to the drug in the BNF dictionary'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age the patient was at the time of this event'
Issue_method	text	No Constraint	'The issue method of the medication eg hand written'
date_recorded	datetime	DEFAULT NULL,	No comment yet added
Unique index name: `medication_statement_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the observation'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter the observation was recorded at'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the observation was identified by a clinician'
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)'
result_value	double	DEFAULT NULL	'The value of the result of the observation'
result_value_units	varchar(50)	DEFAULT NULL	'The units of the result of the observation'
result_date	date	DEFAULT NULL	'The date of the result'
result_text	text	No Constraint	'Any text associated with the result'
result_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the result'
is_problem	tinyint(1)	NOT NULL	'Whether the observation is marked as a problem'
is_review	tinyint(1)	NOT NULL	'Whether the observation is a review of an existing problem'
problem_end_date	date	DEFAULT NULL	'The end date of the problem'
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'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the observation'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the observation'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age of the patient at the time of the observation'
episodicity_concept_id	int	DEFAULT NULL	'Reference to the episodicity of the problem eg First, review, flare'
is_primary	tinyint(1)	DEFAULT NULL	'Whether the observation is a primary observation'
date_recorded	datetime	DEFAULT NULL	'The date the observation was recorded in the system'
Unique index name: `observation_id` Index Columns: (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
id	bigint	NOT NULL	'same as the id column on the observation table'
property_id	int	NOT NULL	'IM reference (i.e. significance)'
value_id	int	DEFAULT NULL	'IM reference (i.e. minor, significant)'
json_value	json	DEFAULT NULL	'the JSON data itself '
text_value	varchar(255)	DEFAULT NULL	'where there is no mapped value_id or raw JSON, just a basic text value'
Index name: `observation_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

organization

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the organisation'
ods_code	varchar(50)	DEFAULT NULL	'ODS Code of the organisation'
Name	varchar(255)	DEFAULT NULL	'Name of the organisation'
type_code	varchar(50)	DEFAULT NULL	'The type of organisation'
type_desc	varchar(255)	DEFAULT NULL	'Textual description of the type of organisation eg GP Practice'
Postcode	varchar(10)	DEFAULT NULL	'The postcode of the organisation'
parent_organization_id	bigint	DEFAULT NULL	'The id of the parent organisation'
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
id	bigint	NOT NULL	'organisation ID, corresponds to same ID in the organizaton table'
publishing_software	varchar(50)	NOT NULL	'software name of publishing system, i.e. SystmOne'
last_data_to_dds	datetime	DEFAULT NULL	'date time data was last sent to DDS'
last_data_cutoff	datetime	DEFAULT NULL	'cutoff date time of the last extract from the publishing system'
PRIMARY KEY CONSTRAINT(s) (`id`,`publishing_software`)

patient

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the patient'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
title	varchar(255)	DEFAULT NULL	'The title of the patient'
first_names	varchar(255)	DEFAULT NULL	'The first names of the patient'
last_name	varchar(255)	DEFAULT NULL	'The last name of the patient'
gender_concept_id	int	DEFAULT NULL	'Reference to the gender of the patient'
nhs_number	varchar(255)	DEFAULT NULL	'The NHS number of the patient'
date_of_birth	date	DEFAULT NULL	'The date of birth of the patient'
date_of_death	date	DEFAULT NULL	'The date of death of the patient'
current_address_id	bigint	DEFAULT NULL	'Reference to the current address of the patient'
ethnic_code_concept_id	int	DEFAULT NULL	'Reference to the ethnicity of the patient'
registered_practice_organization_id	bigint	DEFAULT NULL	'Reference to the organisation the patient is registered at'
birth_year	smallint	DEFAULT NULL,	No comment yet added
birth_month	tinyint	DEFAULT NULL,	No comment yet added
birth_week	tinyint	DEFAULT NULL,	No comment yet added
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
id	bigint	NOT NULL	'same as the id column on the patient table '
property_id	int	NOT NULL	'IM reference (e.g. Cause of death)'
value_id	int	DEFAULT NULL	'IM reference (e.g. COVID)'
json_value	json	DEFAULT NULL	'where there is no mapped value_id, just raw JSON'
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)'
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
Id	bigint	NOT NULL	'Unique Id of the address'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
address_line_1	varchar(255)	DEFAULT NULL	'The first line of the address'
address_line_2	varchar(255)	DEFAULT NULL	'The second line of the address'
address_line_3	varchar(255)	DEFAULT NULL	'The third line of the address'
address_line_4	varchar(255)	DEFAULT NULL	'The fourth line of the address'
city	varchar(255)	DEFAULT NULL	'The city'
postcode	varchar(255)	DEFAULT NULL	'The postcode'
use_concept_id	int	NOT NULL	'use of address (e.g. home, temporary)'
start_date	date	NOT NULL	'The start date of this address being relevant'
end_date	date	DEFAULT NULL	'The end date of this address being relevant'
lsoa_2001_code	varchar(9)	DEFAULT NULL	'A reference to the LSOA_2001 code'
lsoa_2011_code	varchar(9)	DEFAULT NULL	'A reference to the LSOA_2011 code'
msoa_2001_code	varchar(9)	DEFAULT NULL	'A reference to the MSOA_2001 code'
msoa_2011_code	varchar(9)	DEFAULT NULL	'A reference to the MSOA_2011 code'
ward_code	varchar(9)	DEFAULT NULL	'The ward the address belongs to'
local_authority_code	varchar(9)	DEFAULT NULL	'The local authority the address belongs to'
townsend_deprivation_index	double	DEFAULT NULL,	No comment yet added
Unique index name: `ux_patient_address_id` Index Columns: (Id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`Id`,`patient_id`,`person_id`)

patient_address_match

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	No comment yet added
patient_address_id	bigint	NOT NULL,	No comment yet added
uprn	varchar(255)	CHARACTER SET	No comment yet added
uprn_ralf00	varchar(255)	DEFAULT NULL,	No comment yet added
status	tinyint(1)	DEFAULT NULL,	No comment yet added
uprn_property_classification	varchar(45)	CHARACTER SET	No comment yet added
latitude	double	DEFAULT NULL,	No comment yet added
longitude	double	DEFAULT NULL,	No comment yet added
uprn_xcoordinate	double	DEFAULT NULL,	No comment yet added
uprn_ycoordinate	double	DEFAULT NULL,	No comment yet added
qualifier	varchar(50)	CHARACTER SET	No comment yet added
match_rule	varchar(4096)	CHARACTER SET	No comment yet added
match_date	datetime	DEFAULT NULL,	No comment yet added
abp_address_number	varchar(255)	CHARACTER SET	No comment yet added
abp_address_street	varchar(255)	CHARACTER SET	No comment yet added
abp_address_locality	varchar(255)	CHARACTER SET	No comment yet added
abp_address_town	varchar(255)	CHARACTER SET	No comment yet added
abp_address_postcode	varchar(10)	CHARACTER SET	No comment yet added
abp_address_organization	varchar(255)	CHARACTER SET	No comment yet added
match_pattern_postcode	varchar(255)	CHARACTER SET	No comment yet added
match_pattern_street	varchar(255)	CHARACTER SET	No comment yet added
match_pattern_number	varchar(255)	CHARACTER SET	No comment yet added
match_pattern_building	varchar(255)	CHARACTER SET	No comment yet added
match_pattern_flat	varchar(255)	CHARACTER SET	No comment yet added
algorithm_version	varchar(255)	CHARACTER SET	No comment yet added
epoch	varchar(255)	CHARACTER SET	No comment yet added
Index name: `patient_address_uprn_index` Index Columns: (uprn)
Index name: `patient_address_patient_address_id` Index Columns: (id,uprn)
PRIMARY KEY CONSTRAINT(s) (`id`)

patient_address_ralf

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL,	No comment yet added
organization_id	bigint	NOT NULL,	No comment yet added
patient_id	bigint	NOT NULL,	No comment yet added
person_id	bigint	NOT NULL,	No comment yet added
patient_address_id	bigint	NOT NULL,	No comment yet added
patient_address_match_uprn_ralf00	varchar(255)	NOT NULL,	No comment yet added
salt_name	varchar(50)	NOT NULL,	No comment yet added
ralf	varchar(255)	NOT NULL,	No comment yet added
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
id	bigint	NOT NULL	'Unique Id of the patient contact'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
use_concept_id	int	DEFAULT NULL	'use of contact (e.g. mobile, home,work'
type_concept_id	int	DEFAULT NULL	'type of contact (e.g. phone, email)'
start_date	date	DEFAULT NULL	'The start date of the contact being valid'
end_date	date	DEFAULT NULL	'The end date of the contact being valid'
value	varchar(255)	DEFAULT NULL	'The value of the contact information eg phone number, email address'
Unique index name: `ux_patient_contact_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

patient_pseudo_id

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the patient pseudo id'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	'Reference to the patient this registration status history belongs to'
person_id	bigint	NOT NULL	'Reference to the person this registration status history belongs to'
salt_name	varchar(50)	NOT NULL	'The name of the salt used to create the pseudo id'
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)'
is_nhs_number_valid	tinyint(1)	NOT NULL	'Whether the nhs number is valid'
is_nhs_number_verified_by_publisher	tinyint(1)	NOT NULL	'Whether the nhs number has been verified by the publisher'
Unique index name: `ux_patient_pseudo_id` Index Columns: (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
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'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
uprn	bigint	DEFAULT NULL,	No comment yet added
qualifier	varchar(50)	DEFAULT NULL,	No comment yet added
algorithm	varchar(255)	DEFAULT NULL,	No comment yet added
match	varchar(255)	DEFAULT NULL,	No comment yet added
no_address	tinyint(1)	DEFAULT NULL,	No comment yet added
invalid_address	tinyint(1)	DEFAULT NULL,	No comment yet added
missing_postcode	tinyint(1)	DEFAULT NULL,	No comment yet added
invalid_postcode	tinyint(1)	DEFAULT NULL,	No comment yet added
Unique index name: `patient_uprn_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`)

person

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

practitioner

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

procedure_request

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the procedure'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter the procedure was administered at'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the procedure was administered by a clinician'
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)'
status_concept_id	int	DEFAULT NULL	'Reference to the status of the procedure'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the procedure'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the procedure'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age of the patient at the time of the procedure'
date_recorded	datetime	DEFAULT NULL	'The date the procedure was recorded in the source system'
Unique index name: `procedure_request_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the referral'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
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'
person_id	bigint	NOT NULL	'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter the referral was made in'
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	'The date the referral was made'
date_precision_concept_id	smallint	DEFAULT NULL	'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'
requester_organization_id	bigint	DEFAULT NULL	'Reference to the organisation that made the refereral request'
recipient_organization_id	bigint	DEFAULT NULL	'Reference to the organization receiving the referral'
referral_request_priority_concept_id	smallint	DEFAULT NULL	'Reference to the priority of the referral'
referral_request_type_concept_id	int	DEFAULT NULL	'Reference to the type of referral request'
Mode	varchar(50)	DEFAULT NULL	'The mode of the referral'
outgoing_referral	tinyint(1)	DEFAULT NULL	'Whether this is an outgoing referral'
is_review	tinyint(1)	DEFAULT NULL	'Whether this referral is a review'
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the referral'
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the referral'
age_at_event	decimal(5,2)	DEFAULT NULL	'The age of the patient at the time of the referral'
date_recorded	datetime	DEFAULT NULL	'The date the referral request was added to the source system'
Unique index name: `referral_request_id` Index Columns: (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
id	bigint	NOT NULL	'Unique Id of the registration status history'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	'Reference to the patient this registration status history belongs to'
person_id	bigint	NOT NULL	'Reference to the person this registration status history belongs to'
episode_of_care_id	bigint	DEFAULT NULL	'Reference to the episode of care this status history belongs to'
registration_status_concept_id	int	DEFAULT NULL	'Reference to the registration status'
start_date	datetime	DEFAULT NULL	'The start date for the period this registration status history was valid'
end_date	datetime	DEFAULT NULL	'The end date for the period this registration status history was valid'
Unique index name: `ux_registration_status_history_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

schedule

Column Name	Data Type	Constraint	Comment
id	bigint	NOT NULL	'Unique Id of the schedule'
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'
practitioner_id	bigint	DEFAULT NULL	'Reference to the practitioner who owns the schedule'
start_date	date	DEFAULT NULL	'The start date of the schedule'
type	varchar(255)	DEFAULT NULL	'The type of schedule eg Timed Appointments'
location	varchar(255)	DEFAULT NULL	'Textual description of the location the schedule was held at'
name	varchar(150)	DEFAULT NULL	'The name of the schedule'
Unique index name: `schedule_id` Index Columns: (id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`)