Compass 2.1 Schema Documentation

This has been superseded by version 2.3

See Compass 2.3.0 Schema Documentation for more information.

This page describes the Remote Subscriber Database (RSD) Schema (Compass 2) Version: 2.1

allergy_intolerance

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

appointment

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

appointment_additional

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'same as the id column on the patient table '	No Foreign Key reference
property_id	int	NOT NULL	'IM reference (e.g. appointment type)'	No Foreign Key reference
value_id	int	DEFAULT NULL	'IM reference (e.g. appointment type)'	No Foreign Key reference
json_value	json	DEFAULT NULL	'where there is no mapped value_id, just raw JSON'	No Foreign Key reference
text_value	varchar(255)	DEFAULT NULL	'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'	No Foreign Key reference
Index name: `appointment_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

concept

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
dbid	int	NOT NULL	'Unique concept int DB identifier'	No Foreign Key reference
document	int	DEFAULT NULL	'Document this concept originated from'	No Foreign Key reference
id	varchar(150)	CHARACTER SET	'Unique human-readable concept id'	No Foreign Key reference
draft	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
name	varchar(255)	DEFAULT NULL	'Short name'	No Foreign Key reference
description	varchar(400)	DEFAULT NULL	'Full name (or term for ontological concepts)'	No Foreign Key reference
scheme	bigint	DEFAULT NULL	'The coding scheme for the code (Read, CTV3, SNOMED etc)'	No Foreign Key reference
code	varchar(40)	CHARACTER SET	'The code (non-unique unless coupled with a scheme)'	No Foreign Key reference
use_count	bigint	No Constraint	'Rough indicator of number of occurences of the concept'	No Foreign Key reference
updated	datetime	NOT NULL	'The timestamp of the last update to the concept'	No Foreign Key reference
Index name: `ix_scheme_code` Index Columns: (scheme,code)
Index name: `ix_code` Index Columns: (code)
Index name: `ix_dbid_code` Index Columns: (dbid,code)
PRIMARY KEY CONSTRAINT(s) (`dbid`)

concept_map

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
legacy	int	NOT NULL	'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'	No Foreign Key reference
core	int	NOT NULL	'the core (snomed, discovery) concept that the legacy concept maps to'	No Foreign Key reference
updated	datetime	NOT NULL	'Timestamp the map was last updated/added'	No Foreign Key reference
id	int	NOT NULL,	No comment yet added	No Foreign Key reference
deleted	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
Unique index name: `concept_map_uq` Index Columns: (legacy,deleted,updated)
Index name: `ix_legacy_core` Index Columns: (legacy,core)
PRIMARY KEY CONSTRAINT(s) (`id`)

database_version

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
version	varchar(255)	NOT NULL	'version of the database'	No Foreign Key reference
valid_from	date	NOT NULL	'date that the version was updated'	No Foreign Key reference
valid_to	date	DEFAULT NULL	'date that the version was made outdated'	No Foreign Key reference
wiki_page	varchar(255)	DEFAULT NULL	'the wiki page where this database structure is found'	No Foreign Key reference
Unique index name: `valid_to_uq` Index Columns: (valid_to)
PRIMARY KEY CONSTRAINT(s) (`valid_from`,`version`)

diagnostic_order

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

encounter

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

encounter_additional

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

encounter_event

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

episode_of_care

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'Unique Id of the episode of care'	No Foreign Key reference
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'	patient.organization_id
patient_id	bigint	NOT NULL	'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'	patient.id
person_id	bigint	NOT NULL	'Unique individual across all organisations'	No Foreign Key reference
registration_type_concept_id	int	DEFAULT NULL	'Reference to the registration type of the patient'	No Foreign Key reference
registration_status_concept_id	int	DEFAULT NULL	'Reference to the registration status of the patient'	No Foreign Key reference
date_registered	date	DEFAULT NULL	'The date the registration was started  for this episode of care'	No Foreign Key reference
date_registered_end	date	DEFAULT NULL	'The date the registration was ended  for this episode of care'	No Foreign Key reference
usual_gp_practitioner_id	bigint	DEFAULT NULL	'Reference to the usual GP for this episode of care'	practitioner.id
Unique index name: `episode_of_care_id` Index Columns: (id)
Index name: `fk_episode_of_care_patient_id_organisation_id` Index Columns: (patient_id,organization_id)
Index name: `fk_episode_of_care_practitioner_id` Index Columns: (usual_gp_practitioner_id)
Index name: `episode_of_care_patient_id` Index Columns: (patient_id)
Index name: `episode_of_care_registration_type_concept_id` Index Columns: (registration_type_concept_id)
Index name: `episode_of_care_date_registered` Index Columns: (date_registered)
Index name: `episode_of_care_date_registered_end` Index Columns: (date_registered_end)
Index name: `episode_of_care_person_id` Index Columns: (person_id)
Index name: `episode_of_care_organization_id` Index Columns: (organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

event_log

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
dt_change	datetime(3)	NOT NULL	'date time the change was made to this DB'	No Foreign Key reference
change_type	tinyint	NOT NULL	'type of transaction 0=insert, 1=update, 2=delete'	No Foreign Key reference
table_id	tinyint	NOT NULL	'identifier of the table changed'	No Foreign Key reference
record_id	bigint	NOT NULL	'id of the record changed'	No Foreign Key reference

flag

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

location

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'Unique Id of the location'	No Foreign Key reference
name	varchar(255)	DEFAULT NULL	'The name of a location set by the publisher. E.g. ward, clinic, domiciliary'	No Foreign Key reference
type_code	varchar(50)	DEFAULT NULL	'The type of location'	No Foreign Key reference
type_desc	varchar(255)	DEFAULT NULL	'Textual description of the type of location eg GP Practice'	No Foreign Key reference
postcode	varchar(10)	DEFAULT NULL	'The postcode of the location'	No Foreign Key reference
managing_organization_id	bigint	DEFAULT NULL	'Reference to the managing organisation of the location'	organization.id
Unique index name: `location_id` Index Columns: (id)
Index name: `fk_location_managing_organisation_id` Index Columns: (managing_organization_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

medication_order

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

medication_statement

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'Unique Id of the medication'	No Foreign Key reference
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'	patient.organization_id
patient_id	bigint	NOT NULL	'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'	patient.id
person_id	bigint	NOT NULL	'Unique individual across all organisations'	No Foreign Key reference
encounter_id	bigint	DEFAULT NULL	'Reference to the encounter this medication was recorded in'	encounter.id
practitioner_id	bigint	DEFAULT NULL	'The clinician the activity is recorded against'	practitioner.id
clinical_effective_date	datetime	DEFAULT NULL	'The date the medication was clinical relevant'	No Foreign Key reference
date_precision_concept_id	int	DEFAULT NULL	'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'	No Foreign Key reference
is_active	tinyint(1)	DEFAULT NULL	'Whether the medication is active or not'	No Foreign Key reference
cancellation_date	date	DEFAULT NULL	'The date the medication was cancelled'	No Foreign Key reference
dose	varchar(1000)	DEFAULT NULL	'Texual description of the dose of the medication'	No Foreign Key reference
quantity_value	double	DEFAULT NULL	'The value of the medication that was prescribed eg 50'	No Foreign Key reference
quantity_unit	varchar(255)	DEFAULT NULL	'The unit of the medication that was prescribed eg tablets'	No Foreign Key reference
authorisation_type_concept_id	int	DEFAULT NULL	'Reference to the authorisation type'	No Foreign Key reference
core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'	No Foreign Key reference
non_core_concept_id	int	DEFAULT NULL	'Reference to the clinical coding of the medication'	No Foreign Key reference
bnf_reference	varchar(6)	DEFAULT NULL	'A reference to the drug in the BNF dictionary'	No Foreign Key reference
age_at_event	decimal(5,2)	DEFAULT NULL	'The age the patient was at the time of this event'	No Foreign Key reference
issue_method	text	No Constraint	'The issue method of the medication eg hand written'	No Foreign Key reference
date_recorded	datetime	DEFAULT NULL,	No comment yet added	No Foreign Key reference
Unique index name: `medication_statement_id` Index Columns: (id)
Index name: `fk_medication_statement_encounter_id` Index Columns: (encounter_id)
Index name: `fk_medication_statement_patient_id_organization_id` Index Columns: (patient_id,organization_id)
Index name: `fk_medication_statement_practitioner_id` Index Columns: (practitioner_id)
Index name: `medication_statement_patient_id` Index Columns: (patient_id)
Index name: `medication_statement_dmd_id` Index Columns: (patient_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)

observation

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

observation_additional

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'same as the id column on the observation table'	No Foreign Key reference
property_id	int	NOT NULL	'IM reference (i.e. significance)'	No Foreign Key reference
value_id	int	DEFAULT NULL	'IM reference (i.e. minor, significant)'	No Foreign Key reference
json_value	json	DEFAULT NULL	'the JSON data itself '	No Foreign Key reference
text_value	varchar(255)	DEFAULT NULL	'where there is no mapped value_id or raw JSON, just a basic text value'	No Foreign Key reference
Index name: `observation_additional_value_id` Index Columns: (value_id)
PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)

organization

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'Unique Id of the organisation'	No Foreign Key reference
ods_code	varchar(50)	DEFAULT NULL	'ODS Code of the organisation'	No Foreign Key reference
name	varchar(255)	DEFAULT NULL	'Name of the organisation'	No Foreign Key reference
type_code	varchar(50)	DEFAULT NULL	'The type of organisation'	No Foreign Key reference
type_desc	varchar(255)	DEFAULT NULL	'Textual description of the type of organisation eg GP Practice'	No Foreign Key reference
postcode	varchar(10)	DEFAULT NULL	'The postcode of the organisation'	No Foreign Key reference
parent_organization_id	bigint	DEFAULT NULL	'The id of the parent organisation'	No Foreign Key reference
Unique index name: `organization_id` Index Columns: (id)
Index name: `fki_organization_parent_organization_id` Index Columns: (parent_organization_id)
PRIMARY KEY CONSTRAINT(s) (`id`)

organization_metadata

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

patient

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

patient_additional

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

patient_address

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

patient_address_match

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

patient_address_ralf

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL,	No comment yet added	No Foreign Key reference
organization_id	bigint	NOT NULL,	No comment yet added	organization.id
patient_id	bigint	NOT NULL,	No comment yet added	patient.id
person_id	bigint	NOT NULL,	No comment yet added	person.id
patient_address_id	bigint	NOT NULL,	No comment yet added	patient_address.id
patient_address_match_uprn_ralf00	varchar(255)	NOT NULL,	No comment yet added	No Foreign Key reference
salt_name	varchar(50)	NOT NULL,	No comment yet added	No Foreign Key reference
ralf	varchar(255)	NOT NULL,	No comment yet added	No Foreign Key reference
Index name: `fk_patient_address_ralf_organization_id` Index Columns: (organization_id)
Index name: `fk_patient_address_ralf_person_id` Index Columns: (person_id)
Index name: `ux_patient_address_ralf_id` Index Columns: (id)
Index name: `patient_address_ralf_patient_id` Index Columns: (patient_id)
Index name: `patient_address_ralf_patient_address_id` Index Columns: (patient_address_id)
Index name: `patient_address_ralf_patient_address_match_uprn_ralf_00` Index Columns: (patient_address_match_uprn_ralf00)
PRIMARY KEY CONSTRAINT(s) (`id`,`patient_address_id`,`patient_address_match_uprn_ralf00`)

patient_contact

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

patient_pseudo_id

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

patient_uprn

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
patient_id	bigint	NOT NULL	'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'	patient.id
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'	patient.organization_id
person_id	bigint	NOT NULL	'Unique individual across all organisations'	No Foreign Key reference
uprn	bigint	DEFAULT NULL,	No comment yet added	No Foreign Key reference
qualifier	varchar(50)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
algorithm	varchar(255)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
match	varchar(255)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
no_address	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
invalid_address	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
missing_postcode	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
invalid_postcode	tinyint(1)	DEFAULT NULL,	No comment yet added	No Foreign Key reference
Unique index name: `patient_uprn_id` Index Columns: (patient_id)
Index name: `fk_patient_uprn_patient_id_organisation_id` Index Columns: (patient_id,organization_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`)

person

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

practitioner

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

procedure_request

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

referral_request

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

registration_status_history

Column Name	Data Type	Constraint	Comment	Foreign Key Reference
id	bigint	NOT NULL	'Unique Id of the registration status history'	No Foreign Key reference
organization_id	bigint	NOT NULL	'Owning organisation (i.e. publisher)'	No Foreign Key reference
patient_id	bigint	NOT NULL	'Reference to the patient this registration status history belongs to'	No Foreign Key reference
person_id	bigint	NOT NULL	'Reference to the person this registration status history belongs to'	No Foreign Key reference
episode_of_care_id	bigint	DEFAULT NULL	'Reference to the episode of care this status history belongs to'	episode_of_care.id
registration_status_concept_id	int	DEFAULT NULL	'Reference to the registration status'	No Foreign Key reference
start_date	datetime	DEFAULT NULL	'The start date for the period this registration status history was valid'	No Foreign Key reference
end_date	datetime	DEFAULT NULL	'The end date for the period this registration status history was valid'	No Foreign Key reference
Unique index name: `ux_registration_status_history_id` Index Columns: (id)
Index name: `fk_registration_status_history_episode_id` Index Columns: (episode_of_care_id)
PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)

schedule

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

DATABASE UPDATE INFO

New Version	Date Updated
2.1	2023-01-06T16:46:00.566742300