Compass 2 Schema Mappings: Difference between revisions

From Discovery Data Service
Jump to navigation Jump to search
(Created page with "== Table: allergy_intolerance == PRIMARY KEY (organization_id,person_id,id) {| class="wikitable sortable mw-collapsible mw-collapsed" |'''Column name''' |'''Data type''' |'''C...")
 
(Replaced content with "== Table: appointment == {| class="wikitable sortable mw-collapsible mw-collapsed" |'''Column name''' |'''Comments''' |'''Providing publisher''' |- |id |Unique Id of the a...")
Tags: Replaced Visual edit
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__

Revision as of 11:44, 18 May 2021

Table: appointment

Column name Comments Providing publisher
id Unique Id of the appointment (specific to the database)
organization_id Owning organisation (i.e. publisher) EMIS
patient_id 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 Unique individual across all organisations
practitioner_id The clinician the activity is recorded against EMIS
schedule_id The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule
start_date The start date of the appointment EMIS
planned_duration The time allocated for the appointment, not necessarily the actual duration always in minutes EMIS
actual_duration Time between sent in and left always in minutes EMIS
appointment_status_concept_id The status of the appointment e.g. arrived/sent in/left/DNA EMIS
patient_wait How long the patient waited from being marked as arrived to being sent in
patient_delay How long the patient was delayed for
date_time_sent_in Date and time the patient was sent into the practitioner EMIS
date_time_left Date and time the patient left the practitioner EMIS
source_id Unique reference to the source of the appointment EMIS
cancelled_date The date the appointment was cancelled EMIS

Table: episode_of_care

Column name Comments Providing publisher
id Unique Id of the episode of care
organization_id Owning organisation (i.e. publisher)
patient_id 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 Unique individual across all organisations
registration_type_concept_id Reference to the registration type of the patient
registration_status_concept_id Reference to the registration status of the patient
date_registered The date the registration was started  for this episode of care
date_registered_end The date the registration was ended  for this episode of care
usual_gp_practitioner_id Reference to the usual GP for this episode of care

Table: patient

Column name Comments Providing publisher
id Unique Id of the patient
organization_id Owning organisation (i.e. publisher) EMIS, ADT, BHRUT
person_id Unique individual across all organisations
title The title of the patient EMIS
first_names The first names of the patient EMIS
last_name The last name of the patient EMIS
gender_concept_id Reference to the gender of the patient EMIS
nhs_number The NHS number of the patient EMIS
date_of_birth The date of birth of the patient EMIS
date_of_death The date of death of the patient EMIS
current_address_id Reference to the current address of the patient EMIS
ethnic_code_concept_id Reference to the ethnicity of the patient EMIS
registered_practice_organization_id Reference to the organisation the patient is registered at EMIS
birth_year EMIS
birth_month EMIS
birth_week EMIS

Table: patient_additional

Column name Comments Providing publisher
id Unique Id of the patient
property_id IM reference (e.g. cause of death)
value_id IM reference (e.g. COVID)
json_value Where there is no mapped value_id, just raw JSON
text_value Where there is no mapped value_id or raw JSON, just a basic text value



Retrieved from ‘https://wiki.discoverydataservice.org/index.php?title=Compass_2_Schema_Mappings&oldid=4953’