Table: allergy_intolerance
PRIMARY KEY (organization_id,person_id,id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
id
|
bigint
|
NOT NULL
|
Unique Id of the allergy
|
|
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 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
|
|
CONSTRAINT pk_allergy_intolerance_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_allergy_intolerance_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_allergy_intolerance_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_allergy_intolerance_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: appointment
PRIMARY KEY (organization_id,person_id,id)
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
|
bigint
|
DEFAULT NULL
|
The clinician the activity is recorded against
|
practitioner.id
|
schedule_id
|
bigint
|
DEFAULT NULL
|
The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule
|
schedule.id
|
start_date
|
date
|
DEFAULT NULL
|
The start date of the appointment
|
|
planned_duration
|
int
|
DEFAULT NULL
|
The time allocated for the appointment, not necessarily the actual duration always in minutes
|
|
actual_duration
|
int
|
DEFAULT NULL
|
Time between sent in and left always in minutes
|
|
appointment_status_concept_id
|
int
|
DEFAULT NULL
|
The status of the appointment e.g. arrived/sent in/left/DNA
|
|
patient_wait
|
int
|
DEFAULT NULL
|
How long the patient waited from being marked as arrived to being sent in
|
|
patient_delay
|
int
|
DEFAULT NULL
|
How long the patient was delayed for
|
|
date_time_sent_in
|
datetime
|
DEFAULT NULL
|
Date and time the patient was sent into the practitioner
|
|
date_time_left
|
datetime
|
DEFAULT NULL
|
Date and time the patient left the practitioner
|
|
source_id
|
varchar(36)
|
DEFAULT NULL
|
Unique reference to the source of the appointment
|
|
cancelled_date
|
datetime
|
DEFAULT NULL
|
The date the appointment was cancelled
|
|
CONSTRAINT pk_appointment_id PRIMARY KEY (organization_id,person_id,id),
|
CONSTRAINT fk_appointment_organization_id FOREIGN KEY (organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_appointment_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: appointment_status
PRIMARY KEY (id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
id
|
smallint(6)
|
NOT NULL
|
Unique Id of the appointment status
|
|
Value
|
varchar(50)
|
NOT NULL
|
The value of the appointment status
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: concept
PRIMARY KEY (dbid)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
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
|
|
CONSTRAINT PRIMARY KEY (dbid),
|
Table: concept_map
PRIMARY KEY (id)
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
|
int(11)
|
|
|
|
deleted
|
tinyint(1)
|
|
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: concept_property_object
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
dbid
|
int(11)
|
NOT NULL
|
Unique Id of the concept
|
|
group
|
int(11)
|
NOT NULL
|
Grouping identifier
|
|
property
|
int(11)
|
NOT NULL
|
The property concept
|
|
value
|
int(11)
|
NOT NULL
|
The value concept for the given concepts property
|
|
updated
|
datetime
|
NOT NULL
|
Timestamp the property value was updated/added
|
|
Table: concept_tct
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Source
|
int(11)
|
DEFAULT NULL
|
Source concept
|
|
Property
|
int(11)
|
DEFAULT NULL
|
Property concept
|
|
Level
|
int(11)
|
DEFAULT NULL
|
The "distance" of the from source (1=parent, 2=grandparent, etc)
|
|
Target
|
int(11)
|
DEFAULT NULL
|
The target
|
|
Status
|
int(11)
|
DEFAULT NULL
|
Whether the concept is active
|
|
created_date
|
datetime
|
DEFAULT NULL
|
When the concept was created
|
|
Table: consent_code
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
STATUS
|
varchar(10)
|
DEFAULT NULL
|
Whether the consent code is active
|
|
DESCRIPTION
|
varchar(100)
|
DEFAULT NULL
|
Description of the consent code
|
|
CODE
|
varchar(20)
|
DEFAULT NULL
|
The actual consent code
|
|
TERM_CODE
|
varchar(20)
|
DEFAULT NULL
|
The clinical term code
|
|
TERM
|
varchar(100)
|
DEFAULT NULL
|
The clinical term
|
|
Table: date_precision
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
|
|
Value
|
varchar(11)
|
NOT NULL
|
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: diagnostic_order
PRIMARY KEY (organization_id,person_id,id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
id
|
bigint
|
NOT NULL
|
Unique Id of the diagnostic order
|
|
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
|
|
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
|
|
CONSTRAINT pk_diagnostic_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_diagnostic_order_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_diagnostic_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_diagnostic_order_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: encounter
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_encounter_id PRIMARY KEY (organization_id,person_id,id),
|
CONSTRAINT fk_encounter_appointment_id FOREIGN KEY (appointment_id)
|
REFERENCES appointment (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
CONSTRAINT fk_encounter_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_episode_of_care_id FOREIGN KEY (episode_of_care_id)
|
REFERENCES episode_of_care (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: encounter_additional
PRIMARY KEY (id, property_id, value_id)
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)
|
|
CONSTRAINT pk_encounter_additional_id PRIMARY KEY (id, property_id, value_id)
|
Table: encounter_event
PRIMARY KEY (organization_id, person_id, id)
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
|
|
CONSTRAINT pk_encounter_event_id PRIMARY KEY (organization_id, person_id, id),
|
CONSTRAINT fk_encounter_event_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_event_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_event_episode_of_care_id FOREIGN KEY (episode_of_care_id)
|
REFERENCES episode_of_care (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_encounter_event_service_provider_organization_id FOREIGN KEY (service_provider_organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: episode_of_care
PRIMARY KEY (organization_id,person_id,id)
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
|
int
|
DEFAULT NULL
|
Reference to the registration type of the patient
|
registration.type.id
|
registration_status_concept_id
|
int
|
DEFAULT NULL
|
Reference to the registration status of the patient
|
|
date_registered
|
date
|
DEFAULT NULL
|
The date the registration was started for this episode of care
|
|
date_registered_end
|
date
|
DEFAULT NULL
|
The date the registration was ended for this episode of care
|
|
usual_gp_practitioner_id
|
bigint
|
DEFAULT NULL
|
Reference to the usual GP for this episode of care
|
Practitioner.id
|
CONSTRAINT pk_episode_of_care_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_episode_of_care_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_episode_of_care_practitioner_id FOREIGN KEY (usual_gp_practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: ethnicity_lookup
PRIMARY KEY(ethnic_code)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
ethnic_code
|
char(1)
|
NOT NULL
|
Ethnic code
|
|
ethnic_name
|
varchar(100)
|
DEFAULT NULL
|
Corresponding ethnicity name
|
|
CONSTRAINT PRIMARY KEY (ethnic_code)
|
Table: event_log
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)
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
|
|
CONSTRAINT pk_flag_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_flag_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: location
PRIMARY KEY (pk_location_id PRIMARY KEY id)
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
|
|
CONSTRAINT pk_location_id PRIMARY KEY (id)
|
CONSTRAINT fk_location_organisation_id FOREIGN KEY (managing_organization_id)
|
REFERENCES organization (id)
|
MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: medication_order
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_medication_order_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_medication_order_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_medication_order_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_medication_order_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: medication_statement
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_medication_statement_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_medication_statement_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_medication_statement_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_medication_statement_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: observation
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_observation_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_observation_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_observation_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_observation_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: observation_additional
PRIMARY KEY (id, property_id)
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
|
|
CONSTRAINT pk_observation_additional_id PRIMARY KEY (id, property_id)
|
Table: organization
PRIMARY KEY (pk_organization_id PRIMARY KEY (id))
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
|
|
PRIMARY KEY pk_organization_id PRIMARY KEY (id)
|
Table: patient
PRIMARY KEY (organization_id,person_id,id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
id
|
bigint
|
NOT NULL
|
Unique Id of the patient
|
|
organization_id
|
bigint
|
NOT NULL
|
Owning organisation (i.e. publisher)
|
organization.id
|
person_id
|
bigint
|
NOT NULL
|
Unique individual across all organisations
|
person.id
|
title
|
varchar(255)
|
|
The title of the patient
|
|
first_names
|
varchar(255)
|
|
The first names of the patient
|
|
last_name
|
varchar(255)
|
|
The last name of the patient
|
|
gender_concept_id
|
int
|
|
Reference to the gender of the patient
|
patient.gender.id
|
nhs_number
|
varchar(255)
|
|
The NHS number of the patient
|
|
date_of_birth
|
date
|
|
The date of birth of the patient
|
|
date_of_death
|
date
|
|
The date of death of the patient
|
|
current_address_id
|
bigint
|
|
Reference to the current address of the patient
|
|
ethnic_code_concept_id
|
int
|
|
Reference to the ethnicity of the patient
|
|
registered_practice_organization_id
|
bigint
|
|
Reference to the organisation the patient is registered at
|
|
birth_year
|
smallint
|
|
|
|
birth_month
|
tinyint
|
|
|
|
birth_week
|
tinyint
|
|
|
|
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
CONSTRAINT fk_patient_organization_id FOREIGN KEY (organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
Table: patient_additional
PRIMARY KEY (id, property_id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
id
|
bigint
|
NOT NULL
|
Unique Id of the patient
|
|
property_id
|
int
|
NOT NULL
|
IM reference (e.g. cause of death)
|
|
value_id
|
int
|
NULL
|
IM reference (e.g. COVID)
|
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
|
|
CONSTRAINT pk_patient_additional_id PRIMARY KEY (id, property_id)
|
Table: patient_address
PRIMARY KEY (organization_id,id,patient_id,person_id)
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
|
|
CONSTRAINT pk_organization_id_id_patient_id_person_id
|
PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`)
|
CONSTRAINT fk_patient_address_patient_id_organization_id
|
FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id)
|
COMMENT 'stores address details for patients'
|
Table: patient_address_match
PRIMARY KEY(id, uprn)
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
|
|
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)
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
|
|
|
CONSTRAINT pk_patient_address_ralf PRIMARY KEY (id, patient_address_id, patient_address_match_uprn_ralf00)
|
CONSTRAINT fk_patient_address_ralf_organization_id FOREIGN KEY (organization_id) REFERENCES organization (id)
|
CONSTRAINT fk_patient_address_ralf_patient_id FOREIGN KEY (patient_id) REFERENCES patient (id)
|
CONSTRAINT fk_patient_address_ralf_person_id FOREIGN KEY (person_id) REFERENCES person (id)
|
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)
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
|
|
CONSTRAINT pk_organization_id_id_patient_id_person_id PRIMARY KEY (`organization_id`,`id`,`patient_id`,`person_id`),
|
CONSTRAINT fk_patient_contact_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id)
|
COMMENT 'stores contact details (e.g. phone) for patients'
|
Table: patient_gender
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
Unique Id of the gender lookup
|
|
Value
|
varchar(10)
|
NOT NULL
|
Value of the gender lookup
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: patient_pseudo_id
PRIMARY KEY(organization_id)
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
|
|
CONSTRAINT PRIMARY KEY (organization_id)
|
CONSTRAINT fk_patient_pseudo_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id)
|
Table: patient_uprn
PRIMARY KEY (organization_id,person_id,id)
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
|
|
|
CONSTRAINT pk_patient_id_organization_id PRIMARY KEY (`organization_id`,`person_id`,`patient_id`),
|
CONSTRAINT fk_patient_uprn_patient_id_organisation_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: person
PRIMARY KEY (id)
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
|
|
|
CONSTRAINT pk_person_id PRIMARY KEY (id)
|
Table: practitioner
PRIMARY KEY (id)
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
|
|
CONSTRAINT pk_practitioner_id PRIMARY KEY (id),
|
CONSTRAINT fk_practitioner_organisation_id FOREIGN KEY (organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: procedure_request
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_procedure_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
/*got an Emis procedure request referring to a confidential encounter, so can't enforce this
|
CONSTRAINT fk_procedure_request_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,*/
|
CONSTRAINT fk_procedure_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_procedure_request_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: procedure_request_status
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
Unique Id of the procedure request status lookup
|
|
Value
|
varchar(50)
|
NOT NULL
|
The value of the procedure request status lookup
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: pseudo_id
PRIMARY KEY (patient_id, salt_key_name)
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
|
|
CONSTRAINT pk_pseudo_id PRIMARY KEY (patient_id, salt_key_name)
|
Table: referral_request
PRIMARY KEY (organization_id,person_id,id)
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
|
|
CONSTRAINT pk_referral_request_id PRIMARY KEY (`organization_id`,`person_id`,`id`),
|
/*got an Emis referral request referring to a confidential encounter, so can't enforce this
|
CONSTRAINT fk_referral_request_encounter_id FOREIGN KEY (encounter_id)
|
REFERENCES encounter (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,*/
|
CONSTRAINT fk_referral_request_patient_id_organization_id FOREIGN KEY (patient_id, organization_id)
|
REFERENCES patient (id, organization_id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_referral_request_practitioner_id FOREIGN KEY (practitioner_id)
|
REFERENCES practitioner (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_referral_request_recipient_organization_id FOREIGN KEY (recipient_organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
CONSTRAINT fk_referral_request_requester_organization_id FOREIGN KEY (requester_organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
Table: referral_request_priority
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
Unique Id of the referral request priority lookup
|
|
Value
|
varchar(50)
|
NOT NULL
|
The value of the referral request priority lookup
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: referral_request_type
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
Unique Id of the referral request type
|
|
Value
|
varchar(50)
|
NOT NULL
|
The value of the referral request lookup
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: registration_status
PRIMARY KEY(id)
Column name
|
Data type
|
Constraint
|
Comments
|
References
|
Id
|
smallint(6)
|
NOT NULL
|
Unique Id of the registration status
|
|
Code
|
varchar(10)
|
NOT NULL
|
The registration status code
|
|
Description
|
varchar(50)
|
NOT NULL
|
The description of the registration status
|
|
is_active
|
tinyint(1)
|
NOT NULL
|
Whether the registration status is active
|
|
CONSTRAINT PRIMARY KEY (id)
|
Table: registration_status_history
PRIMARY KEY(organization_id,id,patient_id,person_id)
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
|
|
PRIMARY KEY (organization_id,id,patient_id,person_id)
|
CONSTRAINT fk_registration_status_history_episode_id FOREIGN KEY (episode_of_care_id)
|
Table: schedule
PRIMARY KEY (organization_id, id)
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
|
|
CONSTRAINT pk_schedule_id PRIMARY KEY (organization_id, id),
|
CONSTRAINT fk_schedule_organization_id FOREIGN KEY (organization_id)
|
REFERENCES organization (id) MATCH SIMPLE
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|