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

From Discovery Data Service
Jump to navigation Jump to search
No edit summary
Line 1: Line 1:


== Table: allergy_intolerance ==
== Table: allergy_intolerance ==
 
PRIMARY KEY (organization_id,person_id,id)
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
|+PRIMARY KEY (organization_id,person_id,id)
|Column name
|Column name
|Data type
|Data type
Line 11: Line 10:
|-
|-


|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 126: Line 116:


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


|}
|}
Line 135: Line 124:
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
Line 277: Line 266:
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
Line 315: Line 304:
PRIMARY KEY (id)
PRIMARY KEY (id)


{| class="wikitable" border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
|Column name
|Data type
|Data type
|Constraint
|Constraint
Line 351: Line 339:
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
Line 502: Line 490:
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
Line 746: Line 734:
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
Line 866: Line 854:


PRIMARY KEY (ethnic_code)
PRIMARY KEY (ethnic_code)
{| border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"


|Column name
|Column name
Line 905: Line 893:
PRIMARY KEY (organization_id,person_id,id)
PRIMARY KEY (organization_id,person_id,id)


{| border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
|Column name
|Column name
|Data type
|Data type
Line 1,013: Line 1,001:
PRIMARY KEY (local_authority_code)
PRIMARY KEY (local_authority_code)


{| border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
|Column name
|Column name
|Data type
|Data type
Line 1,049: Line 1,037:
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
Line 1,135: Line 1,123:
PRIMARY KEY (lsoa_code)
PRIMARY KEY (lsoa_code)


{| border="1"
{| class="wikitable sortable mw-collapsible mw-collapsed" border="1"
|Column name
|Column name
|Data type
|Data type
Line 1,638: Line 1,626:
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
Line 1,819: Line 1,807:
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
Line 1,998: Line 1,986:
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
Line 2,036: Line 2,024:
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
Line 2,076: Line 2,064:
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
Line 2,319: Line 2,307:
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
Line 2,443: Line 2,431:
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
Line 2,643: Line 2,631:
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
Line 2,681: Line 2,669:
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
Line 2,877: Line 2,865:
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
Line 3,044: Line 3,032:
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
Line 3,114: Line 3,102:
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
Line 3,248: Line 3,236:
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
Line 3,286: Line 3,274:
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
Line 3,485: Line 3,473:
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
Line 3,523: Line 3,511:
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
Line 3,561: Line 3,549:
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
Line 3,621: Line 3,609:
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
Line 3,719: Line 3,707:
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
Line 3,768: Line 3,756:
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
Line 3,845: Line 3,833:
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

Revision as of 10:58, 9 June 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)

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



value varchar(11) NOT NULL



PRIMARY KEY (id)

Table: encounter

PRIMARY KEY (organization_id,person_id,id)

Column name Data type Constraint Comments References
id bigint(20) NOT NULL



organization_id bigint(20) NOT NULL Owning organisation (i.e. publisher) organization.id
patient_id bigint(20) NOT NULL The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times patient.id
person_id bigint(20) NOT NULL Unique individual across all organisations person.id
practitioner_id bigint(20) DEFAULT NULL The clinician the activity is recorded against practitioner.id
appointment_id bigint(20) DEFAULT NULL


appointment.id
clinical_effective_date date DEFAULT NULL The date the clinical code is recorded for


date_precision_id smallint(6) DEFAULT NULL Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)


snomed_concept_id bigint(20) DEFAULT NULL The SNOMED code


original_code varchar(100) DEFAULT NULL The original code from the source system


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


episode_of_care_id bigint(20) DEFAULT NULL Is this instance of the code a review of a previous encounter


service_provider_organization_id bigint(20) DEFAULT NULL



PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY encounter_id (id),
KEY encounter_patient_id (patient_id),
KEY fki_encounter_appointment_id (appointment_id),
KEY fki_encounter_patient_id_organization_id (patient_id,organization_id),
KEY encounter_snomed_concept_id_clinical_effective_date (snomed_concept_id,clinical_effective_date),
KEY snomed_concept_id_clinical_effective_date_patient_id (snomed_concept_id,clinical_effective_date,patient_id),
KEY patient_id_clinical_effective_date_snomed_concept_id (patient_id,clinical_effective_date,snomed_concept_id),
KEY encounter_snomed_concept_id (snomed_concept_id)

Table: encounter_detail

PRIMARY KEY (organization_id,person_id,id)

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

PRIMARY KEY (organization_id,person_id,id)

Column name Data type Constraint Comments References
id bigint(20) NOT NULL



organization_id bigint(20) NOT NULL Owning organisation (i.e. publisher) organization.id
patient_id bigint(20) NOT NULL The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times patient.id
person_id bigint(20) NOT NULL Unique individual across all organisations person.id
registration_type_id smallint(6) DEFAULT NULL


registration.type.id
date_registered date DEFAULT NULL



date_registered_end date DEFAULT NULL



usual_gp_practitioner_id bigint(20) DEFAULT NULL


Practitioner.id
registration_status_id smallint(6) DEFAULT NULL


registration.status
PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY episode_of_care_id (id),
KEY episode_of_care_patient_id (patient_id),
KEY episode_of_care_registration_type_id (registration_type_id),
KEY episode_of_care_date_registered (date_registered),
KEY episode_of_care_date_registered_end (date_registered_end),
KEY episode_of_care_person_id (person_id),
KEY episode_of_care_organization_id (organization_id),
KEY reg_type_id_patient_id_date_registered_date_registered_end (registration_type_id,patient_id,date_registered,date_registered_end)


Table: ethnicity_lookup

PRIMARY KEY (ethnic_code)

Column name Data type Constraint Comments References
ethnic_code char(1) NOT NULL



ethnic_name varchar(100) DEFAULT NULL



PRIMARY KEY (ethnic_code)


Table: flag

PRIMARY KEY (organization_id,person_id,id)

Column name Data type Constraint Comments References
id bigint(20) NOT NULL



organization_id bigint(20) NOT NULL Owning organisation (i.e. publisher)


patient_id bigint(20) NOT NULL The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times patient.id
person_id bigint(20) NOT NULL Unique individual across all organisations person.id
effective_date date DEFAULT NULL



date_precision_id smallint(6) DEFAULT NULL Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)


is_active tinyint(1) NOT NULL



flag_text text   This is a warning set by the publisher regarding he patient


PRIMARY KEY (organization_id,person_id,id),
UNIQUE KEY flag_id (id),
KEY fk_flag_patient_id_organization_id (patient_id,organization_id),
KEY fk_flag_date_precision (date_precision_id),
KEY flag_patient_id (patient_id),
CONSTRAINT fk_flag_date_precision FOREIGN KEY (date_precision_id) REFERENCES
CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id) REFERENCES patient (id, organization_id)


Table: local_authority_lookup

PRIMARY KEY (local_authority_code)

Column name Data type Constraint Comments References
local_authority_code varchar(9) NOT NULL


local.authority.code
local_authority_name varchar(255) DEFAULT NULL



PRIMARY KEY (local_authority_code)


Table: location

PRIMARY KEY (id)

Column name Data type Constraint Comments References
id bigint(20) NOT NULL



name varchar(255) DEFAULT NULL The name of a location set by the publisher. E.g. ward, clinic, domiciliary


type_code varchar(50) DEFAULT NULL



type_desc varchar(255) DEFAULT NULL



postcode varchar(10) DEFAULT NULL



managing_organization_id bigint(20) DEFAULT NULL



PRIMARY KEY (id),
UNIQUE KEY location_id (id),
KEY fk_location_managing_organisation_id (managing_organization_id)


Table: Isoa_lookup

PRIMARY KEY (lsoa_code)

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.discoverydataservice.org/index.php?title=Remote_Subscriber_Database_(RSD)_Schema_(Compass_1)&oldid=1284’