Remote Subscriber Database (RSD) Schema (Compass 1)
Jump to navigation
Jump to search
Table: allergy_intolerance
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL |
|
|
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
| patient_id | bigint(20) | NOT NULL | The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times | patient.id |
| person_id | bigint(20) | NOT NULL | Unique individual across all organisations | person.id |
| encounter_id | bigint(20) | DEFAULT NULL |
|
encounter.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| clinical_effective_date | date | DEFAULT NULL | The date the clinical code is recorded for |
|
| date_precision_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) |
|
| snomed_concept_id | bigint(20) | DEFAULT NULL | The SNOMED code |
|
| original_code | varchar(100) CHARACTER SET utf8 COLLATE utf8_bin | DEFAULT NULL | The original code from the source system |
|
| original_term | varchar(1000) | DEFAULT NULL | The original code term from the source system |
|
| is_review | tinyint(1) | NOT NULL | Is this instance of the code a review of a previous encounter |
|
| PRIMARY KEY (organization_id,person_id,id), | ||||
| UNIQUE KEY allergy_intolerance_id (id), | ||||
| KEY allergy_intolerance_patient_id (patient_id), | ||||
| KEY allergy_intolerance_snomed_concept_id (snomed_concept_id) | ||||
|
| ||||
Table: appointment
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL |
|
|
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
| patient_id | bigint(20) | NOT NULL | The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times | patient.id |
| person_id | bigint(20) | NOT NULL | Unique individual across all organisations | person.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| schedule_id | bigint(20) | DEFAULT NULL | The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule | schedule.id |
| start_date | datetime | DEFAULT NULL |
|
|
| planned_duration | int(11) | DEFAULT NULL | The time allocated for the appointment, not necessarily the actual duration always in minutes |
|
| actual_duration | int(11) | DEFAULT NULL | Time between sent in and left always in minutes |
|
| appointment_status_id | smallint(6) | NOT NULL | The status of the appointment e.g. arrived/sent in/left/DNA | appointment.status.id |
| patient_wait | int(11) | DEFAULT NULL | How long the patient waited from being marked as arrived to being sent in |
|
| patient_delay | int(11) | DEFAULT NULL |
|
|
| sent_in | datetime | DEFAULT NULL | Date and time the patient was sent into the practitioner |
|
| left | datetime | DEFAULT NULL | Date and time the patient left the practitioner |
|
| PRIMARY KEY (organization_id,person_id,id), | ||||
| UNIQUE KEY appointment_id (id), | ||||
| KEY appointment_patient_id (patient_id) | ||||
Table: appointment_status
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | smallint(6) | NOT NULL |
|
|
| value | varchar(50) | NOT NULL |
|
|
| PRIMARY KEY (id) | ||||
Table: date_precision
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | smallint(6) | NOT NULL |
|
|
| value | varchar(11) | NOT NULL |
|
|
| PRIMARY KEY (id) | ||||
Table: encounter
PRIMARY KEY (organization_id,person_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL |
|
|
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
| patient_id | bigint(20) | NOT NULL | The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times | patient.id |
| person_id | bigint(20) | NOT NULL | Unique individual across all organisations | person.id |
| practitioner_id | bigint(20) | DEFAULT NULL | The clinician the activity is recorded against | practitioner.id |
| appointment_id | bigint(20) | DEFAULT NULL |
|
appointment.id |
| clinical_effective_date | date | DEFAULT NULL | The date the clinical code is recorded for |
|
| date_precision_id | smallint(6) | DEFAULT NULL | Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14) |
|
| snomed_concept_id | bigint(20) | DEFAULT NULL | The SNOMED code |
|
| original_code | varchar(100) | DEFAULT NULL | The original code from the source system |
|
| original_term | varchar(1000) | DEFAULT NULL | The original code term from the source system |
|
| episode_of_care_id | bigint(20) | DEFAULT NULL | Is this instance of the code a review of a previous encounter |
|
| service_provider_organization_id | bigint(20) | DEFAULT NULL |
|
|
| PRIMARY KEY (organization_id,person_id,id), | ||||
| UNIQUE KEY encounter_id (id), | ||||
| KEY encounter_patient_id (patient_id), | ||||
| KEY fki_encounter_appointment_id (appointment_id), | ||||
| KEY fki_encounter_patient_id_organization_id (patient_id,organization_id), | ||||
| KEY encounter_snomed_concept_id_clinical_effective_date (snomed_concept_id,clinical_effective_date), | ||||
| KEY snomed_concept_id_clinical_effective_date_patient_id (snomed_concept_id,clinical_effective_date,patient_id), | ||||
| KEY patient_id_clinical_effective_date_snomed_concept_id (patient_id,clinical_effective_date,snomed_concept_id), | ||||
| KEY encounter_snomed_concept_id (snomed_concept_id) | ||||
Table: encounter_detail
PRIMARY KEY (organization_id,person_id,id)
| 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) | ||||