|
|
Line 1: |
Line 1: |
| == Table: allergy_intolerance == | | == Table: appointment == |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed" | | {| class="wikitable sortable mw-collapsible mw-collapsed" |
| |'''Column name''' | | |'''Column name''' |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments''' | | |'''Comments''' |
| |'''References''' | | |'''Providing publisher''' |
| |- | | |- |
| |id | | |id |
| |bigint
| | |Unique Id of the appointment (specific to the database) |
| |NOT NULL
| |
| |Unique Id of the allergy | |
| | | | | |
| |- | | |- |
| |organization_id | | |organization_id |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher) | | |Owning organisation (i.e. publisher) |
| |organization.id | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |patient_id | | |patient_id |
| |bigint
| | |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 |
| |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 | | |person_id |
| |bigint
| |
| |NOT NULL
| |
| |Unique individual across all organisations | | |Unique individual across all organisations |
| |person.id
| |
| |-
| |
| |encounter_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the encounter this allergy was record in
| |
| |encounter.id
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |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_concept_id
| |
| |Int
| |
| |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_review
| |
| |boolean
| |
| |NOT NULL
| |
| |Is this instance of the code a review of a previous encounter
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |Int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the allergy
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |Int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the allergy
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age the patient was at the time of this event
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |NOT NULL
| |
| |The date the allergy was recorded
| |
| | | | | |
| |-
| |
| | colspan="5" |CONSTRAINT pk_allergy_intolerance_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_allergy_intolerance_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_allergy_intolerance_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_allergy_intolerance_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: appointment ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the appointment
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organization.id
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |NOT NULL
| |
| |Unique individual across all organisations
| |
| |person.id
| |
| |- | | |- |
| |practitioner_id | | |practitioner_id |
| |bigint
| |
| |DEFAULT NULL
| |
| |The clinician the activity is recorded against | | |The clinician the activity is recorded against |
| |practitioner.id | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |schedule_id | | |schedule_id |
| |bigint
| |
| |DEFAULT NULL
| |
| |The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule | | |The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule |
| |schedule.id | | | |
| |- | | |- |
| |start_date | | |start_date |
| |date
| |
| |DEFAULT NULL
| |
| |The start date of the appointment | | |The start date of the appointment |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |planned_duration | | |planned_duration |
| |int
| |
| |DEFAULT NULL
| |
| |The time allocated for the appointment, not necessarily the actual duration always in minutes | | |The time allocated for the appointment, not necessarily the actual duration always in minutes |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |actual_duration | | |actual_duration |
| |int
| |
| |DEFAULT NULL
| |
| |Time between sent in and left always in minutes | | |Time between sent in and left always in minutes |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |appointment_status_concept_id | | |appointment_status_concept_id |
| |int
| |
| |DEFAULT NULL
| |
| |The status of the appointment e.g. arrived/sent in/left/DNA | | |The status of the appointment e.g. arrived/sent in/left/DNA |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |patient_wait | | |patient_wait |
| |int
| |
| |DEFAULT NULL
| |
| |How long the patient waited from being marked as arrived to being sent in | | |How long the patient waited from being marked as arrived to being sent in |
| | | | | |
| |- | | |- |
| |patient_delay | | |patient_delay |
| |int
| |
| |DEFAULT NULL
| |
| |How long the patient was delayed for | | |How long the patient was delayed for |
| | | | | |
| |- | | |- |
| |date_time_sent_in | | |date_time_sent_in |
| |datetime
| |
| |DEFAULT NULL
| |
| |Date and time the patient was sent into the practitioner | | |Date and time the patient was sent into the practitioner |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |date_time_left | | |date_time_left |
| |datetime
| |
| |DEFAULT NULL
| |
| |Date and time the patient left the practitioner | | |Date and time the patient left the practitioner |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |source_id | | |source_id |
| |varchar(36)
| |
| |DEFAULT NULL
| |
| |Unique reference to the source of the appointment | | |Unique reference to the source of the appointment |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |cancelled_date | | |cancelled_date |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the appointment was cancelled | | |The date the appointment was cancelled |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | |
| | colspan="5" |CONSTRAINT pk_appointment_id PRIMARY KEY (organization_id,person_id,id),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_appointment_organization_id FOREIGN KEY (organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_appointment_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |} | | |} |
|
| |
|
| == Table: concept == | | == Table: episode_of_care == |
| PRIMARY KEY (dbid)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed" | | {| class="wikitable sortable mw-collapsible mw-collapsed" |
| |'''Column name''' | | |'''Column name''' |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments''' | | |'''Comments''' |
| |'''References''' | | |'''Providing publisher''' |
| |-
| |
| |dbid
| |
| |int(11)
| |
| |NOT NULL
| |
| |Unique Id of the concept
| |
| |
| |
| |-
| |
| |Document
| |
| |int(11)
| |
| |NOT NULL
| |
| |Concept grouping construct, deprecated
| |
| |
| |
| |-
| |
| |Id
| |
| |varchar(150)
| |
| |NOT NULL
| |
| |Unique concept identifier
| |
| |
| |
| |-
| |
| |Draft
| |
| |tinyint(1)
| |
| |NOT NULL
| |
| |Whether its draft/autocreated or confirmed as a "proper" concept
| |
| |
| |
| |-
| |
| |Name
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Short name
| |
| |
| |
| |-
| |
| |Description
| |
| |varchar(400)
| |
| |DEFAULT NULL
| |
| |Full name (or term for ontological concepts)
| |
| |
| |
| |-
| |
| |Scheme
| |
| |bigint(20)
| |
| |DEFAULT NULL
| |
| |The coding scheme for the code (Read, CTV3, SNOMED etc)
| |
| |
| |
| |-
| |
| |Code
| |
| |varchar(40)
| |
| |DEFAULT NULL
| |
| |The code (non-unique unless coupled with a scheme)
| |
| |
| |
| |-
| |
| |use_count
| |
| |bigint(20)
| |
| |NOT NULL DEFAULT 0
| |
| |Rough indicator of number of occurences of the concept
| |
| |
| |
| |-
| |
| |updated
| |
| |datetime
| |
| |NOT NULL
| |
| |The timestamp of the last update to the concept
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT PRIMARY KEY (dbid),
| |
| |}
| |
| | |
| == Table: concept_map ==
| |
| PRIMARY KEY (id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Legacy
| |
| |int(11)
| |
| |NOT NULL
| |
| |the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept
| |
| |
| |
| |-
| |
| |Core
| |
| |int(11)
| |
| |NOT NULL
| |
| |the core (snomed, discovery) concept that the legacy concept maps to
| |
| |
| |
| |-
| |
| |Updated
| |
| |datetime
| |
| |NOT NULL
| |
| |Timestamp the map was last updated/added
| |
| |
| |
| |- | | |- |
| |id | | |id |
| |int(11)
| | |Unique Id of the episode of care |
| |
| |
| |
| |
| |
| |
| |-
| |
| |deleted
| |
| |tinyint(1)
| |
| |
| |
| |
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT PRIMARY KEY (id)
| |
| |}
| |
| | |
| == Table: diagnostic_order ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the diagnostic order | |
| | | | | |
| |- | | |- |
| |organization_id | | |organization_id |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher) | | |Owning organisation (i.e. publisher) |
| | | | | |
| |- | | |- |
| |patient_id | | |patient_id |
| |bigint
| |
| |NOT NULL
| |
| |The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times | | |The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times |
| | | | | |
| |- | | |- |
| |person_id | | |person_id |
| |bigint
| | |Unique individual across all organisations |
| |NOT NULL
| |
| |Unique individual across all organisations | |
| |
| |
| |-
| |
| |encounter_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the encounter the observation was recorded at
| |
| |
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |The clinician the activity is recorded against
| |
| |
| |
| |-
| |
| |clinical_effective_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date the diagnostic order was identified by a clinician
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
| |
| |
| |
| |-
| |
| |result_value
| |
| |real
| |
| |DEFAULT NULL
| |
| |The value of the result of the observation
| |
| |
| |
| |-
| |
| |result_value_units
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The units of the result of the observation
| |
| |
| |
| |-
| |
| |result_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date of the result
| |
| |
| |
| |-
| |
| |result_text
| |
| |text
| |
| |DEFAULT NULL
| |
| |Any text associated with the result
| |
| |
| |
| |-
| |
| |result_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the result
| |
| |
| |
| |-
| |
| |is_problem
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the observation is marked as a problem
| |
| |
| |
| |-
| |
| |is_review
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the observation is a review of an existing problem
| |
| |
| |
| |-
| |
| |problem_end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of the problem
| |
| |
| |
| |-
| |
| |parent_observation_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the observation
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the observation
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal (5,2)
| |
| |DEFAULT NULL
| |
| |The age of the patient at the time of the observation
| |
| |
| |
| |-
| |
| |episodicity_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the episodicity of the problem eg First, review, flare
| |
| |
| |
| |-
| |
| |is_primary
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether the diagnostic order is a primary order
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_diagnostic_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_diagnostic_order_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_diagnostic_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_diagnostic_order_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: encounter ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the encounter
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |
| |
| |-
| |
| |person_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique individual across all organisations
| |
| |
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |The clinician the activity is recorded against
| |
| |
| |
| |-
| |
| |appointment_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the appointment this encounter took part on
| |
| |
| |
| |-
| |
| |clinical_effective_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date the clinical code is recorded for
| |
| |
| |
| |-
| |
| |
| |
| date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the precision of the date of the encounter
| |
| |
| |
| |-
| |
| |episode_of_care_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Is this instance of the code a review of a previous encounter
| |
| |
| |
| |-
| |
| |service_provider_organization_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the service provider organisation
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the type of encounter
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the type of encounter
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age the patient was when this encounter took place
| |
| |
| |
| |-
| |
| |Type
| |
| |text
| |
| |DEFAULT NULL
| |
| |Unused
| |
| |
| |
| |-
| |
| |sub_type
| |
| |text
| |
| |DEFAULT NULL
| |
| |Unused
| |
| |
| |
| |-
| |
| |admission_method
| |
| |varchar(40)
| |
| |DEFAULT NULL
| |
| |The admission method of the encounter
| |
| |
| |
| |-
| |
| |end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of the encounter
| |
| |
| |
| |-
| |
| |institution_location_id
| |
| |text
| |
| |DEFAULT NULL
| |
| |Reference to the institution the encounter took place at
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the encounter was recorded
| |
| |
| |
| |-
| |
| | colspan="5" |
| |
| CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_appointment_id FOREIGN KEY (appointment_id)
| |
| |-
| |
| | colspan="5" |REFERENCES appointment (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_episode_of_care_id FOREIGN KEY (episode_of_care_id)
| |
| |-
| |
| | colspan="5" |REFERENCES episode_of_care (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: encounter_additional ==
| |
| PRIMARY KEY (id, property_id, value_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |same as the id column on the encounter table
| |
| |
| |
| |-
| |
| |property_id
| |
| |int
| |
| |NOT NULL
| |
| |IM reference (i.e. Admission method)
| |
| |
| |
| |-
| |
| |value_id
| |
| |int
| |
| |NOT NULL
| |
| |IM reference (i.e. Emergency admission)
| |
| |
| |
| |-
| |
| |json_value
| |
| |JSON
| |
| |NULL
| |
| |where there is no mapped value_id, just raw JSON (i.e. birth delivery details)
| |
| |
| |
| |-
| |
| |text_value
| |
| |varchar(255)
| |
| |NULL
| |
| |where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_encounter_additional_id PRIMARY KEY (id, property_id, value_id)
| |
| |}
| |
| | |
| == Table: encounter_event ==
| |
| PRIMARY KEY (organization_id, person_id, id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the encounter event
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |NOT NULL
| |
| |The patient this event belongs to
| |
| |
| |
| |-
| |
| |person_id
| |
| |bigint
| |
| |NOT NULL
| |
| |The person this event belongs to
| |
| |
| |
| |-
| |
| |encounter_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Reference to the parent encounter record
| |
| |
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |The clinician the activity is recorded against
| |
| |
| |
| |-
| |
| |appointment_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the appointment this encounter took part on
| |
| |
| |
| |-
| |
| |clinical_effective_date
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the encounter took place
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the precision of the date of the encounter
| |
| |
| |
| |-
| |
| |episode_of_care_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the episode of care this encounter belongs to
| |
| |
| |
| |-
| |
| |service_provider_organization_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the service provider organisation
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the type of encounter
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the type of encounter
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age of the patient at the time of the encounter
| |
| |
| |
| |-
| |
| |type
| |
| |text
| |
| |DEFAULT NULL
| |
| |Unused
| |
| |
| |
| |-
| |
| |sub_type
| |
| |text
| |
| |DEFAULT NULL
| |
| |Unused
| |
| |
| |
| |-
| |
| |admission_method
| |
| |varchar(40)
| |
| |DEFAULT NULL
| |
| |The admission method of the encounter
| |
| |
| |
| |-
| |
| |end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of the encounter
| |
| |
| |
| |-
| |
| |institution_location_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the institution the encounter took place at
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the encounter was recorded
| |
| |
| |
| |-
| |
| |finished
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether the encounter is finished
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_encounter_event_id PRIMARY KEY (organization_id, person_id, id),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_event_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_event_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_event_episode_of_care_id FOREIGN KEY (episode_of_care_id)
| |
| |-
| |
| | colspan="5" |REFERENCES episode_of_care (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_encounter_event_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: episode_of_care ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the episode of care
| |
| | | | | |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organization.id
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |NOT NULL
| |
| |Unique individual across all organisations
| |
| |person.id
| |
| |- | | |- |
| |registration_type_concept_id | | |registration_type_concept_id |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the registration type of the patient | | |Reference to the registration type of the patient |
| |registration.type.id | | | |
| |- | | |- |
| |registration_status_concept_id | | |registration_status_concept_id |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the registration status of the patient | | |Reference to the registration status of the patient |
| | | | | |
| |- | | |- |
| |date_registered | | |date_registered |
| |date
| |
| |DEFAULT NULL
| |
| |The date the registration was started for this episode of care | | |The date the registration was started for this episode of care |
| | | | | |
| |- | | |- |
| |date_registered_end | | |date_registered_end |
| |date
| |
| |DEFAULT NULL
| |
| |The date the registration was ended for this episode of care | | |The date the registration was ended for this episode of care |
| | | | | |
| |- | | |- |
| |usual_gp_practitioner_id | | |usual_gp_practitioner_id |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the usual GP for this episode of care | | |Reference to the usual GP for this episode of care |
| |Practitioner.id
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_episode_of_care_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_episode_of_care_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_episode_of_care_practitioner_id FOREIGN KEY (usual_gp_practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: event_log ==
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |dt_change
| |
| |datetime(3)
| |
| |NOT NULL
| |
| |date time the change was made to this DB
| |
| |
| |
| |-
| |
| |change_type
| |
| |tinyint
| |
| |NOT NULL
| |
| |type of transaction 0=insert, 1=update, 2=delete
| |
| |
| |
| |-
| |
| |table_id
| |
| |tinyint
| |
| |NOT NULL
| |
| |identifier of the table changed
| |
| |
| |
| |-
| |
| |record_id
| |
| |bigint
| |
| |NOT NULL
| |
| |id of the record changed
| |
| |
| |
| |}
| |
|
| |
| == Table: flag ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the flag
| |
| |
| |
| |-
| |
| |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
| |
| |The date the flag was entered onto the patients record
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |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
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the flag is active or not
| |
| |
| |
| |-
| |
| |flag_text
| |
| |text
| |
| |
| |
| |This is a warning set by the publisher regarding he patient
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_flag_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: location ==
| |
| PRIMARY KEY (pk_location_id PRIMARY KEY id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the location
| |
| |
| |
| |-
| |
| |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
| |
| |The type of location
| |
| |
| |
| |-
| |
| |type_desc
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Textual description of the type of location eg GP Practice
| |
| |
| |
| |-
| |
| |postcode
| |
| |varchar10)
| |
| |DEFAULT NULL
| |
| |The postcode of the location
| |
| |
| |
| |-
| |
| |managing_organization_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the managing organisation of the location
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_location_id PRIMARY KEY (id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_location_organisation_id FOREIGN KEY (managing_organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id)
| |
| |-
| |
| | colspan="5" |MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: medication_order ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the medication order
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organization.id
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |NOT NULL
| |
| |Unique individual across all organisations
| |
| |person.id
| |
| |-
| |
| |encounter_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the encounter the medication order was issued in
| |
| |encounter.id
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |The clinician the activity is recorded against
| |
| |practitioner.id
| |
| |-
| |
| |clinical_effective_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date the medication order was issued
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
| |
| |
| |
| |-
| |
| |dose
| |
| |varchar(1000)
| |
| |DEFAULT NULL
| |
| |Textual description of the dose
| |
| |
| |
| |-
| |
| |quantity_value
| |
| |real
| |
| |DEFAULT NULL
| |
| |The value of the medication that was prescribed eg 50
| |
| |
| |
| |-
| |
| |quantity_unit
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The unit of the medication that was prescribed eg tablets
| |
| |
| |
| |-
| |
| |duration_days
| |
| |int
| |
| |DEFAULT NULL
| |
| |How many days the medication is prescribed for
| |
| |
| |
| |-
| |
| |estimated_cost
| |
| |real
| |
| |DEFAULT NULL
| |
| |The estimated cost of the medication
| |
| |
| |
| |-
| |
| |medication_statement_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the medication statement. A medication statement can have many medication orders
| |
| |medication.statement.id
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the medication
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the medication
| |
| |
| |
| |-
| |
| |bnf_reference
| |
| |varchar(6)
| |
| |DEFAULT NULL
| |
| |A reference to the drug in the BNF dictionary
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age the patient was at the time of this event
| |
| |
| |
| |-
| |
| |issue_method
| |
| |text
| |
| |DEFAULT NULL
| |
| |The issue method of the medication eg hand written
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_medication_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_order_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_order_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: medication_statement ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the medication
| |
| |
| |
| |-
| |
| |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
| |
| |Reference to the encounter this medication was recorded in
| |
| |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 medication was clinical relevant
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |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
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether the medication is active or not
| |
| |
| |
| |-
| |
| |cancellation_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date the medication was cancelled
| |
| |
| |
| |-
| |
| |dose
| |
| |varchar(1000)
| |
| |DEFAULT NULL
| |
| |Texual description of the dose of the medication
| |
| |
| |
| |-
| |
| |quantity_value
| |
| |real
| |
| |DEFAULT NULL
| |
| |The value of the medication that was prescribed eg 50
| |
| |
| |
| |-
| |
| |quantity_unit
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The unit of the medication that was prescribed eg tablets
| |
| |
| |
| |-
| |
| |authorisation_type_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the authorisation type
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the medication
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the medication
| |
| |
| |
| |-
| |
| |bnf_reference
| |
| |varchar(6)
| |
| |DEFAULT NULL
| |
| |A reference to the drug in the BNF dictionary
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age the patient was at the time of this event
| |
| |
| |
| |-
| |
| |Issue_method
| |
| |text
| |
| |DEFAULT NULL
| |
| |The issue method of the medication eg hand written
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_medication_statement_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_statement_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_statement_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_medication_statement_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: observation ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the observation
| |
| |
| |
| |-
| |
| |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
| |
| |Reference to the encounter the observation was recorded at
| |
| |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 observation was identified by a clinician
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
| |
| |
| |
| |-
| |
| |result_value
| |
| |rea
| |
| |DEFAULT NULL
| |
| |The value of the result of the observation
| |
| |
| |
| |-
| |
| |result_value_units
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The units of the result of the observation
| |
| |
| |
| |-
| |
| |result_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date of the result
| |
| |
| |
| |-
| |
| |result_text
| |
| |text
| |
| |
| |
| |Any text associated with the result
| |
| |
| |
| |-
| |
| |result_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the result
| |
| |
| |
| |-
| |
| |is_problem
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the observation is marked as a problem
| |
| |
| |
| |-
| |
| |is_review
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the observation is a review of an existing problem
| |
| |
| |
| |-
| |
| |problem_end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of the problem
| |
| |
| |
| |-
| |
| |parent_observation_id
| |
| |bigint(20)
| |
| |DEFAULT NULL
| |
| |Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the observation
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the observation
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal (5,2)
| |
| |DEFAULT NULL
| |
| |The age of the patient at the time of the observation
| |
| |
| |
| |-
| |
| |episodicity_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the episodicity of the problem eg First, review, flare
| |
| |
| |
| |-
| |
| |is_primary
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether the observation is a primary observation
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the observation was recorded in the system
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_observation_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_observation_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_observation_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_observation_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
|
| |
| == Table: observation_additional ==
| |
| PRIMARY KEY (id, property_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |property_id
| |
| |int
| |
| |NOT NULL
| |
| |IM reference (i.e. significance)
| |
| |
| |
| |-
| |
| |value_id
| |
| |int
| |
| |NULL
| |
| |IM reference (i.e. minor, significant)
| |
| |person.id
| |
| |-
| |
| |json_value
| |
| |json
| |
| |NULL
| |
| |Where there is no mapped value_id, just raw JSON
| |
| |
| |
| |-
| |
| |text_value
| |
| |varchar(255)
| |
| |NULL
| |
| |Where there is no mapped value_id or raw JSON, just a basic text value
| |
| | | | | |
| |-
| |
| | colspan="5" |CONSTRAINT pk_observation_additional_id PRIMARY KEY (id, property_id)
| |
| |}
| |
|
| |
| == Table: organization ==
| |
| PRIMARY KEY (pk_organization_id PRIMARY KEY (id))
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the organisation
| |
| |
| |
| |-
| |
| |ods_code
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |ODS Code of the organisation
| |
| |
| |
| |-
| |
| |Name
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Name of the organisation
| |
| |
| |
| |-
| |
| |type_code
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The type of organisation
| |
| |
| |
| |-
| |
| |type_desc
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Textual description of the type of organisation eg GP Practice
| |
| |
| |
| |-
| |
| |Postcode
| |
| |varchar(10)
| |
| |DEFAULT NULL
| |
| |The postcode of the organisation
| |
| |
| |
| |-
| |
| |parent_organization_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |The id of the parent organisation
| |
| |
| |
| |-
| |
| | colspan="5" |PRIMARY KEY pk_organization_id PRIMARY KEY (id)
| |
| |}
| |
|
| |
| == Table: organization_metadata ==
| |
| PRIMARY KEY (id, publishing_software)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |corresponds to same ID in the organizaton table
| |
| |
| |
| |-
| |
| |publishing_software
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |software name of publishing system
| |
| |
| |
| |-
| |
| |last_data_to_dds
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |date time data was last sent to DDS
| |
| |
| |
| |-
| |
| |last_data_cutoff
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |cutoff date time of the last extract from the publishing system
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_organization_metadata PRIMARY KEY (id, publishing_software)
| |
| |} | | |} |
|
| |
|
| == Table: patient == | | == Table: patient == |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed" | | {| class="wikitable sortable mw-collapsible mw-collapsed" |
| |'''Column name''' | | |'''Column name''' |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments''' | | |'''Comments''' |
| |'''References''' | | |'''Providing publisher''' |
| |- | | |- |
| |id | | |id |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the patient | | |Unique Id of the patient |
| | | | | |
| |- | | |- |
| |organization_id | | |organization_id |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher) | | |Owning organisation (i.e. publisher) |
| |organization.id | | |[[EMIS published data transformation and mapping|EMIS]], [[HL7 ADT|ADT]], [[BHRUT Medway Extract FHIR Mapping|BHRUT]] |
| |- | | |- |
| |person_id | | |person_id |
| |bigint
| |
| |NOT NULL
| |
| |Unique individual across all organisations | | |Unique individual across all organisations |
| |person.id | | | |
| |- | | |- |
| |title | | |title |
| |varchar(255)
| |
| |
| |
| |The title of the patient | | |The title of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |first_names | | |first_names |
| |varchar(255)
| |
| |
| |
| |The first names of the patient | | |The first names of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |last_name | | |last_name |
| |varchar(255)
| |
| |
| |
| |The last name of the patient | | |The last name of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |gender_concept_id | | |gender_concept_id |
| |int
| |
| |
| |
| |Reference to the gender of the patient | | |Reference to the gender of the patient |
| |patient.gender.id | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |nhs_number | | |nhs_number |
| |varchar(255)
| |
| |
| |
| |The NHS number of the patient | | |The NHS number of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |date_of_birth | | |date_of_birth |
| |date
| |
| |
| |
| |The date of birth of the patient | | |The date of birth of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |date_of_death | | |date_of_death |
| |date
| |
| |
| |
| |The date of death of the patient | | |The date of death of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |current_address_id | | |current_address_id |
| |bigint
| |
| |
| |
| |Reference to the current address of the patient | | |Reference to the current address of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |ethnic_code_concept_id | | |ethnic_code_concept_id |
| |int
| |
| |
| |
| |Reference to the ethnicity of the patient | | |Reference to the ethnicity of the patient |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |registered_practice_organization_id | | |registered_practice_organization_id |
| |bigint
| |
| |
| |
| |Reference to the organisation the patient is registered at | | |Reference to the organisation the patient is registered at |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |birth_year | | |birth_year |
| |smallint
| |
| |
| |
| |
| |
| | | | | |
| | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |birth_month | | |birth_month |
| |tinyint
| |
| |
| |
| |
| |
| | | | | |
| | |[[EMIS published data transformation and mapping|EMIS]] |
| |- | | |- |
| |birth_week | | |birth_week |
| |tinyint
| |
| | | | | |
| | | | |[[EMIS published data transformation and mapping|EMIS]] |
| | | |
| |-
| |
| | colspan="5" |CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_organization_id FOREIGN KEY (organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |} | | |} |
|
| |
|
| == Table: patient_additional == | | == Table: patient_additional == |
| PRIMARY KEY (id, property_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed" | | {| class="wikitable sortable mw-collapsible mw-collapsed" |
| |'''Column name''' | | |'''Column name''' |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments''' | | |'''Comments''' |
| |'''References''' | | |'''Providing publisher''' |
| |- | | |- |
| |id | | |id |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the patient | | |Unique Id of the patient |
| | | | | |
| |- | | |- |
| |property_id | | |property_id |
| |int
| |
| |NOT NULL
| |
| |IM reference (e.g. cause of death) | | |IM reference (e.g. cause of death) |
| | | | | |
| |- | | |- |
| |value_id | | |value_id |
| |int
| |
| |NULL
| |
| |IM reference (e.g. COVID) | | |IM reference (e.g. COVID) |
| |person.id | | | |
| |- | | |- |
| |json_value | | |json_value |
| |json
| |
| |NULL
| |
| |Where there is no mapped value_id, just raw JSON | | |Where there is no mapped value_id, just raw JSON |
| | | | | |
| |- | | |- |
| |text_value | | |text_value |
| |varchar(255)
| |
| |NULL
| |
| |Where there is no mapped value_id or raw JSON, just a basic text value | | |Where there is no mapped value_id or raw JSON, just a basic text value |
| | | | | |
| |-
| | |}<br /> |
| | colspan="5" |CONSTRAINT pk_patient_additional_id PRIMARY KEY (id, property_id)
| |
| |} | |
| | |
| == Table: patient_address ==
| |
| PRIMARY KEY (organization_id,id,patient_id,person_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the address
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organisation.id
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |DEFAULT NULL
| |
| |Unique individual across all organisations
| |
| |person.id
| |
| |-
| |
| |address_line_1
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The first line of the address
| |
| |
| |
| |-
| |
| |address_line_2
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The second line of the address
| |
| |
| |
| |-
| |
| |address_line_3
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The third line of the address
| |
| |
| |
| |-
| |
| |address_line_4
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The fourth line of the address
| |
| |
| |
| |-
| |
| |city
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The city
| |
| |
| |
| |-
| |
| |postcode
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The postcode
| |
| |
| |
| |-
| |
| |use_concept_id
| |
| |int
| |
| |NOT NULL
| |
| |use of address (e.g. home, temporary)
| |
| |
| |
| |-
| |
| |start_date
| |
| |date
| |
| |NOT NULL
| |
| |The start date of this address being relevant
| |
| |
| |
| |-
| |
| |end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of this address being relevant
| |
| |
| |
| |-
| |
| |lsoa_2001_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |A reference to the LSOA_2001 code
| |
| |
| |
| |-
| |
| |lsoa_2011_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |A reference to the LSOA_2011 code
| |
| |
| |
| |-
| |
| |msoa_2001_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |A reference to the MSOA_2001 code
| |
| |
| |
| |-
| |
| |msoa_2011_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |A reference to the MSOA_2011 code
| |
| |
| |
| |-
| |
| |ward_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |The ward the address belongs to
| |
| |
| |
| |-
| |
| |local_authority_code
| |
| |varchar(9)
| |
| |DEFAULT NULL
| |
| |The local authority the address belongs to
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_organization_id_id_patient_id_person_id
| |
| |-
| |
| | colspan="5" |PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_address_patient_id_organization_id
| |
| |-
| |
| | colspan="5" |FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id)
| |
| |-
| |
| | colspan="5" |COMMENT 'stores address details for patients'
| |
| |}
| |
| | |
| == Table: patient_address_match ==
| |
| PRIMARY KEY(id, uprn)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint(50)
| |
| |NOT NULL
| |
| |Reference to the patient_address table
| |
| |
| |
| |-
| |
| |patient_address_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |Uprn
| |
| |varchar(255)
| |
| |NOT NULL
| |
| |The Unique Propery Reference Number of the address
| |
| |
| |
| |-
| |
| |uprn_ralf00
| |
| |varchar(255)
| |
| |
| |
| |
| |
| |
| |
| |-
| |
| |Status
| |
| |tinyint(1)
| |
| |DEFAULT NULL
| |
| |Whether the UPRN is active
| |
| |
| |
| |-
| |
| |uprn_property_classification
| |
| |varchar(45)
| |
| |DEFAULT NULL
| |
| |Building type eg flat, pub, house etc
| |
| |
| |
| |-
| |
| |Latitude
| |
| |double
| |
| |DEFAULT NULL
| |
| |The latitude of the UPRN
| |
| |
| |
| |-
| |
| |Longitude
| |
| |double
| |
| |DEFAULT NULL
| |
| |The longitude of the UPRN
| |
| |
| |
| |-
| |
| |uprn_xcoordinate
| |
| |double
| |
| |DEFAULT NULL
| |
| |The x coordinate of the UPRN
| |
| |
| |
| |-
| |
| |uprn_ycoordinate
| |
| |double
| |
| |DEFAULT NULL
| |
| |The y coordinate of the UPRN
| |
| |
| |
| |-
| |
| |qualifier
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |How the match is determined eg equivalent match, near match etc
| |
| |
| |
| |-
| |
| |match_rule
| |
| |varchar(4096)
| |
| |DEFAULT NULL
| |
| |Which algorithm was used to match the address to the UPRN
| |
| |
| |
| |-
| |
| |match_date
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the match was made
| |
| |
| |
| |-
| |
| |abp_address_number
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The number value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |abp_address_street
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The street value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |abp_address_locality
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The locality value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |abp_address_town
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The town value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |abp_address_postcode
| |
| |varchar(10)
| |
| |DEFAULT NULL
| |
| |The postcode value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |abp_address_organization
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The organization value of the address in the ABP dictionary
| |
| |
| |
| |-
| |
| |match_pattern_postcode
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The qualifier used to match the postcode
| |
| |
| |
| |-
| |
| |match_pattern_street
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The qualifier used to match the street
| |
| |
| |
| |-
| |
| |match_pattern_number
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The qualifier used to match the number
| |
| |
| |
| |-
| |
| |match_pattern_building
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The qualifier used to match the building
| |
| |
| |
| |-
| |
| |match_pattern_flat
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The qualifier used to match the flat
| |
| |
| |
| |-
| |
| |algorithm_version
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |What version of the algorithm was used to make the match
| |
| |
| |
| |-
| |
| |epoc
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The version of the ABP files the addresses were matched against
| |
| |
| |
| |-
| |
| | colspan="5" | CONSTRAINT `patient_address_uprn_patient_address_id_fk` FOREIGN KEY (`patient_address_id`) REFERENCES `patient_address` (`id`)
| |
| |}
| |
| | |
| == Table: patient_address_ralf ==
| |
| PRIMARY KEY (id, patient_address_id, patient_address_match_uprn_ralf00)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |person_id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |patient_address_id
| |
| |bigint
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |patient_address_match_uprn_ralf00
| |
| |varchar(255)
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |salt_name
| |
| |varchar(50)
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| |ralf
| |
| |varchar(255)
| |
| |NOT NULL
| |
| |
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_patient_address_ralf PRIMARY KEY (id, patient_address_id, patient_address_match_uprn_ralf00)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_address_ralf_organization_id FOREIGN KEY (organization_id) REFERENCES organization (id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_address_ralf_patient_id FOREIGN KEY (patient_id) REFERENCES patient (id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_address_ralf_person_id FOREIGN KEY (person_id) REFERENCES person (id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_address_ralf_patient_address_id FOREIGN KEY (patient_address_id) REFERENCES patient_address (id)
| |
| |}
| |
| | |
| == Table: patient_contact ==
| |
| PRIMARY KEY (organization_id,id,patient_id,person_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the patient contact
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |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
| |
| |DEFAULT NULL
| |
| |Unique individual across all organisations
| |
| |person.id
| |
| |-
| |
| |use_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |use of contact (e.g. mobile, home,work
| |
| |
| |
| |-
| |
| |type_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |type of contact (e.g. phone, email)
| |
| |
| |
| |-
| |
| |start_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The start date of the contact being valid
| |
| |
| |
| |-
| |
| |end_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The end date of the contact being valid
| |
| |
| |
| |-
| |
| |value
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The value of the contact information eg phone number, email address
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_organization_id_id_patient_id_person_id PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_contact_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id)
| |
| |-
| |
| | colspan="5" |COMMENT 'stores contact details (e.g. phone) for patients'
| |
| |}
| |
| | |
| == Table: patient_pseudo_id ==
| |
| PRIMARY KEY(organization_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the patient pseudo id
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Reference to the patient this registration status history belongs to
| |
| |
| |
| |-
| |
| |person_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Reference to the person this registration status history belongs to
| |
| |
| |
| |-
| |
| |salt_name
| |
| |varchar(50)
| |
| |NOT NULL
| |
| |The name of the salt used to create the pseudo id
| |
| |
| |
| |-
| |
| |Skid
| |
| |varchar(255)
| |
| |NOT NULL
| |
| |"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)
| |
| |
| |
| |-
| |
| |is_nhs_number_valid
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the nhs number is valid
| |
| |
| |
| |-
| |
| |is_nhs_number_verified_by_publisher
| |
| |boolean
| |
| |NOT NULL
| |
| |Whether the nhs number has been verified by the publisher
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT PRIMARY KEY (organization_id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_pseudo_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id)
| |
| |}
| |
| | |
| == Table: patient_uprn ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''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
| |
| |-
| |
| |Uprn
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |qualifier
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |algorithm
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |match
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |no_address
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |invalid_address
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |missing_postcode
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |invalid_postcode
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`patient_id`),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_patient_uprn_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: person ==
| |
| PRIMARY KEY (id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the person
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |title
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The title of the person
| |
| |
| |
| |-
| |
| |first_names
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The first names of the person
| |
| |
| |
| |-
| |
| |last_name
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The last name of the person
| |
| |
| |
| |-
| |
| |gender_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the gender of the person
| |
| |
| |
| |-
| |
| |nhs_number
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The NHS number of the person
| |
| |
| |
| |-
| |
| |date_of_birth date,
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date of birth of the person
| |
| |
| |
| |-
| |
| |date_of_death date,
| |
| |date
| |
| |DEFAULT NULL
| |
| |The date of death of the person
| |
| |
| |
| |-
| |
| |current_address_id,
| |
| |bigint
| |
| |NOT NULL
| |
| |Reference to the current address of the person
| |
| |
| |
| |-
| |
| |ethnic_code_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the ethnicity of the person
| |
| |
| |
| |-
| |
| |registered_practice_organization_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the organisation the person is registered at
| |
| |
| |
| |-
| |
| |birth_year
| |
| |smallint
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |birth_month
| |
| |tinyint
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| |birth_week
| |
| |tinyint
| |
| |DEFAULT NULL
| |
| |
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_person_id PRIMARY KEY (id)
| |
| |}
| |
| | |
| == Table: practitioner ==
| |
| PRIMARY KEY (id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the practitioner
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organisation.id
| |
| |-
| |
| |name
| |
| |varchar(1024)
| |
| |DEFAULT NULL
| |
| |Name of the practitioner
| |
| |
| |
| |-
| |
| |role_code
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The code representing the role of the practitioner
| |
| |
| |
| |-
| |
| |role_desc
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Textual description of the role of the practitioner eg General Medical Practitioner
| |
| |
| |
| |-
| |
| |gmc_code
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The GMC code of the practitioner
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_practitioner_id PRIMARY KEY (id),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_practitioner_organisation_id FOREIGN KEY (organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: procedure_request ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the procedure
| |
| |
| |
| |-
| |
| |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
| |
| |Reference to the encounter the procedure was administered at
| |
| |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 procedure was administered by a clinician
| |
| |
| |
| |-
| |
| |date_precision_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
| |
| |
| |
| |-
| |
| |status_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the status of the procedure
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the procedure
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the procedure
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age of the patient at the time of the procedure
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the procedure was recorded in the source system
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_procedure_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |/*got an Emis procedure request referring to a confidential encounter, so can't enforce this
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_procedure_request_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,*/
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_procedure_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_procedure_request_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: pseudo_id ==
| |
| PRIMARY KEY (patient_id, salt_key_name)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the pseudo Id
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Reference to the patient the pseudo Id belongs to
| |
| |
| |
| |-
| |
| |salt_key_name
| |
| |varchar(50)
| |
| |NOT NULL
| |
| |The name of the salt key used to create the pseudo id
| |
| |
| |
| |-
| |
| |pseudo_id
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The pseudo id
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_pseudo_id PRIMARY KEY (patient_id, salt_key_name)
| |
| |}
| |
| | |
| == Table: referral_request ==
| |
| PRIMARY KEY (organization_id,person_id,id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the referral
| |
| |
| |
| |-
| |
| |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
| |
| |Reference to the encounter the referral was made in
| |
| |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 referral was made
| |
| |
| |
| |-
| |
| |date_precision_concept_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)
| |
| |
| |
| |-
| |
| |requester_organization_id
| |
| |bigint(20)
| |
| |DEFAULT NULL
| |
| |Reference to the organisation that made the refereral request
| |
| |
| |
| |-
| |
| |recipient_organization_id
| |
| |bigint(20)
| |
| |DEFAULT NULL
| |
| |Reference to the organization receiving the referral
| |
| |
| |
| |-
| |
| |referral_request_priority_concept_id
| |
| |smallint(6)
| |
| |DEFAULT NULL
| |
| |Reference to the priority of the referral
| |
| |referral.request.priority
| |
| |-
| |
| |referral_request_type_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the type of referral request
| |
| |referral.request.type
| |
| |-
| |
| |Mode
| |
| |varchar(50)
| |
| |DEFAULT NULL
| |
| |The mode of the referral
| |
| |
| |
| |-
| |
| |outgoing_referral
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether this is an outgoing referral
| |
| |
| |
| |-
| |
| |is_review
| |
| |boolean
| |
| |DEFAULT NULL
| |
| |Whether this referral is a review
| |
| |
| |
| |-
| |
| |core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the referral
| |
| |
| |
| |-
| |
| |non_core_concept_id
| |
| |int
| |
| |DEFAULT NULL
| |
| |Reference to the clinical coding of the referral
| |
| |
| |
| |-
| |
| |age_at_event
| |
| |decimal(5,2)
| |
| |DEFAULT NULL
| |
| |The age of the patient at the time of the referral
| |
| |
| |
| |-
| |
| |date_recorded
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The date the referral request was added to the source system
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_referral_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
| |
| |-
| |
| | colspan="5" |/*got an Emis referral request referring to a confidential encounter, so can't enforce this
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_referral_request_encounter_id FOREIGN KEY (encounter_id)
| |
| |-
| |
| | colspan="5" |REFERENCES encounter (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,*/
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_referral_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES patient (id, organization_id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_referral_request_practitioner_id FOREIGN KEY (practitioner_id)
| |
| |-
| |
| | colspan="5" |REFERENCES practitioner (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_referral_request_recipient_organization_id FOREIGN KEY (recipient_organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION,
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_referral_request_requester_organization_id FOREIGN KEY (requester_organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| == Table: registration_status_history ==
| |
| PRIMARY KEY(organization_id,id,patient_id,person_id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |Id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Unique Id of the registration status history
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |
| |
| |-
| |
| |patient_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Reference to the patient this registration status history belongs to
| |
| |
| |
| |-
| |
| |person_id
| |
| |bigint(20)
| |
| |NOT NULL
| |
| |Reference to the person this registration status history belongs to
| |
| |
| |
| |-
| |
| |episode_of_care_id
| |
| |bigint(20)
| |
| |DEFAULT NULL
| |
| |Reference to the episode of care this status history belongs to
| |
| |
| |
| |-
| |
| |registration_status_concept_id
| |
| |int(11)
| |
| |DEFAULT NULL
| |
| |Reference to the registration status
| |
| |
| |
| |-
| |
| |start_date
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The start date for the period this registration status history was valid
| |
| |
| |
| |-
| |
| |end_date
| |
| |datetime
| |
| |DEFAULT NULL
| |
| |The end date for the period this registration status history was valid
| |
| |
| |
| |-
| |
| | colspan="5" |PRIMARY KEY (organization_id,id,patient_id,person_id)
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_registration_status_history_episode_id FOREIGN KEY (episode_of_care_id)
| |
| |}
| |
| == Table: schedule ==
| |
| PRIMARY KEY (organization_id, id)
| |
| {| class="wikitable sortable mw-collapsible mw-collapsed"
| |
| |'''Column name'''
| |
| |'''Data type'''
| |
| |'''Constraint'''
| |
| |'''Comments'''
| |
| |'''References'''
| |
| |-
| |
| |id
| |
| |bigint
| |
| |NOT NULL
| |
| |Unique Id of the schedule
| |
| |
| |
| |-
| |
| |organization_id
| |
| |bigint
| |
| |NOT NULL
| |
| |Owning organisation (i.e. publisher)
| |
| |organization.id
| |
| |-
| |
| |practitioner_id
| |
| |bigint
| |
| |DEFAULT NULL
| |
| |Reference to the practitioner who owns the schedule
| |
| |practitioner.id
| |
| |-
| |
| |start_date
| |
| |date
| |
| |DEFAULT NULL
| |
| |The start date of the schedule
| |
| |
| |
| |-
| |
| |type
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |The type of schedule eg Timed Appointments
| |
| |
| |
| |-
| |
| |Location
| |
| |varchar(255)
| |
| |DEFAULT NULL
| |
| |Textual description of the location the schedule was held at
| |
| |location.id
| |
| |-
| |
| |Name
| |
| |varchar(150)
| |
| |DEFAULT NULL
| |
| |The name of the schedule
| |
| |
| |
| |-
| |
| | colspan="5" |CONSTRAINT pk_schedule_id PRIMARY KEY (organization_id, id),
| |
| |-
| |
| | colspan="5" |CONSTRAINT fk_schedule_organization_id FOREIGN KEY (organization_id)
| |
| |-
| |
| | colspan="5" |REFERENCES organization (id) MATCH SIMPLE
| |
| |-
| |
| | colspan="5" |ON UPDATE NO ACTION ON DELETE NO ACTION
| |
| |}
| |
| | |
| <br /> | |
| __FORCETOC__ | | __FORCETOC__ |
| __INDEX__ | | __INDEX__ |
| __NONEWSECTIONLINK__ | | __NONEWSECTIONLINK__ |