Remote Subscriber Database (RSD) Schema (Compass 2)

Table: allergy_intolerance

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	ExpandReferences
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	ExpandReferences
id	bigint	NOT NULL	Unique Id of the appointment
organization_id	varchar(36)	NOT NULL	Owning organisation (i.e. publisher)	organization.id
patient_id	varchar(36)	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	ExpandReferences
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	ExpandReferences
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	datetime	NOT NULL	The timestamp of the last update to the concept
CONSTRAINT PRIMARY KEY (dbid),

Table: concept_map

PRIMARY KEY (legacy)

Column name	Data type	Constraint	Comments	ExpandReferences
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
CONSTRAINT PRIMARY KEY (legacy)

Table: concept_property_object

Column name	Data type	Constraint	Comments	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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
service_provider_organization_id	bigint(20)	DEFAULT NULL	Reference to the service provider organisation
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	ExpandReferences
id	bigint	NOT NULL	same as the id column on the encounter table
property_id	bigint	NOT NULL	IM reference (i.e. Admission method)
value_id	bigint	NOT NULL	IM reference (i.e. Emergency admission)
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	ExpandReferences
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	ExpandReferences
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	date	DEFAULT NULL	The date the registration was started  for this episode of care
date_registered_end date	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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
is_active	tinyint(1)	NOT NULL	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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	smallint(6)	DEFAULT NULL	Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)
is_active	tinyint(4)	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	double	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	ExpandReferences
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	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)
result_value	double	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	bigint(20)	DEFAULT NULL	Reference to the clinical coding of the result
is_problem	tinyint(1)	NOT NULL	Whether the observation is marked as a problem
is_review	tinyint(1)	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: organization

PRIMARY KEY (pk_organization_id PRIMARY KEY (id))

Column name	Data type	Constraint	Comments	ExpandReferences
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	ExpandReferences
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(50)		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
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_address

PRIMARY KEY (organization_id,id,patient_id,person_id)

Column name	Data type	Constraint	Comments	ExpandReferences
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	ExpandReferences
Id	bigint(20)	NOT NULL	Reference to the patient_address table
Uprn	varchar(255)	NOT NULL	The Unique Propery Reference Number of the address
Status	smallint(6)	DEFAULT NULL	Whether the UPRN is active
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
Xcoordinate	double	DEFAULT NULL	The x coordinate of the 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
Algorithm	varchar(255)	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(10)	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 PRIMARY KEY (id,uprn)

Table: patient_contact

PRIMARY KEY (organization_id,id,patient_id,person_id)

Column name	Data type	Constraint	Comments	ExpandReferences
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	date	DEFAULT NULL	The start date of the contact being valid
end_date 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	ExpandReferences
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	ExpandReferences
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	tinyint(1)	NOT NULL	Whether the nhs number is valid
is_nhs_number_verified_by_publisher	tinyint(1)	NOT NULL	Whether the nhs number has been verified by the publisher
CONSTRAINT PRIMARY KEY (organization_id)

Table: patient_uprn

PRIMARY KEY (organization_id,person_id,id)

Column name	Data type	Constraint	Comments	ExpandReferences
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	tinyint(1)	DEFAULT NULL
invalid_address	tinyint(1)	DEFAULT NULL
missing_postcode	tinyint(1)	DEFAULT NULL
invalid_postcode	tinyint(1)	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	ExpandReferences
id	bigint	NOT NULL	Unique Id of the person
organization_id	bigint	NOT NULL	Owning organisation (i.e. publisher)
title	varchar(50)	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
CONSTRAINT pk_person_id PRIMARY KEY (id)

Table: practitioner

PRIMARY KEY (id)

Column name	Data type	Constraint	Comments	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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	ExpandReferences
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
CONSTRAINT PRIMARY KEY (organization_id,id,patient_id,person_id)

Table: schedule

PRIMARY KEY (organization_id, id)

Column name	Data type	Constraint	Comments	ExpandReferences
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