Remote Subscriber Database (RSD) Schema (Compass 2)

Table: organisation

PRIMARY KEY (pk_organization_id PRIMARY KEY (id))

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

Table: location

PRIMARY KEY (pk_location_id PRIMARY KEY id)

Column name	Data type	Constraint	Comments	ExpandReferences
Id	bigint	NOT NULL
name	varchar(255),
type_code,	varchar(50)
type_desc	varchar(255)
postcode	varchar10),
managing_organization_id	bigint
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	ExpandReferences
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL
Name	varchar(1024)
role_code	varchar(50)
role_desc	varchar(255)
gmc_code	varchar(50)
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	ExpandReferences
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL
practitioner_id	bigint
start_date	date
type	varchar(255)
Location	varchar(255)
Name	varchar(150)
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	ExpandReferences
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL
title	varchar(50),
first_names	varchar(255),
last_name	varchar(255),
gender_concept_id	int
nhs_number	varchar(255),
date_of_birth date,	date
date_of_death date,	date
current_address_id,	bigint	NOT NULL
ethnic_code_concept_id	int
registered_practice_organization_id	bigint
CONSTRAINT pk_person_id PRIMARY KEY (id)

Table: patient

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	ExpandReferences
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL
person_id	bigint	NOT NULL
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	ExpandReferences
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
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	ExpandReferences
id	bigint	NOT NULL
organization_id	varchar(36)	NOT NULL
patient_id	varchar(36)	NOT NULL
person_id	bigint	NOT NULL
practitioner_id	bigint
schedule_id	bigint
start_date	date
planned_duration	integer
actual_duration	integer
appointment_status_concept_id	int
patient_wait	integer
patient_delay	integer
date_time_sent_in	datetime
date_time_left	datetime
source_id	varchar(36)
cancelled_date	Datetime
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	ExpandReferences
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		The clinician the activity is recorded against
appointment_id	bigint
clinical_effective_date	date		The date the clinical code is recorded for
date_precision_concept_id	int
episode_of_care_id	bigint		Is this instance of the code a review of a previous encounter
service_provider_organization_id	bigint
core_concept_id	int
non_core_concept_id	int
age_at_event	decimal(5,2)
Type	text
sub_type	text
admission_method	varchar(40)
end_date	date
institution_location_id	text
date_recorded	datetime
service_provider_organization_id	bigint(20)	DEFAULT NULL
CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),
/*known examples of Emis consultations referring to unknown appointments, so removed this
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	ExpandReferences
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
appointment_id	bigint
clinical_effective_date	datetime
date_precision_concept_id	int
episode_of_care_id	bigint
service_provider_organization_id	bigint
core_concept_id	int
non_core_concept_id	int
age_at_event	decimal(5,2)
type	text
sub_type	text
admission_method	varchar(40)
end_date	date
institution_location_id	bigint
date_recorded	datetime
Finished	boolean
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	ExpandReferences
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	ExpandReferences
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
is_review	boolean	NOT NULL	Is this instance of the code a review of a previous encounter
core_concept_id	Int
non_core_concept_id	Int
age_at_event	decimal(5,2)
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	ExpandReferences
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
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
core_concept_id	int
non_core_concept_id	int
bnf_reference	varchar(6)
age_at_event	decimal(5,2)
Issue_method	text
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	ExpandReferences
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			encounter.id
practitioner_id	bigint		The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date
date_precision_concept_id	int
dose	varchar(1000)
quantity_value	real
quantity_unit	varchar(255)
duration_days	integer
estimated_cost	real
medication_statement_id	bigint
core_concept_id	int
non_core_concept_id	int
bnf_reference	varchar(6)
age_at_event	decimal(5,2)
issue_method	text
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	ExpandReferences
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
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	ExpandReferences
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
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
non_core_concept_id	int
age_at_event	decimal (5,2)
episodicity_concept_id	int
is_primary	boolean
date_recorded	datetime
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	ExpandReferences
id	bigint	NOT NULL
organization_id	bigint	NOT NULL
patient_id	bigint	NOT NULL
person_id	bigint	NOT NULL
encounter_id	bigint
practitioner_id	bigint
clinical_effective_date	date
date_precision_concept_id	int
result_value	real
result_value_units	varchar(50)
result_date	date
result_text	text
result_concept_id	int
is_problem	boolean	NOT NULL
is_review	boolean	NOT NULL
problem_end_date	date
parent_observation_id	bigint
core_concept_id	int
non_core_concept_id	int
age_at_event	decimal (5,2)
episodicity_concept_id	int
is_primary	boolean
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	ExpandReferences
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
non_core_concept_id	int
age_at_event	decimal(5,2)
date_recorded	datetime
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	ExpandReferences
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
requester_organization_id	bigint(20)	DEFAULT NULL
recipient_organization_id	bigint(20)	DEFAULT NULL
referral_request_priority_concept_id
referral_request_type_concept_id	int			referral.request.type
Mode	varchar(50)
outgoing_referral	boolean
is_review	boolean
core_concept_id	int
non_core_concept_id	int
age_at_event	decimal(5,2)
date_recorded	datetime
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	ExpandReferences
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	ExpandReferences
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
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	ExpandReferences
id	bigint	NOT NULL
organization_id	bigint	NOT NULL
patient_id	bigint	NOT NULL
person_id bigint	bigint
use_concept_id	int		use of contact (e.g. mobile, home,work
type_concept_id	int		type of contact (e.g. phone, email)
start_date date	date
end_date date	date
value	varchar(255)
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	ExpandReferences
Id	bigint	NOT NULL
organization_id	bigint	NOT NULL
patient_id	bigint	NOT NULL
person_id	bigint
address_line_1	varchar(255)
address_line_2	varchar(255)
address_line_3	varchar(255)
address_line_4	varchar(255)
city	varchar(255)
postcode	varchar(255)
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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)