Remote Subscriber Database (RSD) Schema (Compass 1)

Table: allergy_intolerance

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	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	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
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	References
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	References
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	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
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_detail

PRIMARY KEY (organization_id,person_id,id)

id	bigint(20)	NOT NULL	Same as the id column on the encounter table
Column name	Data type	Constraint	Comments	References
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: episode_of_care

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
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	References
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	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_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	References
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	References
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)

lsoa_code	char(9)	NOT NULL
Column name	Data type	Constraint	Comments	References
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)

|id |bigint(20) |NOT NULL |

|

|-

|encounter_id |bigint(20) |DEFAULT NULL |

|encounter.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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id) |-

| colspan="5" | UNIQUE KEY medication_order_id (id) |-

| colspan="5" | KEY medication_order_patient_id (patient_id) |-

| colspan="5" | KEY medication_order_dmd_id (dmd_id) |}

Table: medication_statement

PRIMARY KEY (organization_id,person_id,id)

|id |bigint(20) |NOT NULL |

|

|-

|encounter_id |bigint(20) |DEFAULT NULL |

|encounter.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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-

| colspan="5" | UNIQUE KEY medication_statement_id (id), |-

| colspan="5" | KEY medication_statement_patient_id (patient_id), |-

| colspan="5" | KEY medication_statement_dmd_id (dmd_id) |}

Table: medication_statement_authorisation_type

PRIMARY KEY (id)

|id |smallint(6) |NOT NULL |

|

|-

|value |varchar(50) |NOT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id) |}

Table: msoa_lookup

PRIMARY KEY (msoa_code)

|msoa_code char(9) |NOT NULL |

|

|-

|msoa_name |varchar(255) |DEFAULT NULL |

|

|-

| colspan="5" | PRIMARY KEY (msoa_code) |}

Table: observation

PRIMARY KEY (organization_id,person_id,id)

|id |bigint(20) |NOT NULL |

|

|-

|encounter_id |bigint(20) |DEFAULT NULL |

|encounter.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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-

| colspan="5" | UNIQUE KEY observation_id (id), |-

| colspan="5" | KEY observation_patient_id (patient_id), |-

| colspan="5" | KEY observation_snomed_concept_id (snomed_concept_id), |-

| colspan="5" | KEY observation_snomed_concept_id_is_problem (snomed_concept_id,is_problem), |-

| colspan="5" | KEY observation_snomed_concept_id_value (snomed_concept_id,result_value), |-

| colspan="5" | KEY ix_observation_organization_id (organization_id), |-

| colspan="5" | KEY ix_observation_clinical_effective_date (clinical_effective_date), |-

| colspan="5" | KEY ix_observation_person_id (person_id), |-

| colspan="5" | KEY observation_original_code (original_code) |}

Table: organization

PRIMARY KEY (id)

|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 |

|

|-

| colspan="5" | PRIMARY KEY (id), |-

| colspan="5" | UNIQUE KEY organization_id (id), |-

| colspan="5" | KEY fki_organization_parent_organization_id (parent_organization_id), |-

| colspan="5" | KEY organization_id_parent_organization_id (id,parent_organization_id) |}

Table: patient

PRIMARY KEY (organization_id,person_id,id)

|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 |

|

|-

|-

|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 |-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-

| colspan="5" | UNIQUE KEY patient_id (id), |-

| colspan="5" | KEY patient_person_id (person_id), |-

| colspan="5" | KEY organization_id (organization_id), |-

| colspan="5" | KEY patient_id_organization_id (id,organization_id), |-

| colspan="5" | KEY organization_id_date_of_death_id (organization_id,date_of_death,id), |-

| colspan="5" | KEY patient_date_of_death (date_of_death), |-

| colspan="5" | KEY postcode_prefix (postcode_prefix), |-

| colspan="5" | KEY pseudo_id (pseudo_id) |}

Table: patient_gender

PRIMARY KEY (id)

|id |smallint(6) |NOT NULL |

|

|-

|value |varchar(10) |NOT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id) |}

Table: patient_uprn

PRIMARY KEY (organization_id,person_id,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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,patient_id), |-

| colspan="5" | UNIQUE KEY patient_uprn_id (patient_id), |-

| colspan="5" | KEY ix_patient_uprn_patient_org_uprn (patient_id,organization_id,pseudo_uprn), |-

| colspan="5" | KEY ix1 (patient_id) |}

Table: person

PRIMARY KEY (id)

|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 |

|

|-

| colspan="5" | PRIMARY KEY (id), |-

| colspan="5" | UNIQUE KEY person_id (id) |}

Table: practitioner

PRIMARY KEY (id)

|id |bigint(20) |NOT NULL |

|

|-

|name |varchar(1024) |DEFAULT NULL |

|

|-

|role_code |varchar(50) |DEFAULT NULL |

|

|-

|role_desc |varchar(255) |DEFAULT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id), |-

| colspan="5" | UNIQUE KEY practitioner_id (id) |}

Table: procedure_request

PRIMARY KEY (organization_id,person_id,id)

|id |bigint(20) |NOT NULL |

|

|-

|encounter_id |bigint(20) |DEFAULT NULL |

|encounter.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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-

| colspan="5" | UNIQUE KEY procedure_request_id (id), |-

| colspan="5" | KEY procedure_request_patient_id (patient_id) |}

Table: procedure_request_status

PRIMARY KEY (id)

|id |smallint(6) |NOT NULL |

|

|-

|value |varchar(50) |NOT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id) |}

Table: referral_request

PRIMARY KEY (organization_id,person_id,id)

|id |bigint(20) |NOT NULL |

|

|-

|encounter_id |bigint(20) |DEFAULT NULL |

|encounter.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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-

| colspan="5" | UNIQUE KEY referral_request_id (id), |-

| colspan="5" | KEY referral_request_patient_id (patient_id), |-

| colspan="5" | KEY referral_request_snomed_concept_id (snomed_concept_id) |}

Table: referral_request_priority

PRIMARY KEY (organization_id,person_id,id)

|id |smallint(6) |NOT NULL |

|

|-

|value |varchar(50) |NOT NULL |

|

|-

| colspan="5" | 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
PRIMARY KEY (id)

Table: registration_status

PRIMARY KEY (id)

|id |smallint(6) |NOT NULL |

|

|-

|code |varchar(10) |NOT NULL |

|

|-

|description |varchar(50) |NOT NULL |

|

|-

|is_active |tinyint(1) |NOT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id) |}

Table: registration_status_history

PRIMARY KEY (organization_id,person_id,id)

|id |bigint(20) |NOT NULL |

|

|-

|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 |

|

|-

| colspan="5" | PRIMARY KEY (organization_id,id,patient_id,person_id), |-

| colspan="5" | UNIQUE KEY ux_registration_status_history_id (id) |-

| colspan="5" | ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations'; |}

Table: registration_type

PRIMARY KEY (id)

|id |smallint(6) |NOT NULL |

|

|-

|code |varchar(10) |NOT NULL |

|

|-

|description |varchar(30) |NOT NULL |

|

|-

| colspan="5" | PRIMARY KEY (id) |}

Table: schedule

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

|ward_code |varchar(9) |NOT NULL |

|

|-

|ward_name |varchar(255) |DEFAULT NULL |

|

|-

| colspan="5" | PRIMARY KEY (ward_code) |}