|
|
| Line 3: |
Line 3: |
| PRIMARY KEY (organization_id,person_id,id) | | PRIMARY KEY (organization_id,person_id,id) |
| {| class="wikitable sortable mw-collapsible mw-collapsed" border="1" | | {| class="wikitable sortable mw-collapsible mw-collapsed" border="1" |
| |Column name
| | !Column name |
| |Data type
| | !Data type |
| |Constraint
| | !Constraint |
| |Comments
| | !Comments |
| |References
| | !References |
| |-
| | !- |
|
| |
|
| |id | | |id |
Revision as of 11:18, 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
|
|
Expand
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| id
|
smallint(6)
|
NOT NULL
|
|
|
| value
|
varchar(50)
|
NOT NULL
|
|
|
| PRIMARY KEY (id)
|
Table: date_precision
PRIMARY KEY (id)
| Data type
|
Constraint
|
Comments
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
0PRIMARY KEY (organization_id,person_id,id)
| Column name
|
Data type
|
Constraint
|
Comments
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| 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
|
ExpandReferences
|
| ward_code
|
varchar(9)
|
NOT NULL
|
|
|
| ward_name
|
varchar(255)
|
DEFAULT NULL
|
|
|
| PRIMARY KEY (ward_code)
|