CompassV2TestPages:v2.X

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

Allergy_intolerance

<tbody> </tbody>

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

Appointment

<tbody> </tbody>

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

Appointment_additional

<tbody> </tbody>

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

Concept

<tbody> </tbody>

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

Concept_map

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql
updated	datetime	NOT NULL	COMMENT 'Timestamp the map was last updated/added',	updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added',
legacy	int	NOT NULL	COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',	legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',
core	int	NOT NULL	COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',	core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',

Diagnostic_order

<tbody> </tbody>

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

Encounter

<tbody> </tbody>

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

Encounter_additional

<tbody> </tbody>

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

Encounter_event

<tbody> </tbody>

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

Episode_of_care

<tbody> </tbody>

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

Event_log

<tbody> </tbody>

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

Flag

<tbody> </tbody>

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

Location

<tbody> </tbody>

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

Medication_order

<tbody> </tbody>

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

Medication_statement

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql
dose	varchar(1000)	DEFAULT NULL	COMMENT 'Texual description of the dose of the medication',	dose varchar(1000) NULL COMMENT 'Texual description of the dose of the medication',
patient_id	bigint	NOT NULL	COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',	patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against',	practitioner_id bigint NULL COMMENT 'The clinician the activity is recorded against',
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication',	non_core_concept_id int NULL COMMENT 'Reference to the clinical coding of the medication',
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)',	organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the medication was clinical relevant',	clinical_effective_date date NULL COMMENT 'The date the medication was clinical relevant',
cancellation_date	date	DEFAULT NULL	COMMENT 'The date the medication was cancelled',	cancellation_date date NULL COMMENT 'The date the medication was cancelled',
quantity_unit	varchar(255)	DEFAULT NULL	COMMENT 'The unit of the medication that was prescribed eg tablets',	quantity_unit varchar(255) NULL COMMENT 'The unit of the medication that was prescribed eg tablets',
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age the patient was at the time of this event',	age_at_event decimal(5,2) NULL COMMENT 'The age the patient was at the time of this event',
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication',	core_concept_id int NULL COMMENT 'Reference to the clinical coding of the medication',
bnf_reference	varchar(6)	DEFAULT NULL	COMMENT 'A reference to the drug in the BNF dictionary',	bnf_reference varchar(6) NULL COMMENT 'A reference to the drug in the BNF dictionary',
authorisation_type_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the authorisation type',	authorisation_type_concept_id int NULL COMMENT 'Reference to the authorisation type',
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter this medication was recorded in',	encounter_id bigint NULL COMMENT 'Reference to the encounter this medication was recorded in',
id	bigint	NOT NULL	COMMENT 'Unique Id of the medication',	id bigint NOT NULL COMMENT 'Unique Id of the medication',
is_active	tinyint(1)	DEFAULT NULL	COMMENT 'Whether the medication is active or not',	is_active tinyint(1) NULL COMMENT 'Whether the medication is active or not',
person_id	bigint	NOT NULL	COMMENT 'Unique individual across all organisations',	person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
date_precision_concept_id	int	DEFAULT NULL	COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',	date_precision_concept_id int NULL COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',
quantity_value	double	DEFAULT NULL	COMMENT 'The value of the medication that was prescribed eg 50',	quantity_value double NULL COMMENT 'The value of the medication that was prescribed eg 50',

Observation

<tbody> </tbody>

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

Observation_additional

<tbody> </tbody>

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

Organization

<tbody> </tbody>

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

Organization_metadata

<tbody> </tbody>

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

Patient

<tbody> </tbody>

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

Patient_additional

<tbody> </tbody>

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

Patient_address

<tbody> </tbody>

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

Patient_address_match

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql

Patient_address_ralf

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql

Patient_contact

<tbody> </tbody>

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

Patient_pseudo_id

<tbody> </tbody>

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

Patient_uprn

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql
patient_id	bigint	NOT NULL	COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',	patient_id bigint NOT NULL COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times',
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)',	organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
person_id	bigint	NOT NULL	COMMENT 'Unique individual across all organisations',	person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',

Person

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql
title	varchar(255)	DEFAULT NULL	COMMENT 'The title of the person',	title varchar(255) NULL COMMENT 'The title of the person',
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)',	organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
gender_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the gender of the person',	gender_concept_id int NULL COMMENT 'Reference to the gender of the person',
date_of_birth	date	DEFAULT NULL	COMMENT 'The date of birth of the person',	date_of_birth date NULL COMMENT 'The date of birth of the person',
first_names	varchar(255)	DEFAULT NULL	COMMENT 'The first names of the person',	first_names varchar(255) NULL COMMENT 'The first names of the person',
nhs_number	varchar(255)	DEFAULT NULL	COMMENT 'The NHS number of the person',	nhs_number varchar(255) NULL COMMENT 'The NHS number of the person',
ethnic_code_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the ethnicity of the person',	ethnic_code_concept_id int NULL COMMENT 'Reference to the ethnicity of the person',
last_name	varchar(255)	DEFAULT NULL	COMMENT 'The last name of the person',	last_name varchar(255) NULL COMMENT 'The last name of the person',
date_of_death	date	DEFAULT NULL	COMMENT 'The date of death of the person',	date_of_death date NULL COMMENT 'The date of death of the person',
current_address_id	bigint	NOT NULL	COMMENT 'Reference to the current address of the person',	current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',
id	bigint	NOT NULL	COMMENT 'Unique Id of the person',	id bigint NOT NULL COMMENT 'Unique Id of the person',
registered_practice_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the organisation the person is registered at',	registered_practice_organization_id bigint NULL COMMENT 'Reference to the organisation the person is registered at',

Practitioner

<tbody> </tbody>

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

Procedure_request

<tbody> </tbody>

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

Referral_request

<tbody> </tbody>

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

Registration_status_history

<tbody> </tbody>

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

Schedule

<tbody> </tbody>

Column Name	Type	Constraint	Comment	Sql
start_date	date	DEFAULT NULL	COMMENT 'The start date of the schedule',	start_date date NULL COMMENT 'The start date of the schedule',
id	bigint	NOT NULL	COMMENT 'Unique Id of the schedule',	id bigint NOT NULL COMMENT 'Unique Id of the schedule',
name	varchar(150)	DEFAULT NULL	COMMENT 'The name of the schedule',	name varchar(150) NULL COMMENT 'The name of the schedule',
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)',	organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
type	varchar(255)	DEFAULT NULL	COMMENT 'The type of schedule eg Timed Appointments',	type varchar(255) NULL COMMENT 'The type of schedule eg Timed Appointments',
practitioner_id	bigint	DEFAULT NULL	COMMENT 'Reference to the practitioner who owns the schedule',	practitioner_id bigint NULL COMMENT 'Reference to the practitioner who owns the schedule',
location	varchar(255)	DEFAULT NULL	COMMENT 'Textual description of the location the schedule was held at',	location varchar(255) NULL COMMENT 'Textual description of the location the schedule was held at',