Remote Subscriber Database (RSD) Schema (Compass 1)

From Discovery Data Service
Revision as of 10:43, 9 June 2020 by JoC (talk | contribs)
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)

| 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) |}

Retrieved from ‘https://wiki.discoverydataservice.org/index.php?title=Remote_Subscriber_Database_(RSD)_Schema_(Compass_1)&oldid=1282’