CompassV2TestPages:v2.X

Remote Subscriber Database (RSD) Schema (Compass 2) Version: v2.1.2

allergy_intolerance

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the allergy'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter this allergy was record in'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the clinical code is recorded for'
date_precision_concept_id	int	DEFAULT NULL	COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'
is_review	tinyint(1)	NOT NULL	COMMENT 'Is this instance of the code a review of a previous encounter'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the allergy'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the allergy'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age the patient was at the time of this event'
date_recorded	datetime	NOT NULL	COMMENT 'The date the allergy was recorded'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `allergy_intolerance_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `allergy_intolerance_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)

appointment

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the appointment'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
schedule_id	bigint	DEFAULT NULL	COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule'
start_date	date	DEFAULT NULL	COMMENT 'The start date of the appointment'
planned_duration	int	DEFAULT NULL	COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes'
actual_duration	int	DEFAULT NULL	COMMENT 'Time between sent in and left always in minutes'
appointment_status_concept_id	int	DEFAULT NULL	COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA'
patient_wait	int	DEFAULT NULL	COMMENT 'How long the patient waited from being marked as arrived to being sent in'
patient_delay	int	DEFAULT NULL	COMMENT 'How long the patient was delayed for'
date_time_sent_in	datetime	DEFAULT NULL	COMMENT 'Date and time the patient was sent into the practitioner'
date_time_left	datetime	DEFAULT NULL	COMMENT 'Date and time the patient left the practitioner'
source_id	varchar(36)	DEFAULT NULL	COMMENT 'Unique reference to the source of the appointment'
cancelled_date	datetime	DEFAULT NULL	COMMENT 'The date the appointment was cancelled'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `appointment_patient_id` CONSTRAINT COLUMNS (patient_id)

appointment_additional

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'same as the id column on the patient table '
property_id	int	NOT NULL	COMMENT 'IM reference (e.g. appointment type)'
value_id	int	DEFAULT NULL	COMMENT 'IM reference (e.g. Appointment Type)'
json_value	json	DEFAULT NULL	COMMENT 'where there is no mapped value_id, just raw JSON'
text_value	varchar(255)	DEFAULT NULL	COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),
CONSTRAINT NAME `appointment_additional_value_id` CONSTRAINT COLUMNS (value_id)

concept

Column Name	Type	Constraint	Comment
dbid	int	NOT NULL	COMMENT 'Unique Id of the concept'
Document	int	NOT NULL	COMMENT 'Concept grouping construct, deprecated'
Id	varchar(150)	NOT NULL	COMMENT 'Unique concept identifier'
Draft	tinyint(1)	NOT NULL	COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept'
Name	varchar(255)	DEFAULT NULL	COMMENT 'Short name'
Description	varchar(400)	DEFAULT NULL	COMMENT 'Full name (or term for ontological concepts)'
Scheme	bigint	DEFAULT NULL	COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)'
Code	varchar(40)	DEFAULT NULL	COMMENT 'The code (non-unique unless coupled with a scheme)'
use_count	bigint	NOT NULL	DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept'
updated	datetime	NOT NULL	COMMENT 'The timestamp of the last update to the concept'
PRIMARY KEY CONSTRAINT(s) `dbid`),
CONSTRAINT NAME `ix_scheme_code` CONSTRAINT COLUMNS (Scheme,Code),
CONSTRAINT NAME `ix_code` CONSTRAINT COLUMNS (Code),
CONSTRAINT NAME `ix_dbid_code` CONSTRAINT COLUMNS (dbid,Code)

concept_map

Column Name	Type	Constraint	Comment
legacy	int	NOT NULL	COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'
core	int	NOT NULL	COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to'
updated	datetime	NOT NULL	COMMENT 'Timestamp the map was last updated/added'
id	int	NOT NULL,
deleted	tinyint(1)	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `id`),
CONSTRAINT NAME `ix_legacy_core` CONSTRAINT COLUMNS (legacy,core)

diagnostic_order

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the diagnostic order'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter the observation was recorded at'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the diagnostic order was identified by a clinician'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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	COMMENT 'The value of the result of the observation'
result_value_units	varchar(50)	DEFAULT NULL	COMMENT 'The units of the result of the observation'
result_date	date	DEFAULT NULL	COMMENT 'The date of the result'
result_text	text	COMMENT 'Any	text associated with the result'
result_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the result'
is_problem	tinyint(1)	NOT NULL	COMMENT 'Whether the observation is marked as a problem'
is_review	tinyint(1)	NOT NULL	COMMENT 'Whether the observation is a review of an existing problem'
problem_end_date	date	DEFAULT NULL	COMMENT 'The end date of the problem'
parent_observation_id	bigint	DEFAULT NULL	COMMENT '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	COMMENT 'Reference to the clinical coding of the observation'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the observation'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age of the patient at the time of the observation'
episodicity_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the episodicity of the problem eg First, review, flare'
is_primary	tinyint(1)	DEFAULT NULL	COMMENT 'Whether the diagnostic order is a primary order'
date_recorded	datetime	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `diagnostic_order_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `diagnostic_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id),
CONSTRAINT NAME `diagnostic_order_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem),
CONSTRAINT NAME `diagnostic_order_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value),
CONSTRAINT NAME `diagnostic_order_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id),
CONSTRAINT NAME `ix_diagnostic_order_organization_id` CONSTRAINT COLUMNS (organization_id),
CONSTRAINT NAME `ix_diagnostic_order_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date),
CONSTRAINT NAME `ix_diagnostic_order_person_id` CONSTRAINT COLUMNS (person_id)

encounter

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the encounter'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
appointment_id	bigint	DEFAULT NULL	COMMENT 'Reference to the appointment this encounter took part on'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the clinical code is recorded for'
date_precision_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the precision of the date of the encounter'
episode_of_care_id	bigint	DEFAULT NULL	COMMENT 'Is this instance of the code a review of a previous encounter'
service_provider_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the service provider organisation'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the type of encounter'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the type of encounter'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age the patient was when this encounter took place'
Type	text	COMMENT 'Unused',
sub_type	text	COMMENT 'Unused',
admission_method	varchar(40)	DEFAULT NULL	COMMENT 'The admission method of the encounter'
end_date	date	DEFAULT NULL	COMMENT 'The end date of the encounter'
institution_location_id	text	COMMENT 'Reference	to the institution the encounter took place at'
date_recorded	datetime	DEFAULT NULL	COMMENT 'The date the encounter was recorded'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `encounter_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `fki_encounter_appointment_id` CONSTRAINT COLUMNS (appointment_id),
CONSTRAINT NAME `fki_encounter_patient_id_organization_id` CONSTRAINT COLUMNS (patient_id,organization_id),
CONSTRAINT NAME `encounter_core_concept_id_clinical_effective_date` CONSTRAINT COLUMNS (core_concept_id,clinical_effective_date)

encounter_additional

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'same as the id column on the encounter table'
property_id	int	NOT NULL	COMMENT 'IM concept id reference (i.e. Admission method)'
value_id	int	DEFAULT NULL	COMMENT 'IM concept id reference (i.e. Emergency admission)'
json_value	json	DEFAULT NULL	COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)'
text_value	varchar(255)	DEFAULT NULL	COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)'
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),
CONSTRAINT NAME `encounter_additional_value_id` CONSTRAINT COLUMNS (value_id)

encounter_event

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the encounter event'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT 'The patient this event belongs to'
person_id	bigint	NOT NULL	COMMENT 'The person this event belongs to'
encounter_id	bigint	NOT NULL	COMMENT 'Reference to the parent encounter record'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
appointment_id	bigint	DEFAULT NULL	COMMENT 'Reference to the appointment this encounter took part on'
clinical_effective_date	datetime	DEFAULT NULL	COMMENT 'The date the encounter took place'
date_precision_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the precision of the date of the encounter'
episode_of_care_id	bigint	DEFAULT NULL	COMMENT 'Reference to the episode of care this encounter belongs to'
service_provider_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the service provider organisation'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the type of encounter'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the type of encounter'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age of the patient at the time of the encounter'
type	text	COMMENT 'Unused',
sub_type	text	COMMENT 'Unused',
admission_method	varchar(40)	DEFAULT NULL	COMMENT 'The admission method of the encounter'
end_date	date	DEFAULT NULL	COMMENT 'The end date of the encounter'
institution_location_id	bigint	DEFAULT NULL	COMMENT 'Reference to the institution the encounter took place at'
date_recorded	datetime	DEFAULT NULL	COMMENT 'The date the encounter was recorded'
finished	tinyint(1)	DEFAULT NULL	COMMENT 'Whether the encounter is finished'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),

episode_of_care

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the episode of care'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
registration_type_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the registration type of the patient'
registration_status_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the registration status of the patient'
date_registered	date	DEFAULT NULL	COMMENT 'The date the registration was started  for this episode of care'
date_registered_end	date	DEFAULT NULL	COMMENT 'The date the registration was ended  for this episode of care'
usual_gp_practitioner_id	bigint	DEFAULT NULL	COMMENT 'Reference to the usual GP for this episode of care'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `episode_of_care_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `episode_of_care_registration_type_concept_id` CONSTRAINT COLUMNS (registration_type_concept_id),
CONSTRAINT NAME `episode_of_care_date_registered` CONSTRAINT COLUMNS (date_registered),
CONSTRAINT NAME `episode_of_care_date_registered_end` CONSTRAINT COLUMNS (date_registered_end),
CONSTRAINT NAME `episode_of_care_person_id` CONSTRAINT COLUMNS (person_id),
CONSTRAINT NAME `episode_of_care_organization_id` CONSTRAINT COLUMNS (organization_id)

event_log

Column Name	Type	Constraint	Comment
dt_change	datetime(3)	NOT NULL	COMMENT 'date time the change was made to this DB'
change_type	tinyint	NOT NULL	COMMENT 'type of transaction 0=insert, 1=update, 2=delete'
table_id	tinyint	NOT NULL	COMMENT 'identifier of the table changed'
record_id	bigint	NOT NULL	COMMENT 'id of the record changed'

flag

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the flag'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
effective_date	date	DEFAULT NULL	COMMENT 'The date the flag was entered onto the patients record'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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	COMMENT 'Whether the flag is active or not'
flag_text	text	COMMENT 'This	is a warning set by the publisher regarding he patient'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `flag_patient_id` CONSTRAINT COLUMNS (patient_id)

location

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the location'
name	varchar(255)	DEFAULT NULL	COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary'
type_code	varchar(50)	DEFAULT NULL	COMMENT 'The type of location'
type_desc	varchar(255)	DEFAULT NULL	COMMENT 'Textual description of the type of location eg GP Practice'
postcode	varchar(10)	DEFAULT NULL	COMMENT 'The postcode of the location'
managing_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the managing organisation of the location'
PRIMARY KEY CONSTRAINT(s) `id`),
CONSTRAINT NAME `fk_location_managing_organisation_id` CONSTRAINT COLUMNS (managing_organization_id)

medication_order

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the medication order'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter the medication order was issued in'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the medication order was issued'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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	COMMENT 'Textual description of the dose'
quantity_value	double	DEFAULT NULL	COMMENT 'The value of the medication that was prescribed eg 50'
quantity_unit	varchar(255)	DEFAULT NULL	COMMENT 'The unit of the medication that was prescribed eg tablets'
duration_days	int	DEFAULT NULL	COMMENT 'How many days the medication is prescribed for'
estimated_cost	double	DEFAULT NULL	COMMENT 'The estimated cost of the medication'
medication_statement_id	bigint	DEFAULT NULL	COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication'
bnf_reference	varchar(6)	DEFAULT NULL	COMMENT 'A reference to the drug in the BNF dictionary'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age the patient was at the time of this event'
issue_method	text	COMMENT 'The	issue method of the medication eg hand written'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `medication_order_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `medication_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)

medication_statement

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the medication'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter this medication was recorded in'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the medication was clinical relevant'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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)	DEFAULT NULL	COMMENT 'Whether the medication is active or not'
cancellation_date	date	DEFAULT NULL	COMMENT 'The date the medication was cancelled'
dose	varchar(1000)	DEFAULT NULL	COMMENT 'Texual description of the dose of the medication'
quantity_value	double	DEFAULT NULL	COMMENT 'The value of the medication that was prescribed eg 50'
quantity_unit	varchar(255)	DEFAULT NULL	COMMENT 'The unit of the medication that was prescribed eg tablets'
authorisation_type_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the authorisation type'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the medication'
bnf_reference	varchar(6)	DEFAULT NULL	COMMENT 'A reference to the drug in the BNF dictionary'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age the patient was at the time of this event'
Issue_method	text	COMMENT 'The	issue method of the medication eg hand written'
date_recorded	datetime	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `medication_statement_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `medication_statement_dmd_id` CONSTRAINT COLUMNS (patient_id)

observation

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the observation'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter the observation was recorded at'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the observation was identified by a clinician'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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	COMMENT 'The value of the result of the observation'
result_value_units	varchar(50)	DEFAULT NULL	COMMENT 'The units of the result of the observation'
result_date	date	DEFAULT NULL	COMMENT 'The date of the result'
result_text	text	COMMENT 'Any	text associated with the result'
result_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the result'
is_problem	tinyint(1)	NOT NULL	COMMENT 'Whether the observation is marked as a problem'
is_review	tinyint(1)	NOT NULL	COMMENT 'Whether the observation is a review of an existing problem'
problem_end_date	date	DEFAULT NULL	COMMENT 'The end date of the problem'
parent_observation_id	bigint	DEFAULT NULL	COMMENT '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	COMMENT 'Reference to the clinical coding of the observation'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the observation'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age of the patient at the time of the observation'
episodicity_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the episodicity of the problem eg First, review, flare'
is_primary	tinyint(1)	DEFAULT NULL	COMMENT 'Whether the observation is a primary observation'
date_recorded	datetime	DEFAULT NULL	COMMENT 'The date the observation was recorded in the system'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `observation_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `observation_core_concept_id` CONSTRAINT COLUMNS (core_concept_id),
CONSTRAINT NAME `observation_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem),
CONSTRAINT NAME `observation_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value),
CONSTRAINT NAME `observation_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id),
CONSTRAINT NAME `ix_observation_organization_id` CONSTRAINT COLUMNS (organization_id),
CONSTRAINT NAME `ix_observation_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date),
CONSTRAINT NAME `ix_observation_person_id` CONSTRAINT COLUMNS (person_id)

observation_additional

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'same as the id column on the observation table'
property_id	int	NOT NULL	COMMENT 'IM reference (i.e. significance)'
value_id	int	DEFAULT NULL	COMMENT 'IM reference (i.e. minor, significant)'
json_value	json	DEFAULT NULL	COMMENT 'the JSON data itself '
text_value	varchar(255)	DEFAULT NULL	COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value'
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),
CONSTRAINT NAME `observation_additional_value_id` CONSTRAINT COLUMNS (value_id)

organization

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the organisation'
ods_code	varchar(50)	DEFAULT NULL	COMMENT 'ODS Code of the organisation'
Name	varchar(255)	DEFAULT NULL	COMMENT 'Name of the organisation'
type_code	varchar(50)	DEFAULT NULL	COMMENT 'The type of organisation'
type_desc	varchar(255)	DEFAULT NULL	COMMENT 'Textual description of the type of organisation eg GP Practice'
Postcode	varchar(10)	DEFAULT NULL	COMMENT 'The postcode of the organisation'
parent_organization_id	bigint	DEFAULT NULL	COMMENT 'The id of the parent organisation'
PRIMARY KEY CONSTRAINT(s) `id`),
CONSTRAINT NAME `fki_organization_parent_organization_id` CONSTRAINT COLUMNS (parent_organization_id)

organization_metadata

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'organisation ID, corresponds to same ID in the organizaton table'
publishing_software	varchar(50)	NOT NULL	COMMENT 'software name of publishing system, i.e. SystmOne'
last_data_to_dds	datetime	DEFAULT NULL	COMMENT 'date time data was last sent to DDS'
last_data_cutoff	datetime	DEFAULT NULL	COMMENT 'cutoff date time of the last extract from the publishing system'
PRIMARY KEY CONSTRAINT(s) `id`,`publishing_software`)

patient

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the patient'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
person_id	bigint	NOT NULL	COMMENT 'Unique individual across all organisations'
title	varchar(255)	DEFAULT NULL	COMMENT 'The title of the patient'
first_names	varchar(255)	DEFAULT NULL	COMMENT 'The first names of the patient'
last_name	varchar(255)	DEFAULT NULL	COMMENT 'The last name of the patient'
gender_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the gender of the patient'
nhs_number	varchar(255)	DEFAULT NULL	COMMENT 'The NHS number of the patient'
date_of_birth	date	DEFAULT NULL	COMMENT 'The date of birth of the patient'
date_of_death	date	DEFAULT NULL	COMMENT 'The date of death of the patient'
current_address_id	bigint	DEFAULT NULL	COMMENT 'Reference to the current address of the patient'
ethnic_code_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the ethnicity of the patient'
registered_practice_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the organisation the patient is registered at'
birth_year	smallint	DEFAULT NULL,
birth_month	tinyint	DEFAULT NULL,
birth_week	tinyint	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `patient_person_id` CONSTRAINT COLUMNS (person_id)

patient_additional

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'same as the id column on the patient table '
property_id	int	NOT NULL	COMMENT 'IM reference (e.g. Cause of death)'
value_id	int	DEFAULT NULL	COMMENT 'IM reference (e.g. COVID)'
json_value	json	DEFAULT NULL	COMMENT 'where there is no mapped value_id, just raw JSON'
text_value	varchar(255)	DEFAULT NULL	COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),
CONSTRAINT NAME `ix_patient_additional_id` CONSTRAINT COLUMNS (value_id)

patient_address

Column Name	Type	Constraint	Comment
Id	bigint	NOT NULL	COMMENT 'Unique Id of the address'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
address_line_1	varchar(255)	DEFAULT NULL	COMMENT 'The first line of the address'
address_line_2	varchar(255)	DEFAULT NULL	COMMENT 'The second line of the address'
address_line_3	varchar(255)	DEFAULT NULL	COMMENT 'The third line of the address'
address_line_4	varchar(255)	DEFAULT NULL	COMMENT 'The fourth line of the address'
city	varchar(255)	DEFAULT NULL	COMMENT 'The city'
postcode	varchar(255)	DEFAULT NULL	COMMENT 'The postcode'
use_concept_id	int	NOT NULL	COMMENT 'use of address (e.g. home, temporary)'
start_date	date	NOT NULL	COMMENT 'The start date of this address being relevant'
end_date	date	DEFAULT NULL	COMMENT 'The end date of this address being relevant'
lsoa_2001_code	varchar(9)	DEFAULT NULL	COMMENT 'A reference to the LSOA_2001 code'
lsoa_2011_code	varchar(9)	DEFAULT NULL	COMMENT 'A reference to the LSOA_2011 code'
msoa_2001_code	varchar(9)	DEFAULT NULL	COMMENT 'A reference to the MSOA_2001 code'
msoa_2011_code	varchar(9)	DEFAULT NULL	COMMENT 'A reference to the MSOA_2011 code'
ward_code	varchar(9)	DEFAULT NULL	COMMENT 'The ward the address belongs to'
local_authority_code	varchar(9)	DEFAULT NULL	COMMENT 'The local authority the address belongs to'
townsend_deprivation_index	double	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`Id`,`patient_id`,`person_id`),

patient_address_match

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	AUTO_INCREMENT
patient_address_id	bigint	NOT NULL,
uprn	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL
uprn_ralf00	varchar(255)	DEFAULT NULL,
status	tinyint(1)	DEFAULT NULL,
uprn_property_classification	varchar(45)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
latitude	double	DEFAULT NULL,
longitude	double	DEFAULT NULL,
uprn_xcoordinate	double	DEFAULT NULL,
uprn_ycoordinate	double	DEFAULT NULL,
qualifier	varchar(50)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_rule	varchar(4096)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_date	datetime	DEFAULT NULL,
abp_address_number	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
abp_address_street	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
abp_address_locality	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
abp_address_town	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
abp_address_postcode	varchar(10)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
abp_address_organization	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_pattern_postcode	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_pattern_street	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_pattern_number	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_pattern_building	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
match_pattern_flat	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
algorithm_version	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
epoch	varchar(255)	CHARACTER SET	utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL
PRIMARY KEY CONSTRAINT(s) `id`),
CONSTRAINT NAME `patient_address_uprn_index` CONSTRAINT COLUMNS (uprn),
CONSTRAINT NAME `patient_address_patient_address_id` CONSTRAINT COLUMNS (id,uprn)

patient_address_ralf

Column Name	Type	Constraint	Comment
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,
PRIMARY KEY CONSTRAINT(s) `id`,`patient_address_id`,`patient_address_match_uprn_ralf00`),
CONSTRAINT NAME `ux_patient_address_ralf_id` CONSTRAINT COLUMNS (id),
CONSTRAINT NAME `patient_address_ralf_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `patient_address_ralf_patient_address_id` CONSTRAINT COLUMNS (patient_address_id),
CONSTRAINT NAME `patient_address_ralf_patient_address_match_uprn_ralf_00` CONSTRAINT COLUMNS (patient_address_match_uprn_ralf00)

patient_contact

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the patient contact'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
use_concept_id	int	DEFAULT NULL	COMMENT 'use of contact (e.g. mobile, home,work'
type_concept_id	int	DEFAULT NULL	COMMENT 'type of contact (e.g. phone, email)'
start_date	date	DEFAULT NULL	COMMENT 'The start date of the contact being valid'
end_date	date	DEFAULT NULL	COMMENT 'The end date of the contact being valid'
value	varchar(255)	DEFAULT NULL	COMMENT 'The value of the contact information eg phone number, email address'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`),

patient_pseudo_id

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the patient pseudo id'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT 'Reference to the patient this registration status history belongs to'
person_id	bigint	NOT NULL	COMMENT 'Reference to the person this registration status history belongs to'
salt_name	varchar(50)	NOT NULL	COMMENT 'The name of the salt used to create the pseudo id'
Skid	varchar(255)	NOT NULL	COMMENT '"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	COMMENT 'Whether the nhs number is valid'
is_nhs_number_verified_by_publisher	tinyint(1)	NOT NULL	COMMENT 'Whether the nhs number has been verified by the publisher'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `patient_pseudo_id_patient` CONSTRAINT COLUMNS (patient_id)

patient_uprn

Column Name	Type	Constraint	Comment
patient_id	bigint	NOT NULL	COMMENT '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'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
person_id	bigint	NOT NULL	COMMENT 'Unique individual across all organisations'
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,
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`patient_id`),

person

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the person'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
title	varchar(255)	DEFAULT NULL	COMMENT 'The title of the person'
first_names	varchar(255)	DEFAULT NULL	COMMENT 'The first names of the person'
last_name	varchar(255)	DEFAULT NULL	COMMENT 'The last name of the person'
gender_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the gender of the person'
nhs_number	varchar(255)	DEFAULT NULL	COMMENT 'The NHS number of the person'
date_of_birth	date	DEFAULT NULL	COMMENT 'The date of birth of the person'
date_of_death	date	DEFAULT NULL	COMMENT 'The date of death of the person'
current_address_id	bigint	NOT NULL	COMMENT 'Reference to the current address of the person'
ethnic_code_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the ethnicity of the person'
registered_practice_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the organisation the person is registered at'
birth_year	smallint	DEFAULT NULL,
birth_month	tinyint	DEFAULT NULL,
birth_week	tinyint	DEFAULT NULL,
PRIMARY KEY CONSTRAINT(s) `id`),

practitioner

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the practitioner'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
name	varchar(1024)	DEFAULT NULL	COMMENT 'Name of the practitioner'
role_code	varchar(50)	DEFAULT NULL	COMMENT 'The code representing the role of the practitioner'
role_desc	varchar(255)	DEFAULT NULL	COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner'
gmc_code	varchar(50)	DEFAULT NULL	COMMENT 'The GMC code of the practitioner'
PRIMARY KEY CONSTRAINT(s) `id`),

procedure_request

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the procedure'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter the procedure was administered at'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the procedure was administered by a clinician'
date_precision_concept_id	int	DEFAULT NULL	COMMENT '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	COMMENT 'Reference to the status of the procedure'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the procedure'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the procedure'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age of the patient at the time of the procedure'
date_recorded	datetime	DEFAULT NULL	COMMENT 'The date the procedure was recorded in the source system'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `procedure_request_patient_id` CONSTRAINT COLUMNS (patient_id)

referral_request

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the referral'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT '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	COMMENT 'Unique individual across all organisations'
encounter_id	bigint	DEFAULT NULL	COMMENT 'Reference to the encounter the referral was made in'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'The clinician the activity is recorded against'
clinical_effective_date	date	DEFAULT NULL	COMMENT 'The date the referral was made'
date_precision_concept_id	smallint	DEFAULT NULL	COMMENT '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	DEFAULT NULL	COMMENT 'Reference to the organisation that made the refereral request'
recipient_organization_id	bigint	DEFAULT NULL	COMMENT 'Reference to the organization receiving the referral'
referral_request_priority_concept_id	smallint	DEFAULT NULL	COMMENT 'Reference to the priority of the referral'
referral_request_type_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the type of referral request'
Mode	varchar(50)	DEFAULT NULL	COMMENT 'The mode of the referral'
outgoing_referral	tinyint(1)	DEFAULT NULL	COMMENT 'Whether this is an outgoing referral'
is_review	tinyint(1)	DEFAULT NULL	COMMENT 'Whether this referral is a review'
core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the referral'
non_core_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the clinical coding of the referral'
age_at_event	decimal(5,2)	DEFAULT NULL	COMMENT 'The age of the patient at the time of the referral'
date_recorded	datetime	DEFAULT NULL	COMMENT 'The date the referral request was added to the source system'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),
CONSTRAINT NAME `referral_request_patient_id` CONSTRAINT COLUMNS (patient_id),
CONSTRAINT NAME `referral_request_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)

registration_status_history

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the registration status history'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
patient_id	bigint	NOT NULL	COMMENT 'Reference to the patient this registration status history belongs to'
person_id	bigint	NOT NULL	COMMENT 'Reference to the person this registration status history belongs to'
episode_of_care_id	bigint	DEFAULT NULL	COMMENT 'Reference to the episode of care this status history belongs to'
registration_status_concept_id	int	DEFAULT NULL	COMMENT 'Reference to the registration status'
start_date	datetime	DEFAULT NULL	COMMENT 'The start date for the period this registration status history was valid'
end_date	datetime	DEFAULT NULL	COMMENT 'The end date for the period this registration status history was valid'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`),

schedule

Column Name	Type	Constraint	Comment
id	bigint	NOT NULL	COMMENT 'Unique Id of the schedule'
organization_id	bigint	NOT NULL	COMMENT 'Owning organisation (i.e. publisher)'
practitioner_id	bigint	DEFAULT NULL	COMMENT 'Reference to the practitioner who owns the schedule'
start_date	date	DEFAULT NULL	COMMENT 'The start date of the schedule'
type	varchar(255)	DEFAULT NULL	COMMENT 'The type of schedule eg Timed Appointments'
location	varchar(255)	DEFAULT NULL	COMMENT 'Textual description of the location the schedule was held at'
name	varchar(150)	DEFAULT NULL	COMMENT 'The name of the schedule'
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`),