Remote Subscriber Database (RSD) Schema (Compass 1)

Table: allergy_intolerance

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	The date the clinical code is recorded for
date_precision_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)
snomed_concept_id	bigint(20)	DEFAULT NULL	The SNOMED code
original_code	varchar(100) CHARACTER SET utf8 COLLATE utf8_bin	DEFAULT NULL	The original code from the source system
original_term	varchar(1000)	DEFAULT NULL	The original code term from the source system
is_review	tinyint(1)	NOT NULL	Is this instance of the code a review of a previous encounter
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY allergy_intolerance_id (id),
KEY allergy_intolerance_patient_id (patient_id),
KEY allergy_intolerance_snomed_concept_id (snomed_concept_id)

Table: appointment

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
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
schedule_id	bigint(20)	DEFAULT NULL	The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule	schedule.id
start_date	datetime	DEFAULT NULL
planned_duration	int(11)	DEFAULT NULL	The time allocated for the appointment, not necessarily the actual duration always in minutes
actual_duration	int(11)	DEFAULT NULL	Time between sent in and left always in minutes
appointment_status_id	smallint(6)	NOT NULL	The status of the appointment e.g. arrived/sent in/left/DNA	appointment.status.id
patient_wait	int(11)	DEFAULT NULL	How long the patient waited from being marked as arrived to being sent in
patient_delay	int(11)	DEFAULT NULL
sent_in	datetime	DEFAULT NULL	Date and time the patient was sent into the practitioner
left	datetime	DEFAULT NULL	Date and time the patient left the practitioner
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY appointment_id (id),
KEY appointment_patient_id (patient_id)

Table: appointment_status

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
value	varchar(50)	NOT NULL
PRIMARY KEY (id)

Table: date_precision

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
value	varchar(11)	NOT NULL
PRIMARY KEY (id)

Table: encounter

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
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
appointment_id	bigint(20)	DEFAULT NULL		appointment.id
clinical_effective_date	date	DEFAULT NULL	The date the clinical code is recorded for
date_precision_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)
snomed_concept_id	bigint(20)	DEFAULT NULL	The SNOMED code
original_code	varchar(100)	DEFAULT NULL	The original code from the source system
original_term	varchar(1000)	DEFAULT NULL	The original code term from the source system
episode_of_care_id	bigint(20)	DEFAULT NULL	Is this instance of the code a review of a previous encounter
service_provider_organization_id	bigint(20)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY encounter_id (id),
KEY encounter_patient_id (patient_id),
KEY fki_encounter_appointment_id (appointment_id),
KEY fki_encounter_patient_id_organization_id (patient_id,organization_id),
KEY encounter_snomed_concept_id_clinical_effective_date (snomed_concept_id,clinical_effective_date),
KEY snomed_concept_id_clinical_effective_date_patient_id (snomed_concept_id,clinical_effective_date,patient_id),
KEY patient_id_clinical_effective_date_snomed_concept_id (patient_id,clinical_effective_date,snomed_concept_id),
KEY encounter_snomed_concept_id (snomed_concept_id)

Table: encounter_additional

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint	NOT NULL	Same as the id column on the encounter table or encounter_event id column (sub encounters)
property_id	varchar(255)	NOT NULL	IM reference (i.e. Admission method)
value_id	varchar(255)	NOT NULL	IM reference (i.e. Emergency admission)
PRIMARY KEY (id, property_id)

Table: encounter_detail

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint(20)	NOT NULL	Same as the id column on the encounter table
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
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
episode_of_care_id	bigint(20)	DEFAULT NULL
clinical_effective_date	date	DEFAULT NULL	The date the clinical code is recorded for
date_precision_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)
recording_practitioner_id	bigint(20)	DEFAULT NULL	Who recorded the encounter
recording_date	date	DEFAULT NULL
appointment_id	bigint(20)	DEFAULT NULL	The unique reference for that appointment	appointment.id
service_provider_organization_id	bigint(20)	DEFAULT NULL	Organisation that performed the encounter
location_id	bigint(20)	DEFAULT NULL	Where the encounter took place	location.id
end_date	date	DEFAULT NULL
duration_minutes	int(11)	DEFAULT NULL	Duration always in minutes
completion_status_concept_id	bigint(20)	DEFAULT NULL
healthcare_service_type_concept_id	bigint(20)	DEFAULT NULL
interaction_mode_concept_id	bigint(20)	DEFAULT NULL
administrative_action_concept_id	bigint(20)	DEFAULT NULL
purpose_concept_id	bigint(20)	DEFAULT NULL
disposition_concept_id	bigint(20)	DEFAULT NULL
site_of_care_type_concept_id	bigint(20)	DEFAULT NULL
patient_status_concept_id	bigint(20)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY ix_encounter_detail_id (id),
KEY ix_encounter_detail_patient_id (patient_id),
KEY ix_encounter_detail_appointment_id (appointment_id),
KEY ix_encounter_detail_patient_id_organization_id (patient_id,organization_id)

Table: encounter_event

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	NOT NULL		registration.type.id
practitioner_id	bigint	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
appointment_id	bigint	DEFAULT NULL		appointment.id
clinical_effective_date	datetime	DEFAULT NULL
date_precision_id	smallint	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
snomed_concept_id	bigint	DEFAULT NULL
original_code	varchar(100) binary	DEFAULT NULL
original_term	varchar(1000)	DEFAULT NULL
episode_of_care_id	bigint	DEFAULT NULL		episode.of.care.id
service_provider_organization_id	bigint	DEFAULT NULL
date_recorded	datetime	DEFAULT NULL
location_id	bigint	DEFAULT NULL	Where the encounter took place	location.id
finished	boolean	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),

Table: episode_of_care

0PRIMARY 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
registration_type_id	smallint(6)	DEFAULT NULL		registration.type.id
date_registered	date	DEFAULT NULL
date_registered_end	date	DEFAULT NULL
usual_gp_practitioner_id	bigint(20)	DEFAULT NULL		practitioner.id
registration_status_id	smallint(6)	DEFAULT NULL		registration.status
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY episode_of_care_id (id),
KEY episode_of_care_patient_id (patient_id),
KEY episode_of_care_registration_type_id (registration_type_id),
KEY episode_of_care_date_registered (date_registered),
KEY episode_of_care_date_registered_end (date_registered_end),
KEY episode_of_care_person_id (person_id),
KEY episode_of_care_organization_id (organization_id),
KEY reg_type_id_patient_id_date_registered_date_registered_end (registration_type_id,patient_id,date_registered,date_registered_end)

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
PRIMARY KEY (ethnic_code)

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_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
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY flag_id (id),
KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
KEY fk_flag_date_precision (date_precision_id),
KEY flag_patient_id (patient_id),
CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) REFERENCES patient (id, organization_id)

Table: local_authority_lookup

PRIMARY KEY (local_authority_code)

Column name	Data type	Constraint	Comments	ExpandReferences
local_authority_code	varchar(9)	NOT NULL		local.authority.code
local_authority_name	varchar(255)	DEFAULT NULL
PRIMARY KEY (local_authority_code)

Table: location

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint(20)	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	varchar(10)	DEFAULT NULL
managing_organization_id	bigint(20)	DEFAULT NULL
PRIMARY KEY (id),
UNIQUE KEY location_id (id),
KEY fk_location_managing_organisation_id (managing_organization_id)

Table: Isoa_lookup

PRIMARY KEY (lsoa_code)

Column name	Data type	Constraint	Comments	ExpandReferences
lsoa_code	char(9)	NOT NULL
lsoa_name	varchar(255)	DEFAULT NULL
imd_score	decimal(53)	DEFAULT NULL	Index of Multiple Deprivation (IMD) Score
imd_rank	int(11)	DEFAULT NULL	Index of Multiple Deprivation (IMD) Rank (where 1 is most deprived)
imd_decile	int(11)	DEFAULT NULL	Index of Multiple Deprivation (IMD) Decile (where 1 is most deprived 10% of LSOAs)
income_score	decimal(53)	DEFAULT NULL	Income Score (rate)
income_rank	int(11)	DEFAULT NULL	Income Rank (where 1 is most deprived)
income_decile	int(11)	DEFAULT NULL	Income Decile (where 1 is most deprived 10% of LSOAs)
employment_score	decimal(53)	DEFAULT NULL	Employment Score (rate)
employment_rank	int(11)	DEFAULT NULL	Employment Rank (where 1 is most deprived)
employment_decile	int(11)	DEFAULT NULL	Employment Decile (where 1 is most deprived 10% of LSOAs)
education_score	decimal(53)	DEFAULT NULL	Education Skills and Training Score
education_rank	int(11)	DEFAULT NULL	Education Skills and Training Rank (where 1 is most deprived)
education_decile	int(11)	DEFAULT NULL	Education Skills and Training Decile (where 1 is most deprived 10% of LSOAs)
health_score	decimal(53)	DEFAULT NULL	Health Deprivation and Disability Score
health_rank	int(11)	DEFAULT NULL	Health Deprivation and Disability Rank (where 1 is most deprived)
health_decile	int(11)	DEFAULT NULL	Health Deprivation and Disability Decile (where 1 is most deprived 10% of LSOAs)
crime_score	decimal(53)	DEFAULT NULL	Crime Score
crime_rank	int(11)	DEFAULT NULL	Crime Rank (where 1 is most deprived)
crime_decile	int(11)	DEFAULT NULL	Crime Decile (where 1 is most deprived 10% of LSOAs)
housing_and_services_barriers_score	decimal(53)	DEFAULT NULL	Barriers to Housing and Services Score
housing_and_services_barriers_rank	int(11)	DEFAULT NULL	Barriers to Housing and Services Rank (where 1 is most deprived)
housing_and_services_barriers_decile	int(11)	DEFAULT NULL	Barriers to Housing and Services Decile (where 1 is most deprived 10% of LSOAs)
living_environment_score	decimal(53)	DEFAULT NULL	Living Environment Score
living_environment_rank	int(11)	DEFAULT NULL	Living Environment Rank (where 1 is most deprived)
living_environment_decile	int(11)	DEFAULT NULL	Living Environment Decile (where 1 is most deprived 10% of LSOAs)
idaci_score	decimal(53)	DEFAULT NULL	Income Deprivation Affecting Children Index (IDACI) Score (rate)
idaci_rank	int(11)	DEFAULT NULL	Income Deprivation Affecting Children Index (IDACI) Rank (where 1 is most deprived)
idaci_decile	int(11)	DEFAULT NULL	Income Deprivation Affecting Children Index (IDACI) Decile (where 1 is most deprived 10% of LSOAs)
idaopi_score	decimal(53)	DEFAULT NULL	Income Deprivation Affecting Older People (IDAOPI) Score (rate)
idaopi_rank	int(11)	DEFAULT NULL	Income Deprivation Affecting Older People (IDAOPI) Rank (where 1 is most deprived)
idaopi_decile	int(11)	DEFAULT NULL	Income Deprivation Affecting Older People (IDAOPI) Decile (where 1 is most deprived 10% of LSOAs)
children_and_young_sub_domain_score	decimal(53)	DEFAULT NULL	Children and Young People Sub-domain Score
children_and_young_sub_domain_rank	int(11)	DEFAULT NULL	Children and Young People Sub-domain Rank (where 1 is most deprived)
children_and_young_sub_domain_decile	int(11)	DEFAULT NULL	Children and Young People Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
adult_skills_sub_somain_score	decimal(53)	DEFAULT NULL	Adult Skills Sub-domain Score
adult_skills_sub_somain_rank	int(11)	DEFAULT NULL	Adult Skills Sub-domain Rank (where 1 is most deprived)
adult_skills_sub_somain_decile	int(11)	DEFAULT NULL	Adult Skills Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
geographical_barriers_sub_domain_score	decimal(53)	DEFAULT NULL	Geographical Barriers Sub-domain Score
geographical_barriers_sub_domain_rank	int(11)	DEFAULT NULL	Geographical Barriers Sub-domain Rank (where 1 is most deprived)
geographical_barriers_sub_domain_decile	int(11)	DEFAULT NULL	Geographical Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
wider_barriers_sub_domain_score	decimal(53)	DEFAULT NULL	Wider Barriers Sub-domain Score
wider_barriers_sub_domain_rank	int(11)	DEFAULT NULL	Wider Barriers Sub-domain Rank (where 1 is most deprived)
wider_barriers_sub_domain_decile	int(11)	DEFAULT NULL	Wider Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
indoors_sub_domain_score	decimal(53)	DEFAULT NULL	Indoors Sub-domain Score
indoors_sub_domain_rank	int(11)	DEFAULT NULL	Indoors Sub-domain Rank (where 1 is most deprived)
indoors_sub_domain_decile	int(11)	DEFAULT NULL	Indoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
outdoors_sub_domain_score	decimal(53)	DEFAULT NULL	Outdoors Sub-domain Score
outdoors_sub_domain_rank	int(11)	DEFAULT NULL	Outdoors Sub-domain Rank (where 1 is most deprived)
outdoors_sub_domain_decile	int(11)	DEFAULT NULL	Outdoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
total_population	int(11)	DEFAULT NULL	Total population: mid 2012 (excluding prisoners)
dependent_children_0_to_15	int(11)	DEFAULT NULL	Dependent Children aged 0-15: mid 2012 (excluding prisoners)
population_16_to_59	int(11)	DEFAULT NULL	Population aged 16-59: mid 2012 (excluding prisoners)
older_population_60_and_over	int(11)	DEFAULT NULL	Older population aged 60 and over: mid 2012 (excluding prisoners)
PRIMARY KEY (lsoa_code)

Table: medication_order

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_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)
dmd_id	bigint(20)	DEFAULT NULL
dose	varchar(1000)	DEFAULT NULL
quantity_value	double	DEFAULT NULL
quantity_unit	varchar(255)	DEFAULT NULL
duration_days	int(11)	NOT NULL
estimated_cost	double	DEFAULT NULL
medication_statement_id	bigint(20)	DEFAULT NULL
original_term	varchar(1000)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id)
UNIQUE KEY medication_order_id (id)
KEY medication_order_patient_id (patient_id)
KEY medication_order_dmd_id (dmd_id)

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_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)
dmd_id	bigint(20)	DEFAULT NULL
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
medication_statement_ authorisation_type_id	smallint(6)	NOT NULL
original_term	varchar(1000)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY medication_statement_id (id),
KEY medication_statement_patient_id (patient_id),
KEY medication_statement_dmd_id (dmd_id)

Table: medication_statement_authorisation_type

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
value	varchar(50)	NOT NULL
PRIMARY KEY (id)

Table: msoa_lookup

PRIMARY KEY (msoa_code)

Column name	Data type	Constraint	Comments	ExpandReferences
msoa_code char(9)	NOT NULL
msoa_name	varchar(255)	DEFAULT NULL
PRIMARY KEY (msoa_code)

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_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)
snomed_concept_id	bigint(20)	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
original_code	varchar(20)	DEFAULT NULL
is_problem	tinyint(1)	NOT NULL
original_term	varchar(1000)	DEFAULT NULL
is_review	tinyint(1)	NOT NULL
problem_end_date	date	DEFAULT NULL
parent_observation_id	bigint(20)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY observation_id (id),
KEY observation_patient_id (patient_id),
KEY observation_snomed_concept_id (snomed_concept_id),
KEY observation_snomed_concept_id_is_problem (snomed_concept_id,is_problem),
KEY observation_snomed_concept_id_value (snomed_concept_id,result_value),
KEY ix_observation_organization_id (organization_id),
KEY ix_observation_clinical_effective_date (clinical_effective_date),
KEY ix_observation_person_id (person_id),
KEY observation_original_code (original_code)

Table: organization

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint(20)	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(20)	DEFAULT NULL
latitude	decimal(108)	DEFAULT NULL
longitude	decimal(118)	DEFAULT NULL
PRIMARY KEY (id),
UNIQUE KEY organization_id (id),
KEY fki_organization_parent_organization_id (parent_organization_id),
KEY organization_id_parent_organization_id (id,parent_organization_id)

Table: patient

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
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
patient_gender_id	smallint(6)	NOT NULL
pseudo_id	varchar(255)	DEFAULT NULL
age_years	int(11)	DEFAULT NULL
age_months	int(11)	DEFAULT NULL
age_weeks	int(11)	DEFAULT NULL
date_of_death	date	DEFAULT NULL
postcode_prefix	varchar(20)	DEFAULT NULL	First part of the postcode
lsoa_code	varchar(50)	DEFAULT NULL
msoa_code	varchar(50)	DEFAULT NULL		msoa.lookup.id
ethnic_code	char(1)	DEFAULT NULL
ward_code	varchar(50)	DEFAULT NULL
local_authority_code	varchar(50)	DEFAULT NULL
registered_practice_organization_id	bigint(20)	DEFAULT NULL		organisation.id
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY patient_id (id),
KEY patient_person_id (person_id),
KEY organization_id (organization_id),
KEY patient_id_organization_id (id,organization_id),
KEY organization_id_date_of_death_id (organization_id,date_of_death,id),
KEY patient_date_of_death (date_of_death),
KEY postcode_prefix (postcode_prefix),
KEY pseudo_id (pseudo_id)

Table: patient_gender

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
value	varchar(10)	NOT NULL
PRIMARY KEY (id)

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
lsoa_code	varchar(50)	DEFAULT NULL		Isoa.lookup
pseudo_uprn	varchar(255)	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
should_be_ignored	bit(1)	DEFAULT b'0'
property_class	varchar(10)	DEFAULT NULL
ncmp_uprn	varchar(255)	DEFAULT NULL
ncmp_pid	varchar(255)	DEFAULT NULL
ncmp_pid_valid	tinyint(4)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,patient_id),
UNIQUE KEY patient_uprn_id (patient_id),
KEY ix_patient_uprn_patient_org_uprn (patient_id,organization_id,pseudo_uprn),
KEY ix1 (patient_id)

Table: person

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint(20)	NOT NULL
patient_gender_id	smallint(6)	NOT NULL
pseudo_id	varchar(255)	DEFAULT NULL
age_years	int(11)	DEFAULT NULL
age_months	int(11)	DEFAULT NULL
age_weeks	int(11)	DEFAULT NULL
date_of_death	date	DEFAULT NULL
postcode_prefix	varchar(20)	DEFAULT NULL
lsoa_code	varchar(50)	DEFAULT NULL		lsoa.lookup.id
msoa_code	varchar(50)	DEFAULT NULL		msoa.lookup.id
ethnic_code	char(1)	DEFAULT NULL
ward_code	varchar(50)	DEFAULT NULL		ward.code.id
local_authority_code	varchar(50)	DEFAULT NULL
registered_practice_organization_id	bigint(20)	DEFAULT NULL
PRIMARY KEY (id),
UNIQUE KEY person_id (id)

Table: practitioner

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	bigint(20)	NOT NULL
organization_id	bigint(20)	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
PRIMARY KEY (id),
UNIQUE KEY practitioner_id (id)

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_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)
snomed_concept_id	bigint(20)	DEFAULT NULL
procedure_request_status_id	smallint(6)	DEFAULT NULL
original_code	varchar(100) CHARACTER SET utf8 COLLATE utf8_bin	DEFAULT NULL
original_term	varchar(1000)	DEFAULT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY procedure_request_id (id),
KEY procedure_request_patient_id (patient_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
PRIMARY KEY (id)

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_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)
snomed_concept_id	bigint(20)	DEFAULT NULL
requester_organization_id	bigint(20)	DEFAULT NULL
recipient_organization_id	bigint(20)	DEFAULT NULL
priority_id	smallint(6)	DEFAULT NULL		referral.request.priority
type_id	smallint(6)	DEFAULT NULL		referral.request.type
mode	varchar(50)	DEFAULT NULL
outgoing_referral *	tinyint(1)	DEFAULT NULL
original_code	varchar(100) CHARACTER SET utf8 COLLATE utf8_bin	DEFAULT NULL
original_term	varchar(1000)	DEFAULT NULL
is_review	tinyint(1)	NOT NULL
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY referral_request_id (id),
KEY referral_request_patient_id (patient_id),
KEY referral_request_snomed_concept_id (snomed_concept_id)

Table: referral_request_priority

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
value	varchar(50)	NOT NULL
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
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
PRIMARY KEY (id)

Table: registration_status_history

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		patient.id
person_id	bigint(20)	NOT NULL		person.id
episode_of_care_id	bigint(20)	DEFAULT NULL		episode.of.care.id
registration_status_id	int(11)	DEFAULT NULL		registration.status.id
start_date	datetime	DEFAULT NULL
end_date	datetime	DEFAULT NULL
PRIMARY KEY (organization_id,id,patient_id,person_id),
UNIQUE KEY ux_registration_status_history_id (id)
ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations';

Table: registration_type

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
id	smallint(6)	NOT NULL
code	varchar(10)	NOT NULL
description	varchar(30)	NOT NULL
PRIMARY KEY (id)

Table: schedule

PRIMARY KEY (organization_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
practitioner_id bigint(20)	bigint(20)	DEFAULT NULL		practitioner.id
start_date	date	DEFAULT NULL
type	varchar(255)	DEFAULT NULL
location	varchar(255)	DEFAULT NULL		location.id
PRIMARY KEY (organization_id,id),
UNIQUE KEY schedule_id (id)

Table: ward_lookup

PRIMARY KEY (ward_code)

Column name	Data type	Constraint	Comments	ExpandReferences
ward_code	varchar(9)	NOT NULL
ward_name	varchar(255)	DEFAULT NULL
PRIMARY KEY (ward_code)