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

From Endeavour Knowledge Base
No edit summary
(Added encounter_additional & encounter_event tables)
 
(11 intermediate revisions by one other user not shown)
Line 1: Line 1:


== Table: allergy_intolerance ==
== Table: allergy_intolerance ==
PRIMARY KEY (organization_id,person_id,id)
PRIMARY KEY (organization_id,person_id,id)
 
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
{| class="wikitable" border="1"
!Column name
|Column name
!Data type
|Data type
!Constraint
|Constraint
!Comments
|Comments
!References
|References
|-
|-


|id  
|id
|bigint(20)  
|bigint(20)
|NOT NULL  
|NOT NULL
|
|


|
|


|-
|-


|organization_id  
|organization_id
|bigint(20)  
|bigint(20)
|NOT NULL  
|NOT NULL
|Owning organisation (i.e. publisher)
|Owning organisation (i.e. publisher)
|organization.id
|organization.id
|-
|-


|patient_id  
|patient_id
|bigint(20)  
|bigint(20)
|NOT 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
|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
|patient.id
|-
|-


|person_id  
|person_id
|bigint(20)  
|bigint(20)
|NOT NULL  
|NOT NULL
|Unique individual across all organisations  
|Unique individual across all organisations
|person.id
|person.id
|-
|-


|encounter_id  
|encounter_id
|bigint(20)  
|bigint(20)
|DEFAULT NULL  
|DEFAULT NULL
|
|


|encounter.id
|encounter.id
|-
|-


|practitioner_id  
|practitioner_id
|bigint(20)  
|bigint(20)
|DEFAULT NULL  
|DEFAULT NULL
|The clinician the activity is recorded against
|The clinician the activity is recorded against
|practitioner.id
|practitioner.id
|-
|-


|clinical_effective_date  
|clinical_effective_date
|date  
|date
|DEFAULT NULL  
|DEFAULT NULL
|The date the clinical code is recorded for
|The date the clinical code is recorded for
|
|


|-
|-


|date_precision_id  
|date_precision_id
|smallint(6)  
|smallint(6)
|DEFAULT 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)
|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  
|snomed_concept_id
|bigint(20)  
|bigint(20)
|DEFAULT NULL  
|DEFAULT NULL
|The SNOMED code
|The SNOMED code
|
|


|-
|-


|original_code  
|original_code
|varchar(100) CHARACTER SET utf8 COLLATE utf8_bin  
|varchar(100) CHARACTER SET utf8 COLLATE utf8_bin
|DEFAULT NULL  
|DEFAULT NULL
|The original code from the source system
|The original code from the source system
|
|


|-
|-


|original_term  
|original_term
|varchar(1000)  
|varchar(1000)
|DEFAULT NULL  
|DEFAULT NULL
|The original code term from the source system
|The original code term from the source system
|
|


|-
|-


|is_review
|is_review
|tinyint(1)  
|tinyint(1)
|NOT NULL  
|NOT NULL
|Is this instance of the code a review of a previous encounter
|Is this instance of the code a review of a previous encounter
|
|


|-
|-
Line 127: Line 116:


| colspan="5" |  
| colspan="5" |  


|}
|}


== Table: appointment ==
== Table: appointment ==
Line 136: Line 123:
PRIMARY KEY (organization_id,person_id,id)
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 272: Line 259:
| colspan="5" | KEY appointment_patient_id (patient_id)
| colspan="5" | KEY appointment_patient_id (patient_id)
|}
|}
== Table: appointment_status ==
== Table: appointment_status ==


PRIMARY KEY (id)
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 310: Line 295:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: date_precision ==
== Table: date_precision ==


PRIMARY KEY (id)
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 352: Line 335:
PRIMARY KEY (organization_id,person_id,id)
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 499: Line 482:
| colspan="5" | KEY encounter_snomed_concept_id (snomed_concept_id)
| colspan="5" | KEY encounter_snomed_concept_id (snomed_concept_id)
|}
|}
== Table: encounter_detail ==


PRIMARY KEY (organization_id,person_id,id)
== Table: encounter_additional ==
 
{| class="wikitable sortable mw-collapsible mw-collapsed"
{| class="wikitable" border="1"
!Column name
|Column name
!Data type
|Data type
!Constraint
|Constraint
!Comments
|Comments
!References
|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
|bigint(20)
|NOT NULL
|Same as the id column on the encounter table
|


!episode_of_care_id
!bigint(20)
!DEFAULT NULL
!
!


|-


|organization_id
|bigint(20)
|NOT NULL
|Owning organisation (i.e. publisher)
|organization.id
|-
|-


!clinical_effective_date
|patient_id
!date
|bigint(20)
!DEFAULT NULL  
|NOT NULL  
!The date the clinical code is recorded for
|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
 
 
|-
|-


!date_precision_id
|person_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)
|Unique individual across all organisations
!
|person.id
 
 
|-
|-


!recording_practitioner_id
|practitioner_id
!bigint(20)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!Who recorded the encounter
|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
!
 
 
!


|


|-
|-


!duration_minutes
|recording_practitioner_id
!int(11)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!Duration always in minutes
|Who recorded the encounter
!
|




|-
|-


!completion_status_concept_id
|recording_date
!bigint(20)
|date
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|
 


|-
|-


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


!interaction_mode_concept_id
|end_date
!bigint(20)
|date
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|




|-
|-


!administrative_action_concept_id
|duration_minutes
!bigint(20)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!
|Duration always in minutes
 
|
 
!




|-
|-


!purpose_concept_id
|completion_status_concept_id
!bigint(20)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|




|-
|-


!disposition_concept_id
|healthcare_service_type_concept_id
!bigint(20)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|




|-
|-


!site_of_care_type_concept_id
|interaction_mode_concept_id
!bigint(20)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|




|-
|-


!patient_status_concept_id
|administrative_action_concept_id
!bigint(20)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!
|




!
|




|-
|-


! colspan="5" | PRIMARY KEY (organization_id,person_id,id),
|purpose_concept_id
|bigint(20)  
|DEFAULT NULL
|
 
 
|
 
 
|-
|-


! colspan="5" | UNIQUE KEY ix_encounter_detail_id (id),
|disposition_concept_id
|-
|bigint(20)  
|DEFAULT NULL
|
 
 
|


! 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)  
|bigint(20)  
|NOT NULL  
|DEFAULT NULL  
|
|


Line 766: 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
|person.id
|-
|-


|registration_type_id
| colspan="5" | KEY ix_encounter_detail_appointment_id (appointment_id),
|smallint(6)  
|-
|DEFAULT NULL
|


| colspan="5" | KEY ix_encounter_detail_patient_id_organization_id (patient_id,organization_id)
|}


|registration.type.id
== Table: encounter_event ==
{| 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
|-


|encounter_id
|bigint
|NOT NULL
|
|


 
|registration.type.id
|-
|-


|usual_gp_practitioner_id
|practitioner_id
|bigint(20)
|bigint
|DEFAULT NULL  
|DEFAULT NULL
|
|The clinician the activity is recorded against


|practitioner.id


|Practitioner.id
|-
|-


|registration_status_id
|appointment_id
|smallint(6)
|bigint
|DEFAULT NULL  
|DEFAULT NULL
|
|


|appointment.id


|registration.status
|-
|-


| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
|clinical_effective_date
|-
|datetime
|DEFAULT NULL
|


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


| colspan="5" | KEY episode_of_care_patient_id (patient_id),
|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_registration_type_id (registration_type_id),
|
|-
|-
 
|snomed_concept_id
| colspan="5" | KEY episode_of_care_date_registered (date_registered),
|bigint
|DEFAULT NULL
|
|
|-
|-
 
|original_code
| colspan="5" | KEY episode_of_care_date_registered_end (date_registered_end),
|varchar(100) binary
|DEFAULT NULL
|
|
|-
|-
 
|original_term
| colspan="5" | KEY episode_of_care_person_id (person_id),
|varchar(1000)
|DEFAULT NULL
|
|
|-
|-
 
|episode_of_care_id
| colspan="5" | KEY episode_of_care_organization_id (organization_id),
|bigint
|DEFAULT NULL
|
|episode.of.care.id
|-
|-
 
|service_provider_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)
|bigint
|}
|DEFAULT NULL
 
|
 
|
== Table: ethnicity_lookup ==
 
PRIMARY KEY (ethnic_code)
{| border="1"
 
|Column name
|Data type
|Constraint
|Comments
|References
|-
|-
 
|date_recorded
|ethnic_code
|datetime
|char(1)
|DEFAULT NULL
|NOT NULL  
|
|
|
|
|-
|-
 
|location_id
|ethnic_name
|bigint
|varchar(100)
|DEFAULT NULL
|DEFAULT NULL  
|Where the encounter took place
|location.id
|-
|finished
|boolean
|DEFAULT NULL
|
|
|
|
|-
|-


| colspan="5" | PRIMARY KEY (ethnic_code)
| colspan="5" | PRIMARY KEY (organization_id,person_id,id),
|}
|}
== Table: episode_of_care ==


0PRIMARY KEY (organization_id,person_id,id)


== Table: flag ==
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
 
!Column name
PRIMARY KEY (organization_id,person_id,id)
!Data type
 
!Constraint
{| border="1"
!Comments
|Column name
!References
|Data type
|Constraint
|Comments
|References
|-
|-


Line 929: Line 911:
|NOT NULL  
|NOT NULL  
|Owning organisation (i.e. publisher)
|Owning organisation (i.e. publisher)
|
|organization.id
 
 
|-
|-


Line 948: Line 928:
|-
|-


|effective_date
|registration_type_id
|smallint(6)
|DEFAULT NULL
|
 
 
|registration.type.id
|-
 
|date_registered
|date  
|date  
|DEFAULT NULL  
|DEFAULT NULL  
Line 959: Line 948:
|-
|-


|date_precision_id
|date_registered_end
|smallint(6)
|date
|DEFAULT 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)
|
|




|-
|


|is_active
|tinyint(1)
|NOT NULL
|


|-


|usual_gp_practitioner_id
|bigint(20)
|DEFAULT NULL
|
|




|practitioner.id
|-
|-


|flag_text
|registration_status_id
|text
|smallint(6)
| 
|DEFAULT NULL
|This is a warning set by the publisher regarding he patient
|
|




|registration.status
|-
|-


Line 991: Line 980:
|-
|-


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


| colspan="5" | KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
| colspan="5" | KEY episode_of_care_patient_id (patient_id),
|-
|-


| colspan="5" | KEY fk_flag_date_precision (date_precision_id),
| colspan="5" | KEY episode_of_care_registration_type_id (registration_type_id),
|-
|-


| colspan="5" | KEY flag_patient_id (patient_id),
| colspan="5" | KEY episode_of_care_date_registered (date_registered),
|-
|-


| colspan="5" | CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
| colspan="5" | KEY episode_of_care_date_registered_end (date_registered_end),
|-
|-


| colspan="5" | CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) REFERENCES patient (id, organization_id)
| colspan="5" | KEY episode_of_care_person_id (person_id),
|}
|-


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


== Table: local_authority_lookup ==
| 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 (local_authority_code)
PRIMARY KEY (ethnic_code)
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"


{| border="1"
!Column name
|Column name
!Data type
|Data type
!Constraint
|Constraint
!Comments
|Comments
!References
|References
|-
|-


|local_authority_code
|ethnic_code
|varchar(9)  
|char(1)  
|NOT NULL  
|NOT NULL  
|
|




|local.authority.code
|
 
 
|-
|-


|local_authority_name
|ethnic_name
|varchar(255)  
|varchar(100)  
|DEFAULT NULL  
|DEFAULT NULL  
|
|
Line 1,042: Line 1,037:
|-
|-


| colspan="5" | PRIMARY KEY (local_authority_code)
| colspan="5" | PRIMARY KEY (ethnic_code)
|}
|}
== Table: flag ==


PRIMARY KEY (organization_id,person_id,id)


== Table: location ==
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
 
!Column name
PRIMARY KEY (id)
!Data type
 
!Constraint
{| border="1"
!Comments
|Column name
!References
|Data type
|Constraint
|Comments
|References
|-
|-


Line 1,069: Line 1,062:
|-
|-


|name
|organization_id
|varchar(255)  
|bigint(20)  
|DEFAULT NULL  
|NOT NULL  
|The name of a location set by the publisher. E.g. ward, clinic, domiciliary
|Owning organisation (i.e. publisher)
|
|


Line 1,078: Line 1,071:
|-
|-


|type_code
|patient_id
|varchar(50)  
|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  
|DEFAULT NULL  
|
|
Line 1,089: Line 1,096:
|-
|-


|type_desc
|date_precision_id
|varchar(255)  
|smallint(6)  
|DEFAULT 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)
 
 
|
|


Line 1,100: Line 1,105:
|-
|-


|postcode
|is_active
|varchar(10)  
|tinyint(1)  
|DEFAULT NULL  
|NOT NULL  
|
|


Line 1,111: Line 1,116:
|-
|-


|managing_organization_id
|flag_text
|bigint(20)
|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" | PRIMARY KEY (id),
| colspan="5" | KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
|-
|-


| colspan="5" | UNIQUE KEY location_id (id),
| colspan="5" | KEY fk_flag_date_precision (date_precision_id),
|-
|-


| colspan="5" | KEY fk_location_managing_organisation_id (managing_organization_id)
| colspan="5" | KEY flag_patient_id (patient_id),
|}
|-


 
| colspan="5" | CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
== Table: Isoa_lookup ==
 
PRIMARY KEY (lsoa_code)
 
{| border="1"
|Column name
|Data type
|Constraint
|Comments
|References
|-
|-
!lsoa_code
!char(9)
!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)


{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
!Column name
!Data type
!Constraint
!Comments
!References
|-
|-


!lsoa_name
|local_authority_code
!varchar(255)  
|varchar(9)  
!DEFAULT NULL  
|NOT NULL  
!
|
 
 
!




|local.authority.code
|-
|-


!imd_score
|local_authority_name
!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
| colspan="5" | PRIMARY KEY (local_authority_code)
!int(11)  
|}
!DEFAULT NULL
== Table: location ==
!Index of Multiple Deprivation (IMD) Decile (where 1 is most deprived 10% of LSOAs)
!


PRIMARY KEY (id)


{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
!Column name
!Data type
!Constraint
!Comments
!References
|-
|-


!income_score
|id
!decimal(53)  
|bigint(20)  
!DEFAULT NULL  
|NOT NULL  
!Income Score (rate)
|
!




|-
|
 
!income_rank
!int(11)
!DEFAULT NULL
!Income Rank (where 1 is most deprived)
!




|-
|-


!income_decile
|name
!int(11)  
|varchar(255)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Decile (where 1 is most deprived 10% of LSOAs)
|The name of a location set by the publisher. E.g. ward, clinic, domiciliary
!
|




|-
|-


!employment_score
|type_code
!decimal(53)  
|varchar(50)  
!DEFAULT NULL  
|DEFAULT NULL  
!Employment Score (rate)
|
!




|-
|
 
!employment_rank
!int(11)
!DEFAULT NULL
!Employment Rank (where 1 is most deprived)
!




|-
|-


!employment_decile
|type_desc
!int(11)  
|varchar(255)  
!DEFAULT NULL  
|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
|postcode
!int(11)  
|varchar(10)  
!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
|managing_organization_id
!decimal(53)  
|bigint(20)  
!DEFAULT NULL  
|DEFAULT NULL  
!Health Deprivation and Disability Score
|
!




|-
|


!health_rank
!int(11)
!DEFAULT NULL
!Health Deprivation and Disability Rank (where 1 is most deprived)
!


|-


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


!health_decile
| colspan="5" | UNIQUE KEY location_id (id),
!int(11)  
!DEFAULT NULL
!Health Deprivation and Disability Decile (where 1 is most deprived 10% of LSOAs)
!
 
 
|-
|-


!crime_score
| colspan="5" | KEY fk_location_managing_organisation_id (managing_organization_id)
!decimal(53)  
|}
!DEFAULT NULL
== Table: Isoa_lookup ==
!Crime Score
!


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
|


!crime_rank
 
!int(11)
|
!DEFAULT NULL
!Crime Rank (where 1 is most deprived)
!




|-
|-


!crime_decile
|lsoa_name
!int(11)  
|varchar(255)  
!DEFAULT NULL  
|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
|imd_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Barriers to Housing and Services Rank (where 1 is most deprived)  
|Index of Multiple Deprivation (IMD) Score
!
|




|-
|-


!housing_and_services_barriers_decile
|imd_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Barriers to Housing and Services Decile (where 1 is most deprived 10% of LSOAs)  
|Index of Multiple Deprivation (IMD) Rank (where 1 is most deprived)  
!
|




|-
|-


!living_environment_score
|imd_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Living Environment Score
|Index of Multiple Deprivation (IMD) Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!living_environment_rank
|income_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Living Environment Rank (where 1 is most deprived)  
|Income Score (rate)  
!
|




|-
|-


!living_environment_decile
|income_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Living Environment Decile (where 1 is most deprived 10% of LSOAs)  
|Income Rank (where 1 is most deprived)  
!
|




|-
|-


!idaci_score
|income_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Children Index (IDACI) Score (rate)  
|Income Decile (where 1 is most deprived 10% of LSOAs)  
!
|




|-
|-


!idaci_rank
|employment_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Children Index (IDACI) Rank (where 1 is most deprived)  
|Employment Score (rate)  
!
|




|-
|-


!idaci_decile
|employment_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Children Index (IDACI) Decile (where 1 is most deprived 10% of LSOAs)  
|Employment Rank (where 1 is most deprived)  
!
|




|-
|-


!idaopi_score
|employment_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Older People (IDAOPI) Score (rate)  
|Employment Decile (where 1 is most deprived 10% of LSOAs)  
!
|




|-
|-


!idaopi_rank
|education_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Older People (IDAOPI) Rank (where 1 is most deprived)
|Education Skills and Training Score
!
|




|-
|-


!idaopi_decile
|education_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Income Deprivation Affecting Older People (IDAOPI) Decile (where 1 is most deprived 10% of LSOAs)  
|Education Skills and Training Rank (where 1 is most deprived)  
!
|




|-
|-


!children_and_young_sub_domain_score
|education_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Children and Young People Sub-domain Score
|Education Skills and Training Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!children_and_young_sub_domain_rank
|health_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Children and Young People Sub-domain Rank (where 1 is most deprived)
|Health Deprivation and Disability Score
!
|
 


|-
|-


!children_and_young_sub_domain_decile
|health_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Children and Young People Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|Health Deprivation and Disability Rank (where 1 is most deprived)  
!
|




|-
|-


!adult_skills_sub_somain_score
|health_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Adult Skills Sub-domain Score
|Health Deprivation and Disability Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!adult_skills_sub_somain_rank
|crime_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Adult Skills Sub-domain Rank (where 1 is most deprived)
|Crime Score
!
|




|-
|-


!adult_skills_sub_somain_decile
|crime_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Adult Skills Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|Crime Rank (where 1 is most deprived)  
!
|




|-
|-


!geographical_barriers_sub_domain_score
|crime_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Geographical Barriers Sub-domain Score
|Crime Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!geographical_barriers_sub_domain_rank
|housing_and_services_barriers_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Geographical Barriers Sub-domain Rank (where 1 is most deprived)
|Barriers to Housing and Services Score
!
|




|-
|-


!geographical_barriers_sub_domain_decile
|housing_and_services_barriers_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Geographical Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|Barriers to Housing and Services Rank (where 1 is most deprived)  
!
|




|-
|-


!wider_barriers_sub_domain_score
|housing_and_services_barriers_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Wider Barriers Sub-domain Score
|Barriers to Housing and Services Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!wider_barriers_sub_domain_rank
|living_environment_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Wider Barriers Sub-domain Rank (where 1 is most deprived)
|Living Environment Score
!
|




|-
|-


!wider_barriers_sub_domain_decile
|living_environment_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Wider Barriers Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|Living Environment Rank (where 1 is most deprived)  
!
|




|-
|-


!indoors_sub_domain_score
|living_environment_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Indoors Sub-domain Score
|Living Environment Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!indoors_sub_domain_rank
|idaci_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Indoors Sub-domain Rank (where 1 is most deprived)  
|Income Deprivation Affecting Children Index (IDACI) Score (rate)  
!
|




|-
|-


!indoors_sub_domain_decile
|idaci_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Indoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|Income Deprivation Affecting Children Index (IDACI) Rank (where 1 is most deprived)  
!
|




|-
|-


!outdoors_sub_domain_score
|idaci_decile
!decimal(53)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Outdoors Sub-domain Score
|Income Deprivation Affecting Children Index (IDACI) Decile (where 1 is most deprived 10% of LSOAs)
!
|




|-
|-


!outdoors_sub_domain_rank
|idaopi_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Outdoors Sub-domain Rank (where 1 is most deprived)  
|Income Deprivation Affecting Older People (IDAOPI) Score (rate)  
!
|




|-
|-


!outdoors_sub_domain_decile
|idaopi_rank
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Outdoors Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
|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)
|
 


|-
|-


!total_population
|children_and_young_sub_domain_decile
!int(11)  
|int(11)  
!DEFAULT NULL  
|DEFAULT NULL  
!Total population: mid 2012 (excluding prisoners)  
|Children and Young People Sub-domain Decile (where 1 is most deprived 10% of LSOAs)  
!
|




|-
|-


!dependent_children_0_to_15
|adult_skills_sub_somain_score
!int(11)  
|decimal(53)  
!DEFAULT NULL  
|DEFAULT NULL  
!Dependent Children aged 0-15: mid 2012 (excluding prisoners)
|Adult Skills Sub-domain Score
!
|




|-
|-


!population_16_to_59  
|adult_skills_sub_somain_rank
!int(11)  
|int(11)
!DEFAULT NULL  
|DEFAULT NULL
!Population aged 16-59: mid 2012 (excluding prisoners)  
|Adult Skills Sub-domain Rank (where 1 is most deprived)
!
|
 
 
 
 
|-
|-
 
 
!older_population_60_and_over  
|adult_skills_sub_somain_decile
!int(11)  
|int(11)
!DEFAULT NULL  
|DEFAULT NULL
!Older population aged 60 and over: mid 2012 (excluding prisoners)  
|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)  
|




Line 1,639: Line 1,770:
PRIMARY KEY (organization_id,person_id,id)
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 1,814: Line 1,945:
| colspan="5" | KEY medication_order_dmd_id (dmd_id)
| colspan="5" | KEY medication_order_dmd_id (dmd_id)
|}
|}
== Table: medication_statement ==
== Table: medication_statement ==


PRIMARY KEY (organization_id,person_id,id)
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 1,999: Line 2,128:
PRIMARY KEY (id)
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,031: Line 2,160:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: msoa_lookup ==
== Table: msoa_lookup ==


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


Line 2,071: Line 2,198:
| colspan="5" | PRIMARY KEY (msoa_code) 
| colspan="5" | PRIMARY KEY (msoa_code) 
|}
|}
== Table: observation ==
== Table: observation ==


PRIMARY KEY (organization_id,person_id,id)
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,314: Line 2,439:
| colspan="5" | KEY observation_original_code (original_code)
| colspan="5" | KEY observation_original_code (original_code)
|}
|}
== Table: organization ==
== Table: organization ==


PRIMARY KEY (id)
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,438: Line 2,561:
| colspan="5" | KEY organization_id_parent_organization_id (id,parent_organization_id)
| colspan="5" | KEY organization_id_parent_organization_id (id,parent_organization_id)
|}
|}
== Table: patient ==
== Table: patient ==


PRIMARY KEY (organization_id,person_id,id)
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,644: Line 2,765:
PRIMARY KEY (id)
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,676: Line 2,797:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: patient_uprn ==
== Table: patient_uprn ==


PRIMARY KEY (organization_id,person_id,id)
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,878: Line 2,997:
PRIMARY KEY (id)
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,039: Line 3,158:
| colspan="5" | UNIQUE KEY person_id (id)
| colspan="5" | UNIQUE KEY person_id (id)
|}
|}
== Table: practitioner ==
== Table: practitioner ==


PRIMARY KEY (id)
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,109: Line 3,226:
| colspan="5" | UNIQUE KEY practitioner_id (id)
| colspan="5" | UNIQUE KEY practitioner_id (id)
|}
|}
== Table: procedure_request ==
== Table: procedure_request ==


PRIMARY KEY (organization_id,person_id,id)
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,243: Line 3,358:
| colspan="5" | KEY procedure_request_patient_id (patient_id)
| colspan="5" | KEY procedure_request_patient_id (patient_id)
|}
|}
== Table: procedure_request_status ==
== Table: procedure_request_status ==


PRIMARY KEY (id)
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,281: Line 3,394:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: referral_request ==
== Table: referral_request ==


PRIMARY KEY (organization_id,person_id,id)
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,480: Line 3,591:
| colspan="5" | KEY referral_request_snomed_concept_id (snomed_concept_id)
| colspan="5" | KEY referral_request_snomed_concept_id (snomed_concept_id)
|}
|}
== Table: referral_request_priority ==
== Table: referral_request_priority ==


PRIMARY KEY (organization_id,person_id,id)
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,518: Line 3,627:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: referral_request_type ==
== Table: referral_request_type ==


PRIMARY KEY (id)
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,556: Line 3,663:
| colspan="5" | PRIMARY KEY (id) 
| colspan="5" | PRIMARY KEY (id) 
|}
|}
== Table: registration_status ==
== Table: registration_status ==


PRIMARY KEY (id)
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,616: Line 3,721:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: registration_status_history ==
== Table: registration_status_history ==


PRIMARY KEY (organization_id,person_id,id)
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,714: Line 3,817:
| colspan="5" | ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations';
| colspan="5" | ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations';
|}
|}
== Table: registration_type ==
== Table: registration_type ==


PRIMARY KEY (id)
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,763: Line 3,864:
| colspan="5" | PRIMARY KEY (id)
| colspan="5" | PRIMARY KEY (id)
|}
|}
== Table: schedule ==
== Table: schedule ==


PRIMARY KEY (organization_id,id)
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,840: Line 3,939:
| colspan="5" | UNIQUE KEY schedule_id (id)
| colspan="5" | UNIQUE KEY schedule_id (id)
|}
|}
== Table: ward_lookup ==
== Table: ward_lookup ==


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


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

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

Column name Data type Constraint Comments References
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 Comments References
id smallint(6) NOT NULL



value varchar(50) NOT NULL



PRIMARY KEY (id)

Table: msoa_lookup

PRIMARY KEY (msoa_code)

Column name Data type Constraint Comments References
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 Comments References
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 Comments References
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 Comments 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 Comments References
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 Comments References
id smallint(6) NOT NULL



value varchar(50) NOT NULL



PRIMARY KEY (id)

Table: referral_request_type

PRIMARY KEY (id)

Column name Data type Constraint Comments References
id smallint(6) NOT NULL



value varchar(50) NOT NULL



PRIMARY KEY (id) 

Table: registration_status

PRIMARY KEY (id)

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



ward_name varchar(255) DEFAULT NULL



PRIMARY KEY (ward_code)
Retrieved from ‘https://wiki.endeavourhealth.org/index.php?title=Remote_Subscriber_Database_(RSD)_Schema_(Compass_1)&oldid=2703’
Last modified
This page was last modified on 16 September 2020, at 16:17.