Remote Subscriber Database (RSD) Schema (Compass 1): Difference between revisions

Latest revision as of 16:17, 16 September 2020

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
id	smallint(6)	NOT NULL
value	varchar(50)	NOT NULL
PRIMARY KEY (id)

Table: date_precision

PRIMARY KEY (id)

Column name	Data type	Constraint
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_additional

Column name	Data type	Constraint	Comments
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	References
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	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	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	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
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	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
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
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	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_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	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_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
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
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	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_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
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	References
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
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	References
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organisation.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
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	References
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	References
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	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_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
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	References
id	bigint(20)	NOT NULL
organization_id	bigint(20)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	bigint(20)	NOT NULL	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times	patient.id
person_id	bigint(20)	NOT NULL	Unique individual across all organisations	person.id
encounter_id	bigint(20)	DEFAULT NULL		encounter.id
practitioner_id	bigint(20)	DEFAULT NULL	The clinician the activity is recorded against	practitioner.id
clinical_effective_date	date	DEFAULT NULL
date_precision_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
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
id	smallint(6)	NOT NULL
value	varchar(50)	NOT NULL
PRIMARY KEY (id)

Table: registration_status

PRIMARY KEY (id)

Column name	Data type	Constraint
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	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		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
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	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)

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

@@ Line 1: / Line 1: @@
 == Table: allergy_intolerance ==
+PRIMARY KEY (organization_id,person_id,id)
 {| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|+PRIMARY KEY (organization_id,person_id,id)
+!Column name
-|Column name
+!Data type
-|Data type
+!Constraint
-|Constraint
+!Comments
-|Comments
+!References
-|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
 |
 |-
@@ Line 126: / Line 116: @@
 | colspan="5" |
 |}
 == Table: appointment ==
@@ Line 135: / Line 123: @@
 PRIMARY KEY (organization_id,person_id,id)
-{| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 271: / Line 259: @@
 | colspan="5" | KEY appointment_patient_id (patient_id)
 |}
 == Table: appointment_status ==
 PRIMARY KEY (id)
-{| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 309: / Line 295: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: date_precision ==
 PRIMARY KEY (id)
-{| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 351: / Line 335: @@
 PRIMARY KEY (organization_id,person_id,id)
-{| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 498: / Line 482: @@
 | colspan="5" | KEY encounter_snomed_concept_id (snomed_concept_id)
 |}
-== Table: encounter_detail ==
-PRIMARY KEY (organization_id,person_id,id)
-{| class="wikitable" border="1"
+== Table: encounter_additional ==
-|Column name
+{| class="wikitable sortable mw-collapsible mw-collapsed"
-|Data type
+!Column name
-|Constraint
+!Data type
-|Comments
+!Constraint
-|References
+!Comments
+!References
 |-
-!id
+|id
-!bigint(20)
+|bigint
-!NOT NULL
+|NOT NULL
-!Same as the id column on the encounter table
+|Same as the id column on the  encounter table or encounter_event id column (sub encounters)
-!
+|
 |-
+|property_id
-!organization_id
+|varchar(255)
-!bigint(20)
+|NOT NULL
-!NOT NULL
+|IM reference (i.e.  Admission method)
-!Owning organisation (i.e. publisher)
+|
-!organization.id
 |-
+|value_id
-!patient_id
+|varchar(255)
-!bigint(20)
+|NOT NULL
-!NOT NULL
+|IM reference (i.e.  Emergency admission)
-!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
 |-
+| colspan="5" |PRIMARY KEY (id, property_id)
+|}
-!person_id
+== Table: encounter_detail ==
-!bigint(20)
+PRIMARY KEY (organization_id,person_id,id)
-!NOT NULL
-!Unique individual across all organisations
-!person.id
-|-
-!practitioner_id
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-!bigint(20)
+!Column name
-!DEFAULT NULL
+!Data type
-!The clinician the activity is recorded against
+!Constraint
-!practitioner.id
+!Comments
+!References
 |-
+|id
-!episode_of_care_id
+|bigint(20)
-!bigint(20)
+|NOT NULL
-!DEFAULT NULL
+|Same as the id column on the encounter table
-!
+|
-!
 |-
-!clinical_effective_date
+|organization_id
-!date
+|bigint(20)
-!DEFAULT NULL
+|NOT NULL
-!The date the clinical code is recorded for
+|Owning organisation (i.e. publisher)
-!
+|organization.id
 |-
-!date_precision_id
+|patient_id
-!smallint(6)
+|bigint(20)
-!DEFAULT NULL
+|NOT NULL
-!Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
+|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
 |-
-!recording_practitioner_id
+|person_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|NOT NULL
-!Who recorded the encounter
+|Unique individual across all organisations
-!
+|person.id
+|-
+|practitioner_id
+|bigint(20)
+|DEFAULT NULL
+|The clinician the activity is recorded against
+|practitioner.id
 |-
-!recording_date
+|episode_of_care_id
-!date
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
+|
-!
 |-
-!appointment_id
+|clinical_effective_date
-!bigint(20)
+|date
-!DEFAULT NULL
+|DEFAULT NULL
-!The unique reference for that appointment
+|The date the clinical code is recorded for
-!appointment.id
+|
-|-
-!service_provider_organization_id
-!bigint(20)
-!DEFAULT NULL
-!Organisation that performed the encounter
-!
 |-
-!location_id
+|date_precision_id
-!bigint(20)
+|smallint(6)
-!DEFAULT NULL
+|DEFAULT NULL
-!Where the encounter took place
+|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
-!location.id
+|
-|-
-!end_date
+|
-!date
-!DEFAULT NULL
-!
+|-
-!
+|recording_practitioner_id
+|bigint(20)
+|DEFAULT NULL
+|Who recorded the encounter
+|
 |-
-!duration_minutes
+|recording_date
-!int(11)
+|date
-!DEFAULT NULL
+|DEFAULT NULL
-!Duration always in minutes
+|
-!
+|
 |-
-!completion_status_concept_id
+|appointment_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|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
 |-
-!healthcare_service_type_concept_id
+|end_date
-!bigint(20)
+|date
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!interaction_mode_concept_id
+|duration_minutes
-!bigint(20)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|Duration always in minutes
+|
-!
 |-
-!administrative_action_concept_id
+|completion_status_concept_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!purpose_concept_id
+|healthcare_service_type_concept_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!disposition_concept_id
+|interaction_mode_concept_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!site_of_care_type_concept_id
+|administrative_action_concept_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!patient_status_concept_id
+|purpose_concept_id
-!bigint(20)
+|bigint(20)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-! colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|disposition_concept_id
-|-
+|bigint(20)
+|DEFAULT NULL
+|
-! colspan="5" | UNIQUE KEY ix_encounter_detail_id (id),
+|
-|-
-! colspan="5" | KEY ix_encounter_detail_patient_id (patient_id),
-|-
-! colspan="5" | KEY ix_encounter_detail_appointment_id (appointment_id),
 |-
-! colspan="5" | KEY ix_encounter_detail_patient_id_organization_id (patient_id,organization_id)
+|site_of_care_type_concept_id
-|}
+|bigint(20)
+|DEFAULT NULL
+|
-== Table: episode_of_care ==
+|
-PRIMARY KEY (organization_id,person_id,id)
-{| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|patient_status_concept_id
 |bigint(20)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 765: / Line 738: @@
 |-
-|organization_id
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
-|bigint(20)
-|NOT NULL
-|Owning organisation (i.e. publisher)
-|organization.id
 |-
-|patient_id
+| colspan="5" | UNIQUE KEY ix_encounter_detail_id (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
+| colspan="5" | KEY ix_encounter_detail_patient_id (patient_id),
-|bigint(20)
+|-
-|NOT NULL
-|Unique individual across all organisations
+| colspan="5" | KEY ix_encounter_detail_appointment_id (appointment_id),
-|person.id
 |-
-|registration_type_id
+| colspan="5" | KEY ix_encounter_detail_patient_id_organization_id (patient_id,organization_id)
-|smallint(6)
+|}
-|DEFAULT NULL
-|
+== Table: encounter_event ==
-|registration.type.id
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|date_registered
+|id
-|date
+|bigint(20)
-|DEFAULT NULL
+|NOT NULL
 |
 |
+|-
+|organization_id
+|bigint(20)
+|NOT NULL
+|Owning organisation (i.e. publisher)
+|organization.id
 |-
-|date_registered_end
+|patient_id
-|date
+|bigint(20)
-|DEFAULT NULL
+|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
 |-
-|usual_gp_practitioner_id
+|encounter_id
-|bigint(20)
+|bigint
-|DEFAULT NULL
+|NOT NULL
 |
+|registration.type.id
-|Practitioner.id
 |-
-|registration_status_id
+|practitioner_id
-|smallint(6)
+|bigint
 |DEFAULT NULL
-|
+|The clinician the activity is recorded against
+|practitioner.id
-|registration.status
 |-
-| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|appointment_id
-|-
+|bigint
+|DEFAULT NULL
+|
-| colspan="5" | UNIQUE KEY episode_of_care_id (id),
+|appointment.id
-|-
-| colspan="5" | KEY episode_of_care_patient_id (patient_id),
 |-
-| colspan="5" | KEY episode_of_care_registration_type_id (registration_type_id),
+|clinical_effective_date
-|-
+|datetime
+|DEFAULT NULL
+|
-| colspan="5" | KEY episode_of_care_date_registered (date_registered),
+|
 |-
-| colspan="5" | KEY episode_of_care_date_registered_end (date_registered_end),
+|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)
-| colspan="5" | KEY episode_of_care_person_id (person_id),
+|
 |-
+|snomed_concept_id
-| colspan="5" | KEY episode_of_care_organization_id (organization_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
+|
+|
 |-
-| colspan="5" | KEY reg_type_id_patient_id_date_registered_date_registered_end (registration_type_id,patient_id,date_registered,date_registered_end)
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
 |}
+== Table: episode_of_care ==
+PRIMARY KEY (organization_id,person_id,id)
-== Table: ethnicity_lookup ==
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
-PRIMARY KEY (ethnic_code)
+!Data type
-{| border="1"
+!Constraint
+!Comments
-|Column name
+!References
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|ethnic_code
+|id
-|char(1)
+|bigint(20)
 |NOT NULL
 |
@@ Line 886: / Line 907: @@
 |-
-|ethnic_name
+|organization_id
-|varchar(100)
+|bigint(20)
-|DEFAULT NULL
+|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
 |-
-| colspan="5" | PRIMARY KEY (ethnic_code)
+|date_registered
-|}
+|date
+|DEFAULT NULL
+|
-== Table: flag ==
+|
-PRIMARY KEY (organization_id,person_id,id)
-{| border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|date_registered_end
-|bigint(20)
+|date
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 924: / Line 959: @@
 |-
-|organization_id
+|usual_gp_practitioner_id
 |bigint(20)
-|NOT NULL
+|DEFAULT NULL
-|Owning organisation (i.e. publisher)
 |
+|practitioner.id
 |-
-|patient_id
+|registration_status_id
-|bigint(20)
+|smallint(6)
-|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
 |
-|
+|registration.status
+|-
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|-
+| colspan="5" | UNIQUE KEY episode_of_care_id (id),
 |-
-|date_precision_id
+| colspan="5" | KEY episode_of_care_patient_id (patient_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)
-|
+| colspan="5" | KEY episode_of_care_registration_type_id (registration_type_id),
+|-
+| colspan="5" | KEY episode_of_care_date_registered (date_registered),
 |-
-|is_active
+| colspan="5" | KEY episode_of_care_date_registered_end (date_registered_end),
-|tinyint(1)
+|-
+| colspan="5" | KEY episode_of_care_person_id (person_id),
+|-
+| colspan="5" | KEY episode_of_care_organization_id (organization_id),
+|-
+| colspan="5" | 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)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
+|-
+|ethnic_code
+|char(1)
 |NOT NULL
 |
@@ Line 978: / Line 1,026: @@
 |-
-|flag_text
+|ethnic_name
-|text
+|varchar(100)
-|
+|DEFAULT NULL
-|This is a warning set by the publisher regarding he patient
 |
-|-
+|
-| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
-|-
-| colspan="5" | UNIQUE KEY flag_id (id),
 |-
-| colspan="5" | KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
+| colspan="5" | PRIMARY KEY (ethnic_code)
-|-
+|}
+== Table: flag ==
-| colspan="5" | KEY fk_flag_date_precision (date_precision_id),
+PRIMARY KEY (organization_id,person_id,id)
-|-
-| colspan="5" | KEY flag_patient_id (patient_id),
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-| colspan="5" | CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
+|id
-|-
+|bigint(20)
+|NOT NULL
+|
-| colspan="5" | 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)
-{| border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|local_authority_code
+|organization_id
-|varchar(9)
+|bigint(20)
 |NOT NULL
+|Owning organisation (i.e. publisher)
 |
-|local.authority.code
 |-
-|local_authority_name
+|patient_id
-|varchar(255)
+|bigint(20)
-|DEFAULT NULL
+|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
-|
 |-
-| colspan="5" | PRIMARY KEY (local_authority_code)
+|person_id
-|}
+|bigint(20)
+|NOT NULL
+|Unique individual across all organisations
-== Table: location ==
+|person.id
-PRIMARY KEY (id)
-{| border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|effective_date
-|bigint(20)
+|date
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 1,068: / Line 1,096: @@
 |-
-|name
+|date_precision_id
-|varchar(255)
+|smallint(6)
 |DEFAULT NULL
-|The name of a location set by the publisher. E.g. ward, clinic, domiciliary
+|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
 |
@@ Line 1,077: / Line 1,105: @@
 |-
-|type_code
+|is_active
-|varchar(50)
+|tinyint(1)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 1,088: / Line 1,116: @@
 |-
-|type_desc
+|flag_text
-|varchar(255)
+|text
-|DEFAULT NULL
+|
+|This is a warning set by the publisher regarding he patient
 |
-|
+|-
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|-
+| colspan="5" | UNIQUE KEY flag_id (id),
+|-
+| colspan="5" | KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
+|-
+| colspan="5" | KEY fk_flag_date_precision (date_precision_id),
+|-
+| colspan="5" | KEY flag_patient_id (patient_id),
 |-
-|postcode
+| colspan="5" | CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
-|varchar(10)
+|-
-|DEFAULT NULL
-|
+| colspan="5" | 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)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
+|-
+|local_authority_code
+|varchar(9)
+|NOT NULL
 |
+|local.authority.code
 |-
-|managing_organization_id
+|local_authority_name
-|bigint(20)
+|varchar(255)
 |DEFAULT NULL
 |
@@ Line 1,121: / Line 1,178: @@
 |-
-| colspan="5" | PRIMARY KEY (id),
+| colspan="5" | PRIMARY KEY (local_authority_code)
-|-
+|}
+== Table: location ==
+PRIMARY KEY (id)
-| colspan="5" | UNIQUE KEY location_id (id),
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-| colspan="5" | KEY fk_location_managing_organisation_id (managing_organization_id)
+|id
-|}
+|bigint(20)
+|NOT NULL
+|
-== Table: Isoa_lookup ==
+|
-PRIMARY KEY (lsoa_code)
-{| border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-!lsoa_code
-!char(9)
-!NOT NULL
-!
+|name
-!
+|varchar(255)
+|DEFAULT NULL
+|The name of a location set by the publisher. E.g. ward, clinic, domiciliary
+|
 |-
-!lsoa_name
+|type_code
-!varchar(255)
+|varchar(50)
-!DEFAULT NULL
+|DEFAULT NULL
-!
+|
-!
+|
 |-
-!imd_score
+|type_desc
-!decimal(53)
+|varchar(255)
-!DEFAULT NULL
+|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
+|postcode
-!int(11)
+|varchar(10)
-!DEFAULT NULL
+|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
+|managing_organization_id
-!int(11)
+|bigint(20)
-!DEFAULT NULL
+|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)
-!
+|-
+| colspan="5" | PRIMARY KEY (id),
 |-
-!employment_score
+| colspan="5" | UNIQUE KEY location_id (id),
-!decimal(53)
-!DEFAULT NULL
-!Employment Score (rate)
-!
 |-
-!employment_rank
+| colspan="5" | KEY fk_location_managing_organisation_id (managing_organization_id)
-!int(11)
+|}
-!DEFAULT NULL
+== Table: Isoa_lookup ==
-!Employment Rank (where 1 is most deprived)
-!
+PRIMARY KEY (lsoa_code)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
+|lsoa_code
+|char(9)
+|NOT NULL
+|
-!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
+|lsoa_name
-!int(11)
+|varchar(255)
-!DEFAULT NULL
+|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
+|imd_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Health Deprivation and Disability Score
+|Index of Multiple Deprivation (IMD) Score
-!
+|
 |-
-!health_rank
+|imd_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Health Deprivation and Disability Rank (where 1 is most deprived)
+|Index of Multiple Deprivation (IMD) Rank (where 1 is most deprived)
-!
+|
 |-
-!health_decile
+|imd_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Health Deprivation and Disability Decile (where 1 is most deprived 10% of LSOAs)
+|Index of Multiple Deprivation (IMD) Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!crime_score
+|income_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Crime Score
+|Income Score (rate)
-!
+|
 |-
-!crime_rank
+|income_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Crime Rank (where 1 is most deprived)
+|Income Rank (where 1 is most deprived)
-!
+|
 |-
-!crime_decile
+|income_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Crime Decile (where 1 is most deprived 10% of LSOAs)
+|Income Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!housing_and_services_barriers_score
+|employment_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Barriers to Housing and Services Score
+|Employment Score (rate)
-!
+|
 |-
-!housing_and_services_barriers_rank
+|employment_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Barriers to Housing and Services Rank (where 1 is most deprived)
+|Employment Rank (where 1 is most deprived)
-!
+|
 |-
-!housing_and_services_barriers_decile
+|employment_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Barriers to Housing and Services Decile (where 1 is most deprived 10% of LSOAs)
+|Employment Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!living_environment_score
+|education_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Living Environment Score
+|Education Skills and Training Score
-!
+|
 |-
-!living_environment_rank
+|education_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Living Environment Rank (where 1 is most deprived)
+|Education Skills and Training Rank (where 1 is most deprived)
-!
+|
 |-
-!living_environment_decile
+|education_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Living Environment Decile (where 1 is most deprived 10% of LSOAs)
+|Education Skills and Training Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!idaci_score
+|health_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Children Index (IDACI) Score (rate)
+|Health Deprivation and Disability Score
-!
+|
 |-
-!idaci_rank
+|health_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Children Index (IDACI) Rank (where 1 is most deprived)
+|Health Deprivation and Disability Rank (where 1 is most deprived)
-!
+|
 |-
-!idaci_decile
+|health_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Children Index (IDACI) Decile (where 1 is most deprived 10% of LSOAs)
+|Health Deprivation and Disability Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!idaopi_score
+|crime_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Older People (IDAOPI) Score (rate)
+|Crime Score
-!
+|
 |-
-!idaopi_rank
+|crime_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Older People (IDAOPI) Rank (where 1 is most deprived)
+|Crime Rank (where 1 is most deprived)
-!
+|
 |-
-!idaopi_decile
+|crime_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Income Deprivation Affecting Older People (IDAOPI) Decile (where 1 is most deprived 10% of LSOAs)
+|Crime Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!children_and_young_sub_domain_score
+|housing_and_services_barriers_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Children and Young People Sub-domain Score
+|Barriers to Housing and Services Score
-!
+|
 |-
-!children_and_young_sub_domain_rank
+|housing_and_services_barriers_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Children and Young People Sub-domain Rank (where 1 is most deprived)
+|Barriers to Housing and Services Rank (where 1 is most deprived)
-!
+|
 |-
-!children_and_young_sub_domain_decile
+|housing_and_services_barriers_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Children and Young People Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Barriers to Housing and Services Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!adult_skills_sub_somain_score
+|living_environment_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Adult Skills Sub-domain Score
+|Living Environment Score
-!
+|
 |-
-!adult_skills_sub_somain_rank
+|living_environment_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Adult Skills Sub-domain Rank (where 1 is most deprived)
+|Living Environment Rank (where 1 is most deprived)
-!
+|
 |-
-!adult_skills_sub_somain_decile
+|living_environment_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Adult Skills Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Living Environment Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!geographical_barriers_sub_domain_score
+|idaci_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Geographical Barriers Sub-domain Score
+|Income Deprivation Affecting Children Index (IDACI) Score (rate)
-!
+|
 |-
-!geographical_barriers_sub_domain_rank
+|idaci_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Geographical Barriers Sub-domain Rank (where 1 is most deprived)
+|Income Deprivation Affecting Children Index (IDACI) Rank (where 1 is most deprived)
-!
+|
 |-
-!geographical_barriers_sub_domain_decile
+|idaci_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Geographical Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Income Deprivation Affecting Children Index (IDACI) Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!wider_barriers_sub_domain_score
+|idaopi_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Wider Barriers Sub-domain Score
+|Income Deprivation Affecting Older People (IDAOPI) Score (rate)
-!
+|
 |-
-!wider_barriers_sub_domain_rank
+|idaopi_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Wider Barriers Sub-domain Rank (where 1 is most deprived)
+|Income Deprivation Affecting Older People (IDAOPI) Rank (where 1 is most deprived)
-!
+|
 |-
-!wider_barriers_sub_domain_decile
+|idaopi_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Wider Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Income Deprivation Affecting Older People (IDAOPI) Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!indoors_sub_domain_score
+|children_and_young_sub_domain_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Indoors Sub-domain Score
+|Children and Young People Sub-domain Score
-!
+|
 |-
-!indoors_sub_domain_rank
+|children_and_young_sub_domain_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Indoors Sub-domain Rank (where 1 is most deprived)
+|Children and Young People Sub-domain Rank (where 1 is most deprived)
-!
+|
 |-
-!indoors_sub_domain_decile
+|children_and_young_sub_domain_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Indoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Children and Young People Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!outdoors_sub_domain_score
+|adult_skills_sub_somain_score
-!decimal(53)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Outdoors Sub-domain Score
+|Adult Skills Sub-domain Score
-!
+|
 |-
-!outdoors_sub_domain_rank
+|adult_skills_sub_somain_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Outdoors Sub-domain Rank (where 1 is most deprived)
+|Adult Skills Sub-domain Rank (where 1 is most deprived)
-!
+|
 |-
-!outdoors_sub_domain_decile
+|adult_skills_sub_somain_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Outdoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
+|Adult Skills Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!total_population
+|geographical_barriers_sub_domain_score
-!int(11)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Total population: mid 2012 (excluding prisoners)
+|Geographical Barriers Sub-domain Score
-!
+|
 |-
-!dependent_children_0_to_15
+|geographical_barriers_sub_domain_rank
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Dependent Children aged 0-15: mid 2012 (excluding prisoners)
+|Geographical Barriers Sub-domain Rank (where 1 is most deprived)
-!
+|
 |-
-!population_16_to_59
+|geographical_barriers_sub_domain_decile
-!int(11)
+|int(11)
-!DEFAULT NULL
+|DEFAULT NULL
-!Population aged 16-59: mid 2012 (excluding prisoners)
+|Geographical Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
-!
+|
 |-
-!older_population_60_and_over
+|wider_barriers_sub_domain_score
-!int(11)
+|decimal(53)
-!DEFAULT NULL
+|DEFAULT NULL
-!Older population aged 60 and over: mid 2012 (excluding prisoners)
+|Wider Barriers Sub-domain Score
-!
+|
 |-
-! colspan="5" | PRIMARY KEY (lsoa_code)
+|wider_barriers_sub_domain_rank
-|}
+|int(11)
-== Table: medication_order ==
+|DEFAULT NULL
+|Wider Barriers Sub-domain Rank (where 1 is most deprived)
+|
-PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|wider_barriers_sub_domain_decile
-|bigint(20)
+|int(11)
-|NOT NULL
+|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
 |
@@ Line 1,657: / Line 1,683: @@
 |-
-|organization_id
+|indoors_sub_domain_rank
-|bigint(20)
+|int(11)
-|NOT NULL
+|DEFAULT NULL
-|Owning organisation (i.e. publisher)
+|Indoors Sub-domain Rank (where 1 is most deprived)
-|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
+|indoors_sub_domain_decile
-|bigint(20)
+|int(11)
 |DEFAULT NULL
+|Indoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)
 |
-|encounter.id
 |-
-|practitioner_id
+|outdoors_sub_domain_score
-|bigint(20)
+|decimal(53)
 |DEFAULT NULL
-|The clinician the activity is recorded against
+|Outdoors Sub-domain Score
-|practitioner.id
+|
 |-
-|clinical_effective_date
+|outdoors_sub_domain_rank
-|date
+|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)
 |
@@ Line 1,705: / Line 1,728: @@
 |-
-|date_precision_id
+|total_population
-|smallint(6)
+|int(11)
 |DEFAULT NULL
-|Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
+|Total population: mid 2012 (excluding prisoners)
 |
@@ Line 1,714: / Line 1,737: @@
 |-
-|dmd_id
+|dependent_children_0_to_15
-|bigint(20)
+|int(11)
 |DEFAULT NULL
-|
+|Dependent Children aged 0-15: mid 2012 (excluding prisoners)
 |
@@ Line 1,725: / Line 1,746: @@
 |-
-|dose
+|population_16_to_59
-|varchar(1000)
+|int(11)
 |DEFAULT NULL
-|
+|Population aged 16-59: mid 2012 (excluding prisoners)
 |
@@ Line 1,736: / Line 1,755: @@
 |-
-|quantity_value
+|older_population_60_and_over
-|double
+|int(11)
 |DEFAULT NULL
+|Older population aged 60 and over: mid 2012 (excluding prisoners)
 |
-|
+|-
+! colspan="5" | PRIMARY KEY (lsoa_code)
+|}
+== Table: medication_order ==
+PRIMARY KEY (organization_id,person_id,id)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|quantity_unit
+|id
-|varchar(255)
+|bigint(20)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 1,758: / Line 1,789: @@
 |-
-|duration_days
+|organization_id
-|int(11)
+|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
 |-
-|estimated_cost
+|encounter_id
-|double
+|bigint(20)
 |DEFAULT NULL
 |
-|
+|encounter.id
+|-
+|practitioner_id
+|bigint(20)
+|DEFAULT NULL
+|The clinician the activity is recorded against
+|practitioner.id
 |-
-|medication_statement_id
+|clinical_effective_date
-|bigint(20)
+|date
 |DEFAULT NULL
 |
@@ Line 1,791: / Line 1,837: @@
 |-
-|original_term
+|date_precision_id
-|varchar(1000)
+|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
 |
-|-
+|
-| 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)
+|dose
-|-
+|varchar(1000)
+|DEFAULT NULL
+|
-| colspan="5" | KEY medication_order_dmd_id (dmd_id)
-|}
+|
-== Table: medication_statement ==
-PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|quantity_value
-|bigint(20)
+|double
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 1,838: / Line 1,879: @@
 |-
-|organization_id
+|quantity_unit
-|bigint(20)
+|varchar(255)
-|NOT NULL
+|DEFAULT NULL
-|Owning organisation (i.e. publisher)
+|
-|organization.id
+|
 |-
-|patient_id
+|duration_days
-|bigint(20)
+|int(11)
 |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
+|estimated_cost
-|bigint(20)
+|double
-|DEFAULT NULL
-|The clinician the activity is recorded against
-|practitioner.id
-|-
-|clinical_effective_date
-|date
 |DEFAULT NULL
 |
@@ Line 1,886: / Line 1,912: @@
 |-
-|date_precision_id
+|medication_statement_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
@@ Line 1,906: / Line 1,923: @@
 |-
-|is_active
+|original_term
-|tinyint(4)
+|varchar(1000)
 |DEFAULT NULL
 |
@@ Line 1,917: / Line 1,934: @@
 |-
-|cancellation_date
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id)
-|date
+|-
-|DEFAULT NULL
-|
+| 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)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|dose
+|id
-|varchar(1000)
+|bigint(20)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 1,939: / Line 1,968: @@
 |-
-|quantity_value
+|organization_id
-|double
+|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
 |-
-|quantity_unit
+|clinical_effective_date
-|varchar(255)
+|date
 |DEFAULT NULL
 |
@@ Line 1,961: / Line 2,016: @@
 |-
-|medication_statement_<br />authorisation_type_id
+|date_precision_id
 |smallint(6)
-|NOT NULL
+|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
 |
@@ Line 1,972: / Line 2,036: @@
 |-
-|original_term
+|is_active
-|varchar(1000)
+|tinyint(4)
 |DEFAULT NULL
 |
@@ Line 1,983: / Line 2,047: @@
 |-
-| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|cancellation_date
-|-
+|date
+|DEFAULT NULL
+|
-| 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)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|dose
-|smallint(6)
+|varchar(1000)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,017: / Line 2,069: @@
 |-
-|value
+|quantity_value
-|varchar(50)
+|double
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,028: / Line 2,080: @@
 |-
-| colspan="5" | PRIMARY KEY (id)
+|quantity_unit
-|}
+|varchar(255)
+|DEFAULT NULL
+|
-== Table: msoa_lookup ==
+|
-PRIMARY KEY (msoa_code)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|msoa_code char(9)
+|medication_statement_<br />authorisation_type_id
-|NOT NULL
+|smallint(6)
-|
+|NOT NULL
 |
@@ Line 2,057: / Line 2,102: @@
 |-
-|msoa_name
+|original_term
-|varchar(255)
+|varchar(1000)
 |DEFAULT NULL
 |
@@ Line 2,068: / Line 2,113: @@
 |-
-| colspan="5" | PRIMARY KEY (msoa_code)
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
-|}
+|-
+| colspan="5" | UNIQUE KEY medication_statement_id (id),
+|-
-== Table: observation ==
+| colspan="5" | KEY medication_statement_patient_id (patient_id),
+|-
-PRIMARY KEY (organization_id,person_id,id)
+| colspan="5" | KEY medication_statement_dmd_id (dmd_id)
-| class="wikitable" border="1"
+|}
-|Column name
+== Table: medication_statement_authorisation_type ==
-|Data type
-|Constraint
+PRIMARY KEY (id)
-|Comments
-|References
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
 |id
-|bigint(20)
+|smallint(6)
 |NOT NULL
 |
@@ Line 2,095: / Line 2,147: @@
 |-
-|organization_id
+|value
-|bigint(20)
+|varchar(50)
 |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
+| colspan="5" | PRIMARY KEY (id)
-|bigint(20)
+|}
-|DEFAULT NULL
+== Table: msoa_lookup ==
-|The clinician the activity is recorded against
-|practitioner.id
+PRIMARY KEY (msoa_code)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|clinical_effective_date
+|msoa_code char(9)
-|date
+|NOT NULL
-|DEFAULT NULL
 |
@@ Line 2,141: / Line 2,180: @@
-|-
-|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)
 |
@@ Line 2,152: / Line 2,185: @@
 |-
-|snomed_concept_id
+|msoa_name
-|bigint(20)
+|varchar(255)
 |DEFAULT NULL
 |
@@ Line 2,163: / Line 2,196: @@
 |-
-|result_value
+| colspan="5" | PRIMARY KEY (msoa_code)
-|double
+|}
-|DEFAULT NULL
+== Table: observation ==
-|
-|
+PRIMARY KEY (organization_id,person_id,id)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|result_value_units
+|id
-|varchar(50)
+|bigint(20)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 2,185: / Line 2,221: @@
 |-
-|result_date
+|organization_id
-|date
+|bigint(20)
-|DEFAULT NULL
+|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
 |-
-|result_text
+|encounter_id
-|text
+|bigint(20)
-|
+|DEFAULT NULL
 |
-|
+|encounter.id
+|-
+|practitioner_id
+|bigint(20)
+|DEFAULT NULL
+|The clinician the activity is recorded against
+|practitioner.id
 |-
-|result_concept_id
+|clinical_effective_date
-|bigint(20)
+|date
 |DEFAULT NULL
 |
@@ Line 2,218: / Line 2,269: @@
 |-
-|original_code
+|date_precision_id
-|varchar(20)
+|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)
 |
@@ Line 2,229: / Line 2,278: @@
 |-
-|is_problem
+|snomed_concept_id
-|tinyint(1)
+|bigint(20)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,240: / Line 2,289: @@
 |-
-|original_term
+|result_value
-|varchar(1000)
+|double
 |DEFAULT NULL
 |
@@ Line 2,251: / Line 2,300: @@
 |-
-|is_review
+|result_value_units
-|tinyint(1)
+|varchar(50)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,262: / Line 2,311: @@
 |-
-|problem_end_date
+|result_date
 |date
 |DEFAULT NULL
@@ Line 2,273: / Line 2,322: @@
 |-
-|parent_observation_id
+|result_text
-|bigint(20)
+|text
-|DEFAULT NULL
+|
 |
@@ Line 2,284: / Line 2,333: @@
 |-
-| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
+|result_concept_id
-|-
+|bigint(20)
+|DEFAULT NULL
+|
-| 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),
+|original_code
-|-
+|varchar(20)
+|DEFAULT NULL
+|
-| 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)
+|is_problem
-|}
+|tinyint(1)
+|NOT NULL
+|
-== Table: organization ==
+|
-PRIMARY KEY (id)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|original_term
-|bigint(20)
+|varchar(1000)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,338: / Line 2,377: @@
 |-
-|ods_code
+|is_review
-|varchar(50)
+|tinyint(1)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 2,349: / Line 2,388: @@
 |-
-|name
+|problem_end_date
-|varchar(255)
+|date
 |DEFAULT NULL
 |
@@ Line 2,360: / Line 2,399: @@
 |-
-|type_code
+|parent_observation_id
-|varchar(50)
+|bigint(20)
 |DEFAULT NULL
 |
@@ Line 2,371: / Line 2,410: @@
 |-
-|type_desc
+| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
-|varchar(255)
+|-
-|DEFAULT NULL
-|
+| 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),
 |-
-|postcode
+| colspan="5" | KEY observation_snomed_concept_id_value (snomed_concept_id,result_value),
-|varchar(10)
+|-
-|DEFAULT NULL
+| 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)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
+|-
+|id
+|bigint(20)
+|NOT NULL
 |
@@ Line 2,393: / Line 2,462: @@
 |-
-|parent_organization_id
+|ods_code
-|bigint(20)
+|varchar(50)
 |DEFAULT NULL
 |
@@ Line 2,404: / Line 2,473: @@
 |-
-|latitude
+|name
-|decimal(108)
+|varchar(255)
 |DEFAULT NULL
 |
@@ Line 2,415: / Line 2,484: @@
 |-
-|longitude
+|type_code
-|decimal(118)
+|varchar(50)
 |DEFAULT NULL
 |
@@ Line 2,426: / Line 2,495: @@
 |-
-| colspan="5" | PRIMARY KEY (id),
+|type_desc
-|-
+|varchar(255)
+|DEFAULT NULL
+|
+|
-| 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)
+|postcode
-|}
+|varchar(10)
+|DEFAULT NULL
+|
-== Table: patient ==
+|
-PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
-|Column name
-|Data type
-|Constraint
-|Comments
-|References
 |-
-|id
+|parent_organization_id
 |bigint(20)
-|NOT NULL
+|DEFAULT NULL
 |
@@ Line 2,462: / Line 2,528: @@
 |-
-|organization_id
+|latitude
-|bigint(20)
+|decimal(108)
-|NOT NULL
+|DEFAULT NULL
-|Owning organisation (i.e. publisher)
+|
-|organization.id
+|
 |-
-|person_id
+|longitude
-|bigint(20)
+|decimal(118)
-|NOT NULL
+|DEFAULT NULL
-|Unique individual across all organisations
-|person.id
-|-
-|patient_gender_id
-|smallint(6)
-|NOT NULL
 |
@@ Line 2,487: / Line 2,550: @@
 |-
-|pseudo_id
+| colspan="5" | PRIMARY KEY (id),
-|varchar(255)
+|-
-|DEFAULT NULL
-|
+| 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)
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
+!Column name
+!Data type
+!Constraint
+!Comments
+!References
 |-
-|age_years
+|id
-|int(11)
+|bigint(20)
-|DEFAULT NULL
+|NOT NULL
 |
@@ Line 2,509: / Line 2,584: @@
 |-
-|age_months
+|organization_id
-|int(11)
+|bigint(20)
-|DEFAULT NULL
+|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
 |
@@ Line 2,520: / Line 2,609: @@
 |-
-|age_weeks
+|pseudo_id
-|int(11)
+|varchar(255)
 |DEFAULT NULL
 |
@@ Line 2,531: / Line 2,620: @@
 |-
-|date_of_death
+|age_years
-|date
+|int(11)
 |DEFAULT NULL
 |
@@ Line 2,542: / Line 2,631: @@
 |-
-|postcode_prefix
+|age_months
-|varchar(20)
+|int(11)
 |DEFAULT NULL
-|First part of the postcode
+|
 |
@@ Line 2,551: / Line 2,642: @@
 |-
-|lsoa_code
+|age_weeks
-|varchar(50)
+|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
 |
@@ Line 2,643: / Line 2,765: @@
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 2,675: / Line 2,797: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: patient_uprn ==
 PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 2,877: / Line 2,997: @@
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,038: / Line 3,158: @@
 | colspan="5" | UNIQUE KEY person_id (id)
 |}
 == Table: practitioner ==
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,108: / Line 3,226: @@
 | colspan="5" | UNIQUE KEY practitioner_id (id)
 |}
 == Table: procedure_request ==
 PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,242: / Line 3,358: @@
 | colspan="5" | KEY procedure_request_patient_id (patient_id)
 |}
 == Table: procedure_request_status ==
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,280: / Line 3,394: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: referral_request ==
 PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,479: / Line 3,591: @@
 | colspan="5" | KEY referral_request_snomed_concept_id (snomed_concept_id)
 |}
 == Table: referral_request_priority ==
 PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,517: / Line 3,627: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: referral_request_type ==
 PRIMARY KEY (id)
-{| border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,555: / Line 3,663: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: registration_status ==
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,615: / Line 3,721: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: registration_status_history ==
 PRIMARY KEY (organization_id,person_id,id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,713: / Line 3,817: @@
 | colspan="5" | ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations';
 |}
 == Table: registration_type ==
 PRIMARY KEY (id)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,762: / Line 3,864: @@
 | colspan="5" | PRIMARY KEY (id)
 |}
 == Table: schedule ==
 PRIMARY KEY (organization_id,id)
-{| border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-
@@ Line 3,839: / Line 3,939: @@
 | colspan="5" | UNIQUE KEY schedule_id (id)
 |}
 == Table: ward_lookup ==
 PRIMARY KEY (ward_code)
-| class="wikitable" border="1"
+{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
-|Column name
+!Column name
-|Data type
+!Data type
-|Constraint
+!Constraint
-|Comments
+!Comments
-|References
+!References
 |-