Remote Subscriber Database (RSD) Schema (Compass 2)

From Discovery Data Service
Jump to navigation Jump to search

Table: allergy_intolerance

PRIMARY KEY (organization_id,person_id,id)

Column name Data type Constraint Comments References
id bigint 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: 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: 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: 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: organization_metadata

PRIMARY KEY (id, publishing_software)

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
CONSTRAINT pk_organization_metadata PRIMARY KEY (id, publishing_software)

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_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: 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: 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



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