Remote Subscriber Database (RSD) Schema (Compass 2)

Table: organization

PRIMARY KEY (pk_organization_id PRIMARY KEY (id))

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
ods_code	varchar(50)	DEFAULT NULL
Name	varchar(255)	DEFAULT NULL
type_code	varchar(50)	DEFAULT NULL
type_desc	varchar(255)	DEFAULT NULL
Postcode	varchar(10)	DEFAULT NULL
parent_organization_id	bigint	DEFAULT NULL
PRIMARY KEY pk_organization_id PRIMARY KEY (id)

Table: location

PRIMARY KEY (pk_location_id PRIMARY KEY id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
name	varchar(255)	DEFAULT NULL	The name of a location set by the publisher. E.g. ward, clinic, domiciliary
type_code,	varchar(50)	DEFAULT NULL
type_desc	varchar(255)	DEFAULT NULL
postcode	varchar10)	DEFAULT NULL
managing_organization_id	bigint	DEFAULT NULL
CONSTRAINT pk_location_id PRIMARY KEY (id)
CONSTRAINT fk_location_organisation_id FOREIGN KEY (managing_organization_id)
REFERENCES organization (id)
MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: practitioner

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)	organisation.id
name	varchar(1024)	DEFAULT NULL
role_code	varchar(50)	DEFAULT NULL
role_desc	varchar(255)	DEFAULT NULL
gmc_code	varchar(50)	DEFAULT NULL
CONSTRAINT pk_practitioner_id PRIMARY KEY (id),
CONSTRAINT fk_practitioner_organisation_id FOREIGN KEY (organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: schedule

PRIMARY KEY (organization_id, id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)	organization.id
practitioner_id	bigint	DEFAULT NULL		practitioner.id
start_date	date	DEFAULT NULL
type	varchar(255)	DEFAULT NULL
Location	varchar(255)	DEFAULT NULL		location.id
Name	varchar(150)	DEFAULT NULL
CONSTRAINT pk_schedule_id PRIMARY KEY (organization_id, id),
CONSTRAINT fk_schedule_organization_id FOREIGN KEY (organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: person

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL
title	varchar(50)	DEFAULT NULL
first_names	varchar(255)	DEFAULT NULL
last_name	varchar(255)	DEFAULT NULL
gender_concept_id	int	DEFAULT NULL
nhs_number	varchar(255)	DEFAULT NULL
date_of_birth date,	date	DEFAULT NULL
date_of_death date,	date	DEFAULT NULL
current_address_id,	bigint	NOT NULL
ethnic_code_concept_id	int	DEFAULT NULL
registered_practice_organization_id	bigint	DEFAULT NULL
CONSTRAINT pk_person_id PRIMARY KEY (id)

Table: patient

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)	organization.id
person_id	bigint	NOT NULL	Unique individual across all organisations	person.id
title	varchar(50)
first_names	varchar(255)
last_name	varchar(255)
gender_concept_id	int
nhs_number	varchar(255)
date_of_birth	date
date_of_death	date
current_address_id	bigint
ethnic_code_concept_id	int
registered_practice_organization_id	bigint
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_patient_organization_id FOREIGN KEY (organization_id)
REFERENCES organization (id) MATCH SIMPLE

Table: episode_of_care

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
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	patient.id
person_id	bigint	NOT NULL	Unique individual across all organisations	person.id
registration_type_concept_id	int	DEFAULT NULL		registration.type.id
registration_status_concept_id	int	DEFAULT NULL
date_registered date	date	DEFAULT NULL
date_registered_end date	date	DEFAULT NULL
usual_gp_practitioner_id	bigint	DEFAULT NULL		Practitioner.id
CONSTRAINT pk_episode_of_care_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_episode_of_care_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_episode_of_care_practitioner_id FOREIGN KEY (usual_gp_practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: appointment

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	varchar(36)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	varchar(36)	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	person.id
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	schedule.id
start_date	date	DEFAULT NULL
planned_duration	int	DEFAULT NULL	The time allocated for the appointment, not necessarily the actual duration always in minutes
actual_duration	int	DEFAULT NULL	Time between sent in and left always in minutes
appointment_status_concept_id	int	DEFAULT NULL	The status of the appointment e.g. arrived/sent in/left/DNA
patient_wait	int	DEFAULT NULL	How long the patient waited from being marked as arrived to being sent in
patient_delay	int	DEFAULT NULL
date_time_sent_in	datetime	DEFAULT NULL	Date and time the patient was sent into the practitioner
date_time_left	datetime	DEFAULT NULL	Date and time the patient left the practitioner
source_id	varchar(36)	DEFAULT NULL
cancelled_date	datetime	DEFAULT NULL
CONSTRAINT pk_appointment_id PRIMARY KEY (organization_id,person_id,id),
CONSTRAINT fk_appointment_organization_id FOREIGN KEY (organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_appointment_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: encounter

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)
patient_id	bigint	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
person_id	bigint	NOT NULL	Unique individual across all organisations
practitioner_id	bigint	DEFAULT NULL	The clinician the activity is recorded against
appointment_id	bigint	DEFAULT NULL
clinical_effective_date	date	DEFAULT NULL	The date the clinical code is recorded for
date_precision_concept_id	int	DEFAULT NULL
episode_of_care_id	bigint	DEFAULT NULL	Is this instance of the code a review of a previous encounter
service_provider_organization_id	bigint	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
Type	text	DEFAULT NULL
sub_type	text	DEFAULT NULL
admission_method	varchar(40)	DEFAULT NULL
end_date	date	DEFAULT NULL
institution_location_id	text	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
service_provider_organization_id	bigint(20)	DEFAULT NULL
CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),
CONSTRAINT fk_encounter_appointment_id FOREIGN KEY (appointment_id)
REFERENCES appointment (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,*/
CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_episode_of_care_id FOREIGN KEY (episode_of_care_id)
REFERENCES episode_of_care (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: encounter_event

PRIMARY KEY (organization_id, person_id, id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL
patient_id	bigint	NOT NULL
person_id	bigint	NOT NULL
encounter_id	bigint	NOT NULL	parent encounter record
practitioner_id	bigint	DEFAULT NULL
appointment_id	bigint	DEFAULT NULL
clinical_effective_date	datetime	DEFAULT NULL
date_precision_concept_id	int	DEFAULT NULL
episode_of_care_id	bigint	DEFAULT NULL
service_provider_organization_id	bigint	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
type	text	DEFAULT NULL
sub_type	text	DEFAULT NULL
admission_method	varchar(40)	DEFAULT NULL
end_date	date	DEFAULT NULL
institution_location_id	bigint	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
finished	boolean	DEFAULT NULL
CONSTRAINT pk_encounter_event_id PRIMARY KEY (organization_id, person_id, id),
CONSTRAINT fk_encounter_event_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_event_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_event_episode_of_care_id FOREIGN KEY (episode_of_care_id)
REFERENCES episode_of_care (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_encounter_event_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: encounter_additional

PRIMARY KEY (id, property_id, value_id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL	same as the id column on the encounter table
property_id	bigint	NOT NULL	IM reference (i.e. Admission method)
value_id	bigint	NOT NULL	IM reference (i.e. Emergency admission)
CONSTRAINT pk_encounter_additional_id PRIMARY KEY (id, property_id, value_id)

Table: allergy_intolerance

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
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	patient.id
person_id	bigint	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint	DEFAULT NULL		encounter.id
practitioner_id	bigint	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	Date	DEFAULT NULL	The date the clinical code is recorded for
date_precision_concept_id	Int	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
is_review	boolean	NOT NULL	Is this instance of the code a review of a previous encounter
core_concept_id	Int	DEFAULT NULL
non_core_concept_id	Int	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
date_recorded	datetime	NOT NULL	Is this instance of the code a review of a previous encounter
CONSTRAINT pk_allergy_intolerance_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_allergy_intolerance_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_allergy_intolerance_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_allergy_intolerance_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: medication_statement

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	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(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
is_active	tinyint(4)	DEFAULT NULL
cancellation_date	date	DEFAULT NULL
dose	varchar(1000)	DEFAULT NULL
quantity_value	double	DEFAULT NULL
quantity_unit	varchar(255)	DEFAULT NULL
authorisation_type_concept_id	int	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
bnf_reference	varchar(6)	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
Issue_method	text	DEFAULT NULL
CONSTRAINT pk_medication_statement_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_medication_statement_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_medication_statement_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_medication_statement_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: medication_order

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
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	patient.id
person_id	bigint	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint	DEFAULT NULL		encounter.id
practitioner_id	bigint	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	int	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
dose	varchar(1000)	DEFAULT NULL
quantity_value	real	DEFAULT NULL
quantity_unit	varchar(255)	DEFAULT NULL
duration_days	int	DEFAULT NULL
estimated_cost	real	DEFAULT NULL
medication_statement_id	bigint	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
bnf_reference	varchar(6)	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
issue_method	text	DEFAULT NULL
CONSTRAINT pk_medication_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_medication_order_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_medication_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_medication_order_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: flag

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)
patient_id	bigint(20)	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(20)	NOT NULL	Unique individual across all organisations	person.id
effective_date	date	DEFAULT NULL
date_precision_concept_id	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
is_active	tinyint(1)	NOT NULL
flag_text	text		This is a warning set by the publisher regarding he patient
CONSTRAINT pk_flag_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: observation

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	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(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
result_value	double	DEFAULT NULL
result_value_units	varchar(50)	DEFAULT NULL
result_date	date	DEFAULT NULL
result_text	text
result_concept_id	bigint(20)	DEFAULT NULL
is_problem	tinyint(1)	NOT NULL
is_review	tinyint(1)	NOT NULL
problem_end_date	date	DEFAULT NULL
parent_observation_id	bigint(20)	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal (5,2)	DEFAULT NULL
episodicity_concept_id	int	DEFAULT NULL
is_primary	boolean	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
CONSTRAINT pk_observation_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_observation_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_observation_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_observation_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: diagnostic_order

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL
patient_id	bigint	NOT NULL
person_id	bigint	NOT NULL
encounter_id	bigint	DEFAULT NULL
practitioner_id	bigint	DEFAULT NULL
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	int	DEFAULT NULL
result_value	real	DEFAULT NULL
result_value_units	varchar(50)	DEFAULT NULL
result_date	date	DEFAULT NULL
result_text	text	DEFAULT NULL
result_concept_id	int	DEFAULT NULL
is_problem	boolean	NOT NULL
is_review	boolean	NOT NULL
problem_end_date	date	DEFAULT NULL
parent_observation_id	bigint	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal (5,2)	DEFAULT NULL
episodicity_concept_id	int	DEFAULT NULL
is_primary	boolean	DEFAULT NULL
CONSTRAINT pk_diagnostic_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
CONSTRAINT fk_diagnostic_order_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_diagnostic_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_diagnostic_order_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: procedure_request

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	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(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	int	DEFAULT NULL
status_concept_id	int	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
CONSTRAINT pk_procedure_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
/*got an Emis procedure request referring to a confidential encounter, so can't enforce this
CONSTRAINT fk_procedure_request_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,*/
CONSTRAINT fk_procedure_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_procedure_request_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: referral_request

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	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(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_concept_id	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
requester_organization_id	bigint(20)	DEFAULT NULL
recipient_organization_id	bigint(20)	DEFAULT NULL
referral_request_priority_concept_id	smallint(6)	DEFAULT NULL		referral.request.priority
referral_request_type_concept_id	int	DEFAULT NULL		referral.request.type
Mode	varchar(50)	DEFAULT NULL
outgoing_referral	boolean	DEFAULT NULL
is_review	boolean	DEFAULT NULL
core_concept_id	int	DEFAULT NULL
non_core_concept_id	int	DEFAULT NULL
age_at_event	decimal(5,2)	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
CONSTRAINT pk_referral_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
/*got an Emis referral request referring to a confidential encounter, so can't enforce this
CONSTRAINT fk_referral_request_encounter_id FOREIGN KEY (encounter_id)
REFERENCES encounter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,*/
CONSTRAINT fk_referral_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_referral_request_practitioner_id FOREIGN KEY (practitioner_id)
REFERENCES practitioner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_referral_request_recipient_organization_id FOREIGN KEY (recipient_organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_referral_request_requester_organization_id FOREIGN KEY (requester_organization_id)
REFERENCES organization (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: pseudo_id

PRIMARY KEY (patient_id, salt_key_name)

Column name	Data type	Constraint	Comments	References
Id	bigint	NOT NULL
patient_id	bigint	NOT NULL
salt_key_name	varchar(50)	NOT NULL
pseudo_id	varchar(255)	DEFAULT NULL
CONSTRAINT pk_pseudo_id PRIMARY KEY (patient_id, salt_key_name)

Table: patient_uprn

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	References
patient_id	bigint(20)	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(20)	NOT NULL	Owning organisation (i.e. publisher)	organisation.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
Uprn	bigint	DEFAULT NULL
qualifier	varchar(50)	DEFAULT NULL
algorithm	varchar(255)	DEFAULT NULL
match	varchar(255)	DEFAULT NULL
no_address	tinyint(1)	DEFAULT NULL
invalid_address	tinyint(1)	DEFAULT NULL
missing_postcode	tinyint(1)	DEFAULT NULL
invalid_postcode	tinyint(1)	DEFAULT NULL
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`patient_id`),
CONSTRAINT fk_patient_uprn_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION

Table: patient_contact

PRIMARY KEY (organization_id,id,patient_id,person_id)

Column name	Data type	Constraint	Comments	References
id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)
patient_id	bigint	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint	DEFAULT NULL	Unique individual across all organisations	person.id
use_concept_id	int	DEFAULT NULL	use of contact (e.g. mobile, home,work
type_concept_id	int	DEFAULT NULL	type of contact (e.g. phone, email)
start_date date	date	DEFAULT NULL
end_date date	date	DEFAULT NULL
value	varchar(255)	DEFAULT NULL
CONSTRAINT pk_organization_id_id_patient_id_person_id PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`),
CONSTRAINT fk_patient_contact_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id)
COMMENT 'stores contact details (e.g. phone) for patients'

Table: patient_address

PRIMARY KEY (organization_id,id,patient_id,person_id)

Column name	Data type	Constraint	Comments	References
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)	organisation.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	DEFAULT NULL	Unique individual across all organisations	person.id
address_line_1	varchar(255)	DEFAULT NULL
address_line_2	varchar(255)	DEFAULT NULL
address_line_3	varchar(255)	DEFAULT NULL
address_line_4	varchar(255)	DEFAULT NULL
city	varchar(255)	DEFAULT NULL
postcode	varchar(255)	DEFAULT NULL
use_concept_id	int	NOT NULL	use of address (e.g. home, temporary)
start_date	Date	NOT NULL
end_date	Date	DEFAULT NULL
lsoa_2001_code	varchar(9)	DEFAULT NULL
lsoa_2011_code	varchar(9)	DEFAULT NULL
msoa_2001_code	varchar(9)	DEFAULT NULL
msoa_2011_code	varchar(9)	DEFAULT NULL
ward_code	varchar(9)	DEFAULT NULL
local_authority_code	varchar(9)	DEFAULT NULL
CONSTRAINT pk_organization_id_id_patient_id_person_id
PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`)
CONSTRAINT fk_patient_address_patient_id_organization_id
FOREIGN KEY (patient_id, organization_id)
REFERENCES patient (id, organization_id)
COMMENT 'stores address details for patients'

Table: event_log

Column name	Data type	Constraint	Comments	References
dt_change	datetime(3)	NOT NULL	date time the change was made to this DB
change_type	tinyint	NOT NULL	type of transaction 0=insert, 1=update, 2=delete
table_id	tinyint	NOT NULL	identifier of the table changed
record_id	bigint	NOT NULL	id of the record changed

Table: appointment_status

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	References
id	smallint(6)	NOT NULL
Value	varchar(50)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: concept

PRIMARY KEY (dbid)

Column name	Data type	Constraint	Comments	References
Dbid	int(11)	NOT NULL
Document	int(11)	NOT NULL
Id	varchar(150)	NOT NULL
Draft	tinyint(1)	NOT NULL
Name	varchar(255)	DEFAULT NULL
Description	varchar(400)	DEFAULT NULL
Scheme	bigint(20)	DEFAULT NULL
Code	varchar(40)	DEFAULT NULL
use_count	bigint(20)	NOT NULL DEFAULT 0
updated datetime	datetime	NOT NULL
CONSTRAINT PRIMARY KEY (dbid),

Table: concept_map

PRIMARY KEY (legacy)

Column name	Data type	Constraint	Comments	References
Legacy	int(11)	NOT NULL
Core	int(11)	NOT NULL
Updated	datetime	NOT NULL
CONSTRAINT PRIMARY KEY (legacy)

Table: concept_property_object

Column name	Data type	Constraint	Comments	References
dbid	int(11)	NOT NULL
group	int(11)	NOT NULL
property	int(11)	NOT NULL
value	int(11)	NOT NULL
updated	datetime	NOT NULL

Table: concept_tct

Column name	Data type	Constraint	Comments	References
Source	int(11)	DEFAULT NULL
Property	int(11)	DEFAULT NULL
Level	int(11)	DEFAULT NULL
Target	int(11)	DEFAULT NULL
Status	int(11)	DEFAULT NULL
created_date	datetime	DEFAULT NULL

Table: consent_code

Column name	Data type	Constraint	Comments	References
STATUS	varchar(10)	DEFAULT NULL
DESCRIPTION	varchar(100)	DEFAULT NULL
CODE	varchar(20)	DEFAULT NULL
TERM_CODE	varchar(20)	DEFAULT NULL
TERM	varchar(100)	DEFAULT NULL

Table: date_precision

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Value	varchar(11)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: ethnicity_lookup

PRIMARY KEY(ethnic_code)

Column name	Data type	Constraint	Comments	References
ethnic_code	char(1)	NOT NULL
ethnic_name	varchar(100)	DEFAULT NULL
CONSTRAINT PRIMARY KEY (ethnic_code)

Table: patient_address_match

PRIMARY KEY(id, uprn)

Column name	Data type	Constraint	Comments	References
Id	bigint(20)	NOT NULL
Uprn	varchar(255)	NOT NULL
Status	smallint(6)	DEFAULT NULL
Classification	varchar(45)	DEFAULT NULL
Latitude	double	DEFAULT NULL
Longitude	double	DEFAULT NULL
Xcoordinate	double	DEFAULT NULL
Ycoordinate	double	DEFAULT NULL
Qualifier	varchar(50)	DEFAULT NULL
Algorithm	varchar(255)	DEFAULT NULL
match_date	datetime	DEFAULT NULL
abp_address_number	varchar(255)	DEFAULT NULL
abp_address_street	varchar(255)	DEFAULT NULL
abp_address_locality	varchar(255)	DEFAULT NULL
abp_address_town	varchar(10)	DEFAULT NULL
abp_address_postcode	varchar(10)	DEFAULT NULL
abp_address_organization	varchar(255)	DEFAULT NULL
match_pattern_postcode	varchar(255)	DEFAULT NULL
match_pattern_street	varchar(255)	DEFAULT NULL
match_pattern_number	varchar(255)	DEFAULT NULL
match_pattern_building	varchar(255)	DEFAULT NULL
match_pattern_flat	varchar(255)	DEFAULT NULL
algorithm_version	varchar(255)	DEFAULT NULL
Epoc	varchar(255)	DEFAULT NULL
CONSTRAINT PRIMARY KEY (id,uprn)

Table: patient_gender

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Value	varchar(10)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: patient_pseudo_id

PRIMARY KEY(organization_id)

Column name	Data type	Constraint	Comments	References
Id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL
patient_id	bigint(20)	NOT NULL
person_id	bigint(20)	NOT NULL
salt_name	varchar(50)	NOT NULL
Skid	varchar(255)	NOT NULL
is_nhs_number_valid	tinyint(1)	NOT NULL
is_nhs_number_verified_by_publisher	tinyint(1)	NOT NULL
CONSTRAINT PRIMARY KEY (organization_id)

Table: procedure_request_status

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Value	varchar(50)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: referral_request_priority

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Value	varchar(50)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: referral_request_type

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Value	varchar(50)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: registration_status

PRIMARY KEY(id)

Column name	Data type	Constraint	Comments	References
Id	smallint(6)	NOT NULL
Code	varchar(10)	NOT NULL
Description	varchar(50)	NOT NULL
is_active	tinyint(1)	NOT NULL
CONSTRAINT PRIMARY KEY (id)

Table: registration_status_history

PRIMARY KEY(organization_id,id,patient_id,person_id)

Column name	Data type	Constraint	Comments	References
Id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL
patient_id	bigint(20)	NOT NULL
person_id	bigint(20)	NOT NULL
episode_of_care_id	bigint(20)	DEFAULT NULL
registration_status_concept_id	int(11)	DEFAULT NULL
start_date	datetime	DEFAULT NULL
end_date	datetime	DEFAULT NULL
CONSTRAINT PRIMARY KEY (organization_id,id,patient_id,person_id)