Remote Subscriber Database (RSD) Schema (Compass 1): Difference between revisions
No edit summary |
No edit summary |
||
| Line 136: | Line 136: | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
| Line 278: | Line 278: | ||
PRIMARY KEY (id) | PRIMARY KEY (id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
| Line 316: | Line 316: | ||
PRIMARY KEY (id) | PRIMARY KEY (id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
| Line 352: | Line 352: | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
| Line 503: | Line 503: | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
| Line 747: | Line 747: | ||
PRIMARY KEY (organization_id,person_id,id) | PRIMARY KEY (organization_id,person_id,id) | ||
| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
|Column name | |Column name | ||
|Data type | |Data type | ||
Revision as of 10:43, 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)
| 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)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id) |-
| colspan="5" | UNIQUE KEY medication_order_id (id) |-
| colspan="5" | KEY medication_order_patient_id (patient_id) |-
| colspan="5" | KEY medication_order_dmd_id (dmd_id) |}
Table: medication_statement
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-
| colspan="5" | UNIQUE KEY medication_statement_id (id), |-
| colspan="5" | KEY medication_statement_patient_id (patient_id), |-
| colspan="5" | KEY medication_statement_dmd_id (dmd_id) |}
Table: medication_statement_authorisation_type
PRIMARY KEY (id)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|id |smallint(6) |NOT NULL |
|
|-
|value |varchar(50) |NOT NULL |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: msoa_lookup
PRIMARY KEY (msoa_code)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|msoa_code char(9) |NOT NULL |
|
|
|-
|msoa_name |varchar(255) |DEFAULT NULL |
|
|-
| colspan="5" | PRIMARY KEY (msoa_code) |}
Table: observation
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-
| colspan="5" | UNIQUE KEY observation_id (id), |-
| colspan="5" | KEY observation_patient_id (patient_id), |-
| colspan="5" | KEY observation_snomed_concept_id (snomed_concept_id), |-
| colspan="5" | KEY observation_snomed_concept_id_is_problem (snomed_concept_id,is_problem), |-
| colspan="5" | KEY observation_snomed_concept_id_value (snomed_concept_id,result_value), |-
| colspan="5" | KEY ix_observation_organization_id (organization_id), |-
| colspan="5" | KEY ix_observation_clinical_effective_date (clinical_effective_date), |-
| colspan="5" | KEY ix_observation_person_id (person_id), |-
| colspan="5" | KEY observation_original_code (original_code) |}
Table: organization
PRIMARY KEY (id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (id), |-
| colspan="5" | UNIQUE KEY organization_id (id), |-
| colspan="5" | KEY fki_organization_parent_organization_id (parent_organization_id), |-
| colspan="5" | KEY organization_id_parent_organization_id (id,parent_organization_id) |}
Table: patient
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-
| colspan="5" | UNIQUE KEY patient_id (id), |-
| colspan="5" | KEY patient_person_id (person_id), |-
| colspan="5" | KEY organization_id (organization_id), |-
| colspan="5" | KEY patient_id_organization_id (id,organization_id), |-
| colspan="5" | KEY organization_id_date_of_death_id (organization_id,date_of_death,id), |-
| colspan="5" | KEY patient_date_of_death (date_of_death), |-
| colspan="5" | KEY postcode_prefix (postcode_prefix), |-
| colspan="5" | KEY pseudo_id (pseudo_id) |}
Table: patient_gender
PRIMARY KEY (id)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|id |smallint(6) |NOT NULL |
|
|-
|value |varchar(10) |NOT NULL |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: patient_uprn
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,patient_id), |-
| colspan="5" | UNIQUE KEY patient_uprn_id (patient_id), |-
| colspan="5" | KEY ix_patient_uprn_patient_org_uprn (patient_id,organization_id,pseudo_uprn), |-
| colspan="5" | KEY ix1 (patient_id) |}
Table: person
PRIMARY KEY (id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (id), |-
| colspan="5" | UNIQUE KEY person_id (id) |}
Table: practitioner
PRIMARY KEY (id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (id), |-
| colspan="5" | UNIQUE KEY practitioner_id (id) |}
Table: procedure_request
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-
| colspan="5" | UNIQUE KEY procedure_request_id (id), |-
| colspan="5" | KEY procedure_request_patient_id (patient_id) |}
Table: procedure_request_status
PRIMARY KEY (id)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|id |smallint(6) |NOT NULL |
|
|-
|value |varchar(50) |NOT NULL |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: referral_request
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,person_id,id), |-
| colspan="5" | UNIQUE KEY referral_request_id (id), |-
| colspan="5" | KEY referral_request_patient_id (patient_id), |-
| colspan="5" | KEY referral_request_snomed_concept_id (snomed_concept_id) |}
Table: referral_request_priority
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|id |smallint(6) |NOT NULL |
|
|-
|value |varchar(50) |NOT NULL |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: referral_request_type
PRIMARY KEY (id)
| Column name | Data type | Constraint | Comments | References |
| id | smallint(6) | NOT NULL |
|
|
| value | varchar(50) | NOT NULL |
|
|
| PRIMARY KEY (id) | ||||
Table: registration_status
PRIMARY KEY (id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: registration_status_history
PRIMARY KEY (organization_id,person_id,id)
| class="wikitable" border="1" |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 |
|
|-
| colspan="5" | PRIMARY KEY (organization_id,id,patient_id,person_id), |-
| colspan="5" | UNIQUE KEY ux_registration_status_history_id (id) |-
| colspan="5" | ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores registration status history for GP registrations'; |}
Table: registration_type
PRIMARY KEY (id)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|id |smallint(6) |NOT NULL |
|
|-
|code |varchar(10) |NOT NULL |
|
|-
|description |varchar(30) |NOT NULL |
|
|-
| colspan="5" | PRIMARY KEY (id) |}
Table: schedule
PRIMARY KEY (organization_id,id)
| Column name | Data type | Constraint | Comments | References |
| id | bigint(20) | NOT NULL |
|
|
| organization_id | bigint(20) | NOT NULL | Owning organisation (i.e. publisher) | organization.id |
| practitioner_id bigint(20) | bigint(20) | DEFAULT NULL |
|
practitioner.id |
| start_date | date | DEFAULT NULL |
|
|
| type | varchar(255) | DEFAULT NULL |
|
|
| location | varchar(255) | DEFAULT NULL |
|
location.id |
| PRIMARY KEY (organization_id,id), | ||||
| UNIQUE KEY schedule_id (id) | ||||
Table: ward_lookup
PRIMARY KEY (ward_code)
| class="wikitable" border="1" |Column name |Data type |Constraint |Comments |References |-
|ward_code |varchar(9) |NOT NULL |
|
|-
|ward_name |varchar(255) |DEFAULT NULL |
|
|-
| colspan="5" | PRIMARY KEY (ward_code) |}