CompassV2TestPages:v2.X: Difference between revisions

From Discovery Data Service
Jump to navigation Jump to search
No edit summary
No edit summary
Line 1: Line 1:
<html class="client-nojs" lang="en-GB" dir="ltr">
<head>
<meta charset="UTF-8"/>
<title>Discovery Data Service Compass v2.1.1 Database Dump</title>
<div id="mw-head-base" class="noprint"></div>
<div id="mw-head-base" class="noprint"></div>
<div id="content" class="mw-body" role="main">
<div id="content" class="mw-body" role="main">
<div class="mw-indicators mw-body-content">
<a id="top"></a>
<div class="mw-indicators mw-body-content">
</div>
</div>


<h1 id="firstHeading" class="firstHeading" lang="en-GB">Remote Subscriber Database (RSD) Schema (Compass 2) Version: v1.2.1</h1>
<h1 id="firstHeading" class="firstHeading" lang="en-GB">Remote Subscriber Database (RSD) Schema (Compass 2) Version: v2.1.1</h1>
<div id="bodyContent" class="mw-body-content">
<div id="bodyContent" class="mw-body-content">
<div id="siteSub" class="noprint">From Discovery Data Service</div>
<div id="siteSub" class="noprint">From Discovery Data Service</div>
<div id="contentSub"></div>
<div id="contentSub"></div>
<h2><span class="mw-headline" id="Sortable_tables">Allergy_intolerance</span></h2>
<h2><span class="mw-headline" id="Sortable_tables">Allergy_intolerance</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>is_review</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the allergy',</td>
<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the allergy',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the allergy',</td>
<td> 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',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>clinical_effective_date</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the encounter this allergy was record in',</td>
<td> COMMENT 'The date the clinical code is recorded for',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this allergy was record in',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>organization_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>non_core_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'Reference to the clinical coding of the allergy',</td>
<td>  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)',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the allergy',</td>
<td> COMMENT 'Unique Id of the allergy',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the allergy',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>age_at_event</td>
<td>bigint</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>core_concept_id</td>
<td>datetime</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the allergy was recorded',</td>
<td> COMMENT 'Reference to the clinical coding of the allergy',</td>
<td>  date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>date_precision_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  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',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>date_recorded</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The date the allergy was recorded',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>encounter_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the clinical code is recorded for',</td>
<td> COMMENT 'Reference to the encounter this allergy was record in',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this allergy was record in',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_review</td>
<td>practitioner_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Appointment</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Appointment</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>patient_wait</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'How long the patient waited from being marked as arrived to being sent in',</td>
<td> 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',</td>
<td>  patient_wait int DEFAULT NULL COMMENT 'How long the patient waited from being marked as arrived to being sent in',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>schedule_id</td>
<td>start_date</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule',</td>
<td> COMMENT 'The start date of the appointment',</td>
<td>  schedule_id bigint DEFAULT NULL COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the appointment',</td>
</tr>
</tr>
<tr>
<tr>
<td>appointment_status_concept_id</td>
<td>organization_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  appointment_status_concept_id int DEFAULT NULL COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_time_sent_in</td>
<td>id</td>
<td>datetime</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Date and time the patient was sent into the practitioner',</td>
<td> COMMENT 'Unique Id of the appointment',</td>
<td>  date_time_sent_in datetime DEFAULT NULL COMMENT 'Date and time the patient was sent into the practitioner',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the appointment',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>appointment_status_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  appointment_status_concept_id int DEFAULT NULL COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_time_left</td>
<td>date_time_left</td>
<td>datetime</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Date and time the patient left the practitioner',</td>
<td> COMMENT 'Date and time the patient left the practitioner',</td>
<td>  date_time_left datetime DEFAULT NULL COMMENT 'Date and time the patient left the practitioner',</td>
<td>  date_time_left datetime DEFAULT NULL COMMENT 'Date and time the patient left the practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>planned_duration</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',</td>
<td>  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',</td>
<td>  planned_duration int DEFAULT NULL COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>date_time_sent_in</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Date and time the patient was sent into the practitioner',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  date_time_sent_in datetime DEFAULT NULL COMMENT 'Date and time the patient was sent into the practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>source_id</td>
<td>bigint</td>
<td>varchar(36)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the appointment',</td>
<td> COMMENT 'Unique reference to the source of the appointment',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the appointment',</td>
<td>  source_id varchar(36) DEFAULT NULL COMMENT 'Unique reference to the source of the appointment',</td>
</tr>
</tr>
<tr>
<tr>
<td>start_date</td>
<td>cancelled_date</td>
<td>date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The start date of the appointment',</td>
<td> COMMENT 'The date the appointment was cancelled',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the appointment',</td>
<td>  cancelled_date datetime DEFAULT NULL COMMENT 'The date the appointment was cancelled',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_delay</td>
<td>patient_delay</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'How long the patient was delayed for',</td>
<td> COMMENT 'How long the patient was delayed for',</td>
<td>  patient_delay int DEFAULT NULL COMMENT 'How long the patient was delayed for',</td>
<td>  patient_delay int DEFAULT NULL COMMENT 'How long the patient was delayed for',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>source_id</td>
<td>practitioner_id</td>
<td>varchar(36)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique reference to the source of the appointment',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  source_id varchar(36) DEFAULT NULL COMMENT 'Unique reference to the source of the appointment',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
<tr>
<tr>
<td>planned_duration</td>
<td>schedule_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',</td>
<td> COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule',</td>
<td>  planned_duration int DEFAULT NULL COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',</td>
<td>  schedule_id bigint DEFAULT NULL COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule',</td>
</tr>
</tr>
<tr>
<tr>
<td>actual_duration</td>
<td>actual_duration</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Time between sent in and left always in minutes',</td>
<td> COMMENT 'Time between sent in and left always in minutes',</td>
<td>  actual_duration int DEFAULT NULL COMMENT 'Time between sent in and left always in minutes',</td>
<td>  actual_duration int DEFAULT NULL COMMENT 'Time between sent in and left always in minutes',</td>
</tr>
</tr>
<tr>
<tr>
<td>cancelled_date</td>
<td>patient_wait</td>
<td>datetime</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the appointment was cancelled',</td>
<td> COMMENT 'How long the patient waited from being marked as arrived to being sent in',</td>
<td>  cancelled_date datetime DEFAULT NULL COMMENT 'The date the appointment was cancelled',</td>
<td>  patient_wait int DEFAULT NULL COMMENT 'How long the patient waited from being marked as arrived to being sent in',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Appointment_additional</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Appointment_additional</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>text_value</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'same as the id column on the patient table ',</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>json_value</td>
<td>property_id</td>
<td>json</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td> COMMENT 'IM reference (e.g. appointment type)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)',</td>
</tr>
</tr>
<tr>
<tr>
<td>text_value</td>
<td>value_id</td>
<td>varchar(255)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',</td>
<td> COMMENT 'IM reference (e.g. Appointment Type)',</td>
<td>  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)',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)',</td>
</tr>
</tr>
<tr>
<tr>
<td>property_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (e.g. appointment type)',</td>
<td> COMMENT 'same as the id column on the patient table ',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>
</tr>
</tr>
<tr>
<tr>
<td>value_id</td>
<td>json_value</td>
<td>int</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (e.g. Appointment Type)',</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Concept</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Concept</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>Name</td>
<td>Name</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Short name',</td>
<td> COMMENT 'Short name',</td>
<td>  Name varchar(255) DEFAULT NULL COMMENT 'Short name',</td>
<td>  Name varchar(255) DEFAULT NULL COMMENT 'Short name',</td>
</tr>
</tr>
<tr>
<tr>
<td>updated</td>
<td>Description</td>
<td>datetime</td>
<td>varchar(400)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The timestamp of the last update to the concept',</td>
<td> COMMENT 'Full name (or term for ontological concepts)',</td>
<td>  updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept',</td>
<td>  Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)',</td>
</tr>
</tr>
<tr>
<tr>
<td>Draft</td>
<td>Id</td>
<td>tinyint(1)</td>
<td>varchar(150)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>
<td> COMMENT 'Unique concept identifier',</td>
<td>  Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>
<td>  Id varchar(150) NOT NULL COMMENT 'Unique concept identifier',</td>
</tr>
</tr>
<tr>
<tr>
<td>Description</td>
<td>dbid</td>
<td>varchar(400)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Full name (or term for ontological concepts)',</td>
<td> COMMENT 'Unique Id of the concept',</td>
<td>  Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)',</td>
<td>  dbid int NOT NULL COMMENT 'Unique Id of the concept',</td>
</tr>
</tr>
<tr>
<tr>
<td>dbid</td>
<td>Scheme</td>
<td>int</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the concept',</td>
<td> COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>
<td>  dbid int NOT NULL COMMENT 'Unique Id of the concept',</td>
<td>  Scheme bigint DEFAULT NULL COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>
</tr>
</tr>
<tr>
<tr>
<td>Document</td>
<td>Code</td>
<td>int</td>
<td>varchar(40)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Concept grouping construct, deprecated',</td>
<td> COMMENT 'The code (non-unique unless coupled with a scheme)',</td>
<td>  Document int NOT NULL COMMENT 'Concept grouping construct, deprecated',</td>
<td>  Code varchar(40) DEFAULT NULL COMMENT 'The code (non-unique unless coupled with a scheme)',</td>
</tr>
</tr>
<tr>
<tr>
<td>Id</td>
<td>Document</td>
<td>varchar(150)</td>
<td>int</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique concept identifier',</td>
<td> COMMENT 'Concept grouping construct, deprecated',</td>
<td>  Id varchar(150) NOT NULL COMMENT 'Unique concept identifier',</td>
<td>  Document int NOT NULL COMMENT 'Concept grouping construct, deprecated',</td>
</tr>
</tr>
<tr>
<tr>
<td>Scheme</td>
<td>updated</td>
<td>bigint</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>
<td> COMMENT 'The timestamp of the last update to the concept',</td>
<td>  Scheme bigint DEFAULT NULL COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>
<td>  updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept',</td>
</tr>
</tr>
<tr>
<tr>
<td>Code</td>
<td>Draft</td>
<td>varchar(40)</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The code (non-unique unless coupled with a scheme)',</td>
<td> COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>
<td>  Code varchar(40) DEFAULT NULL COMMENT 'The code (non-unique unless coupled with a scheme)',</td>
<td>  Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Concept_map</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Concept_map</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>updated</td>
<td>updated</td>
<td>datetime</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Timestamp the map was last updated/added',</td>
<td> COMMENT 'Timestamp the map was last updated/added',</td>
<td>  updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added',</td>
<td>  updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added',</td>
</tr>
</tr>
<tr>
<tr>
<td>core</td>
<td>legacy</td>
<td>int</td>
<td>int</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>
<td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>
<td>  core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>
<td>  legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>
</tr>
</tr>
<tr>
<tr>
<td>legacy</td>
<td>core</td>
<td>int</td>
<td>int</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>
<td> COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>
<td>  legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>
<td>  core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Diagnostic_order</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Diagnostic_order</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>is_problem</td>
<td>parent_observation_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the observation is marked as a problem',</td>
<td> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',</td>
<td>  is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_concept_id</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the result',</td>
<td> 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',</td>
<td>  result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>problem_end_date</td>
<td>result_value</td>
<td>date</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the problem',</td>
<td> COMMENT 'The value of the result of the observation',</td>
<td>  problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',</td>
<td>  result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>problem_end_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'The end date of the problem',</td>
<td> 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)',</td>
<td>  problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>result_date</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The date of the result',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  result_date date DEFAULT NULL COMMENT 'The date of the result',</td>
</tr>
</tr>
<tr>
<tr>
<td>episodicity_concept_id</td>
<td>clinical_effective_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td> COMMENT 'The date the diagnostic order was identified by a clinician',</td>
<td>  episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_date</td>
<td>organization_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date of the result',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  result_date date DEFAULT NULL COMMENT 'The date of the result',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>core_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>is_problem</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> 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',</td>
<td> COMMENT 'Whether the observation is marked as a problem',</td>
<td>  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',</td>
<td>  is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_value_units</td>
<td>age_at_event</td>
<td>varchar(50)</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The units of the result of the observation',</td>
<td> COMMENT 'The age of the patient at the time of the observation',</td>
<td>  result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_primary</td>
<td>is_primary</td>
<td>tinyint(1)</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the diagnostic order is a primary order',</td>
<td> COMMENT 'Whether the diagnostic order is a primary order',</td>
<td>  is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the diagnostic order is a primary order',</td>
<td>  is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the diagnostic order is a primary order',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>result_value_units</td>
<td>bigint</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The units of the result of the observation',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>non_core_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the diagnostic order was identified by a clinician',</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_review</td>
<td>episodicity_concept_id</td>
<td>tinyint(1)</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the observation is a review of an existing problem',</td>
<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>
<td>  episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>result_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Reference to the clinical coding of the result',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>is_review</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td> COMMENT 'Whether the observation is a review of an existing problem',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_value</td>
<td>date_precision_concept_id</td>
<td>double</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the result of the observation',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>parent_observation_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  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',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the diagnostic order',</td>
<td> COMMENT 'Unique Id of the diagnostic order',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>encounter_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>practitioner_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age of the patient at the time of the observation',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Encounter</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>end_date</td>
<td>id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The end date of the encounter',</td>
<td> COMMENT 'Unique Id of the encounter',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>date_precision_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td> COMMENT 'Reference to the precision of the date of the encounter',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
<td>  date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>appointment_id</td>
<td>clinical_effective_date</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the appointment this encounter took part on',</td>
<td> COMMENT 'The date the clinical code is recorded for',</td>
<td>  appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>patient_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The age the patient was when this encounter took place',</td>
<td> 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',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was when this encounter took place',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>end_date</td>
<td>datetime</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the encounter was recorded',</td>
<td> COMMENT 'The end date of the encounter',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>admission_method</td>
<td>date_recorded</td>
<td>varchar(40)</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The admission method of the encounter',</td>
<td> COMMENT 'The date the encounter was recorded',</td>
<td>  admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the encounter',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the encounter',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>admission_method</td>
<td>bigint</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The admission method of the encounter',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>service_provider_organization_id</td>
<td>appointment_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the service provider organisation',</td>
<td> COMMENT 'Reference to the appointment this encounter took part on',</td>
<td>  service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>
<td>  appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td>  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',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>non_core_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>age_at_event</td>
<td>int</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td> COMMENT 'The age the patient was when this encounter took place',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was when this encounter took place',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>service_provider_organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Reference to the service provider organisation',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>core_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the clinical code is recorded for',</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>person_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the precision of the date of the encounter',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>episode_of_care_id</td>
<td>practitioner_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter_additional</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Encounter_additional</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>value_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>
<td> COMMENT 'same as the id column on the encounter table',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the encounter table',</td>
</tr>
</tr>
<tr>
<tr>
<td>json_value</td>
<td>property_id</td>
<td>json</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',</td>
<td> COMMENT 'IM concept id reference (i.e. Admission method)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',</td>
<td>  property_id int NOT NULL COMMENT 'IM concept id reference (i.e. Admission method)',</td>
</tr>
</tr>
<tr>
<tr>
<td>property_id</td>
<td>json_value</td>
<td>int</td>
<td>json</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM concept id reference (i.e. Admission method)',</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',</td>
<td>  property_id int NOT NULL COMMENT 'IM concept id reference (i.e. Admission method)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',</td>
</tr>
</tr>
<tr>
<tr>
<td>text_value</td>
<td>value_id</td>
<td>varchar(255)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)',</td>
<td> COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>
<td>  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)',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>text_value</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'same as the id column on the encounter table',</td>
<td> COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the encounter table',</td>
<td>  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)',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter_event</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Encounter_event</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>end_date</td>
<td>date_precision_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the encounter',</td>
<td> COMMENT 'Reference to the precision of the date of the encounter',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>
<td>  date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>end_date</td>
<td>decimal(5,2)</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age of the patient at the time of the encounter',</td>
<td> COMMENT 'The end date of the encounter',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the encounter',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>date_recorded</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the parent encounter record',</td>
<td> COMMENT 'The date the encounter was recorded',</td>
<td>  encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>organization_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>person_id</td>
<td>datetime</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date the encounter took place',</td>
<td> COMMENT 'The person this event belongs to',</td>
<td>  clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place',</td>
<td>  person_id bigint NOT NULL COMMENT 'The person this event belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>appointment_id</td>
<td>admission_method</td>
<td>bigint</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the appointment this encounter took part on',</td>
<td> COMMENT 'The admission method of the encounter',</td>
<td>  appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>
<td>  admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>appointment_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The person this event belongs to',</td>
<td> COMMENT 'Reference to the appointment this encounter took part on',</td>
<td>  person_id bigint NOT NULL COMMENT 'The person this event belongs to',</td>
<td>  appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>age_at_event</td>
<td>datetime</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the encounter was recorded',</td>
<td> COMMENT 'The age of the patient at the time of the encounter',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>admission_method</td>
<td>finished</td>
<td>varchar(40)</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The admission method of the encounter',</td>
<td> COMMENT 'Whether the encounter is finished',</td>
<td>  admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>
<td>  finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'Unique Id of the encounter event',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the encounter event',</td>
</tr>
</tr>
<tr>
<tr>
<td>service_provider_organization_id</td>
<td>clinical_effective_date</td>
<td>bigint</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the service provider organisation',</td>
<td> COMMENT 'The date the encounter took place',</td>
<td>  service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>
<td>  clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place',</td>
</tr>
</tr>
<tr>
<tr>
<td>finished</td>
<td>non_core_concept_id</td>
<td>tinyint(1)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the encounter is finished',</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td>  finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the encounter event',</td>
<td> COMMENT 'Reference to the episode of care this encounter belongs to',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the encounter event',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this encounter belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>service_provider_organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Reference to the service provider organisation',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>core_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td> COMMENT 'Reference to the type of encounter',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',</td>
</tr>
</tr>
<tr>
<tr>
<td>institution_location_id</td>
<td>institution_location_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the institution the encounter took place at',</td>
<td> COMMENT 'Reference to the institution the encounter took place at',</td>
<td>  institution_location_id bigint DEFAULT NULL COMMENT 'Reference to the institution the encounter took place at',</td>
<td>  institution_location_id bigint DEFAULT NULL COMMENT 'Reference to the institution the encounter took place at',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>practitioner_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The patient this event belongs to',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  patient_id bigint NOT NULL COMMENT 'The patient this event belongs to',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
<tr>
<tr>
<td>episode_of_care_id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the episode of care this encounter belongs to',</td>
<td> COMMENT 'The patient this event belongs to',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this encounter belongs to',</td>
<td>  patient_id bigint NOT NULL COMMENT 'The patient this event belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>encounter_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the precision of the date of the encounter',</td>
<td> COMMENT 'Reference to the parent encounter record',</td>
<td>  date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',</td>
<td>  encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Episode_of_care</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Episode_of_care</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the episode of care',</td>
<td> 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',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the episode of care',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>registration_status_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the registration status of the patient',</td>
<td> COMMENT 'Unique Id of the episode of care',</td>
<td>  registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the episode of care',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_registered</td>
<td>registration_status_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the registration was started  for this episode of care',</td>
<td> COMMENT 'Reference to the registration status of the patient',</td>
<td>  date_registered date DEFAULT NULL COMMENT 'The date the registration was started  for this episode of care',</td>
<td>  registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> 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',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  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',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>registration_type_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Reference to the registration type of the patient',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>usual_gp_practitioner_id</td>
<td>date_registered</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the usual GP for this episode of care',</td>
<td> COMMENT 'The date the registration was started  for this episode of care',</td>
<td>  usual_gp_practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the usual GP for this episode of care',</td>
<td>  date_registered date DEFAULT NULL COMMENT 'The date the registration was started  for this episode of care',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_registered_end</td>
<td>person_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date the registration was ended  for this episode of care',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  date_registered_end date DEFAULT NULL COMMENT 'The date the registration was ended  for this episode of care',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>registration_type_concept_id</td>
<td>date_registered_end</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the registration type of the patient',</td>
<td> COMMENT 'The date the registration was ended  for this episode of care',</td>
<td>  registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient',</td>
<td>  date_registered_end date DEFAULT NULL COMMENT 'The date the registration was ended  for this episode of care',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>usual_gp_practitioner_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Reference to the usual GP for this episode of care',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  usual_gp_practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the usual GP for this episode of care',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Event_log</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Event_log</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>dt_change</td>
<td>dt_change</td>
<td>datetime(3)</td>
<td>datetime(3)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'date time the change was made to this DB',</td>
<td> COMMENT 'date time the change was made to this DB',</td>
<td>  dt_change datetime(3) NOT NULL COMMENT 'date time the change was made to this DB',</td>
<td>  dt_change datetime(3) NOT NULL COMMENT 'date time the change was made to this DB',</td>
</tr>
</tr>
<tr>
<tr>
<td>change_type</td>
<td>change_type</td>
<td>tinyint</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'type of transaction 0=insert, 1=update, 2=delete',</td>
<td> COMMENT 'type of transaction 0=insert, 1=update, 2=delete',</td>
<td>  change_type tinyint NOT NULL COMMENT 'type of transaction 0=insert, 1=update, 2=delete',</td>
<td>  change_type tinyint NOT NULL COMMENT 'type of transaction 0=insert, 1=update, 2=delete',</td>
</tr>
</tr>
<tr>
<tr>
<td>table_id</td>
<td>table_id</td>
<td>tinyint</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'identifier of the table changed',</td>
<td> COMMENT 'identifier of the table changed',</td>
<td>  table_id tinyint NOT NULL COMMENT 'identifier of the table changed',</td>
<td>  table_id tinyint NOT NULL COMMENT 'identifier of the table changed',</td>
</tr>
</tr>
<tr>
<tr>
<td>record_id</td>
<td>record_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'id of the record changed'</td>
<td> COMMENT 'id of the record changed'</td>
<td>  record_id bigint NOT NULL COMMENT 'id of the record changed'</td>
<td>  record_id bigint NOT NULL COMMENT 'id of the record changed'</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Flag</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Flag</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>effective_date</td>
<td>patient_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date the flag was entered onto the patients record',</td>
<td> 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',</td>
<td>  effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>effective_date</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'The date the flag was entered onto the patients record',</td>
<td>  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',</td>
<td>  effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>is_active</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Whether the flag is active or not',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>organization_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  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)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>date_precision_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_active</td>
<td>person_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the flag is active or not',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the flag',</td>
<td> COMMENT 'Unique Id of the flag',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the flag',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the flag',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Location</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Location</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>name</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the location',</td>
<td> COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the location',</td>
<td>  name varchar(255) DEFAULT NULL COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',</td>
</tr>
</tr>
<tr>
<tr>
<td>type_code</td>
<td>type_desc</td>
<td>varchar(50)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The type of location',</td>
<td> COMMENT 'Textual description of the type of location eg GP Practice',</td>
<td>  type_code varchar(50) DEFAULT NULL COMMENT 'The type of location',</td>
<td>  type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of location eg GP Practice',</td>
</tr>
</tr>
<tr>
<tr>
<td>name</td>
<td>postcode</td>
<td>varchar(255)</td>
<td>varchar(10)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',</td>
<td> COMMENT 'The postcode of the location',</td>
<td>  name varchar(255) DEFAULT NULL COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',</td>
<td>  postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the location',</td>
</tr>
</tr>
<tr>
<tr>
<td>type_desc</td>
<td>managing_organization_id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the type of location eg GP Practice',</td>
<td> COMMENT 'Reference to the managing organisation of the location',</td>
<td>  type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of location eg GP Practice',</td>
<td>  managing_organization_id bigint DEFAULT NULL COMMENT 'Reference to the managing organisation of the location',</td>
</tr>
</tr>
<tr>
<tr>
<td>postcode</td>
<td>id</td>
<td>varchar(10)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The postcode of the location',</td>
<td> COMMENT 'Unique Id of the location',</td>
<td>  postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the location',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the location',</td>
</tr>
</tr>
<tr>
<tr>
<td>managing_organization_id</td>
<td>type_code</td>
<td>bigint</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the managing organisation of the location',</td>
<td> COMMENT 'The type of location',</td>
<td>  managing_organization_id bigint DEFAULT NULL COMMENT 'Reference to the managing organisation of the location',</td>
<td>  type_code varchar(50) DEFAULT NULL COMMENT 'The type of location',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Medication_order</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Medication_order</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>medication_statement_id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders',</td>
<td> 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',</td>
<td>  medication_statement_id bigint DEFAULT NULL COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>core_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>quantity_unit</td>
<td>encounter_id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td> COMMENT 'Reference to the encounter the medication order was issued in',</td>
<td>  quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the medication order was issued in',</td>
</tr>
</tr>
<tr>
<tr>
<td>duration_days</td>
<td>non_core_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'How many days the medication is prescribed for',</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  duration_days int DEFAULT NULL COMMENT 'How many days the medication is prescribed for',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>organization_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>quantity_value</td>
<td>bigint</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the encounter the medication order was issued in',</td>
<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the medication order was issued in',</td>
<td>  quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'Unique Id of the medication order',</td>
<td>  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)',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the medication order',</td>
</tr>
</tr>
<tr>
<tr>
<td>quantity_value</td>
<td>estimated_cost</td>
<td>double</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td> COMMENT 'The estimated cost of the medication',</td>
<td>  quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td>  estimated_cost double DEFAULT NULL COMMENT 'The estimated cost of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>estimated_cost</td>
<td>dose</td>
<td>double</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The estimated cost of the medication',</td>
<td> COMMENT 'Textual description of the dose',</td>
<td>  estimated_cost double DEFAULT NULL COMMENT 'The estimated cost of the medication',</td>
<td>  dose varchar(1000) DEFAULT NULL COMMENT 'Textual description of the dose',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>medication_statement_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication order was issued',</td>
<td> COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication order was issued',</td>
<td>  medication_statement_id bigint DEFAULT NULL COMMENT 'Reference to the medication statement.  A medication statement can have many medication orders',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>age_at_event</td>
<td>bigint</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
</tr>
</tr>
<tr>
<tr>
<td>dose</td>
<td>clinical_effective_date</td>
<td>varchar(1000)</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the dose',</td>
<td> COMMENT 'The date the medication order was issued',</td>
<td>  dose varchar(1000) DEFAULT NULL COMMENT 'Textual description of the dose',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication order was issued',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>quantity_unit</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td>  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',</td>
<td>  quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>date_precision_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the medication order',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the medication order',</td>
<td> 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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>duration_days</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'How many days the medication is prescribed for',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  duration_days int DEFAULT NULL COMMENT 'How many days the medication is prescribed for',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>person_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>bnf_reference</td>
<td>bigint</td>
<td>varchar(6)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>
</tr>
</tr>
<tr>
<tr>
<td>bnf_reference</td>
<td>practitioner_id</td>
<td>varchar(6)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Medication_statement</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Medication_statement</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>cancellation_date</td>
<td>authorisation_type_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication was cancelled',</td>
<td> COMMENT 'Reference to the authorisation type',</td>
<td>  cancellation_date date DEFAULT NULL COMMENT 'The date the medication was cancelled',</td>
<td>  authorisation_type_concept_id int DEFAULT NULL COMMENT 'Reference to the authorisation type',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td> 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',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>quantity_unit</td>
<td>core_concept_id</td>
<td>varchar(255)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>non_core_concept_id</td>
<td>decimal(5,2)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>organization_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  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)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>quantity_value</td>
<td>bigint</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the encounter this medication was recorded in',</td>
<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this medication was recorded in',</td>
<td>  quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>
</tr>
</tr>
<tr>
<tr>
<td>quantity_value</td>
<td>cancellation_date</td>
<td>double</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td> COMMENT 'The date the medication was cancelled',</td>
<td>  quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>
<td>  cancellation_date date DEFAULT NULL COMMENT 'The date the medication was cancelled',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>is_active</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the medication',</td>
<td> COMMENT 'Whether the medication is active or not',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the medication',</td>
<td>  is_active tinyint(1) DEFAULT NULL COMMENT 'Whether the medication is active or not',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>age_at_event</td>
<td>bigint</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The age the patient was at the time of this event',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>dose</td>
<td>date</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication was clinical relevant',</td>
<td> COMMENT 'Texual description of the dose of the medication',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication was clinical relevant',</td>
<td>  dose varchar(1000) DEFAULT NULL COMMENT 'Texual description of the dose of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> 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',</td>
<td> COMMENT 'Unique Id of the medication',</td>
<td>  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',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the medication',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>clinical_effective_date</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The date the medication was clinical relevant',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication was clinical relevant',</td>
</tr>
</tr>
<tr>
<tr>
<td>dose</td>
<td>quantity_unit</td>
<td>varchar(1000)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Texual description of the dose of the medication',</td>
<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
<td>  dose varchar(1000) DEFAULT NULL COMMENT 'Texual description of the dose of the medication',</td>
<td>  quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>date_precision_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the medication',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>authorisation_type_concept_id</td>
<td>bnf_reference</td>
<td>int</td>
<td>varchar(6)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the authorisation type',</td>
<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td>  authorisation_type_concept_id int DEFAULT NULL COMMENT 'Reference to the authorisation type',</td>
<td>  bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_active</td>
<td>practitioner_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the medication is active or not',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  is_active tinyint(1) DEFAULT NULL COMMENT 'Whether the medication is active or not',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
<tr>
<tr>
<td>bnf_reference</td>
<td>encounter_id</td>
<td>varchar(6)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td> COMMENT 'Reference to the encounter this medication was recorded in',</td>
<td>  bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this medication was recorded in',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Observation</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Observation</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>parent_observation_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the observation was identified by a clinician',</td>
<td> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_problem</td>
<td>patient_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is marked as a problem',</td>
<td> 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',</td>
<td>  is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_concept_id</td>
<td>result_value</td>
<td>int</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the result',</td>
<td> COMMENT 'The value of the result of the observation',</td>
<td>  result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>
<td>  result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>problem_end_date</td>
<td>problem_end_date</td>
<td>date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the problem',</td>
<td> COMMENT 'The end date of the problem',</td>
<td>  problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',</td>
<td>  problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>result_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'The date of the result',</td>
<td> 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)',</td>
<td>  result_date date DEFAULT NULL COMMENT 'The date of the result',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the observation',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the observation',</td>
<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>core_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>episodicity_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td> COMMENT 'Unique Id of the observation',</td>
<td>  episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_primary</td>
<td>is_problem</td>
<td>tinyint(1)</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is a primary observation',</td>
<td> COMMENT 'Whether the observation is marked as a problem',</td>
<td>  is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation',</td>
<td>  is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>date_recorded</td>
<td>datetime</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the observation was recorded in the system',</td>
<td> COMMENT 'The date the observation was recorded in the system',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the observation was recorded in the system',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the observation was recorded in the system',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_date</td>
<td>age_at_event</td>
<td>date</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of the result',</td>
<td> COMMENT 'The age of the patient at the time of the observation',</td>
<td>  result_date date DEFAULT NULL COMMENT 'The date of the result',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>clinical_effective_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td> COMMENT 'The date the observation was identified by a clinician',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>is_primary</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'Whether the observation is a primary observation',</td>
<td>  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',</td>
<td>  is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_value_units</td>
<td>result_value_units</td>
<td>varchar(50)</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The units of the result of the observation',</td>
<td> COMMENT 'The units of the result of the observation',</td>
<td>  result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',</td>
<td>  result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>non_core_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_review</td>
<td>episodicity_concept_id</td>
<td>tinyint(1)</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the observation is a review of an existing problem',</td>
<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>
<td>  episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>result_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Reference to the clinical coding of the result',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>is_review</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td> COMMENT 'Whether the observation is a review of an existing problem',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td>  is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>
</tr>
</tr>
<tr>
<tr>
<td>result_value</td>
<td>date_precision_concept_id</td>
<td>double</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the result of the observation',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>parent_observation_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  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',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>encounter_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation',</td>
<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>practitioner_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age of the patient at the time of the observation',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Observation_additional</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Observation_additional</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>value_id</td>
<td>json_value</td>
<td>int</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (i.e. minor, significant)',</td>
<td> COMMENT 'the JSON data itself ',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'the JSON data itself ',</td>
</tr>
</tr>
<tr>
<tr>
<td>json_value</td>
<td>value_id</td>
<td>json</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'the JSON data itself ',</td>
<td> COMMENT 'IM reference (i.e. minor, significant)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'the JSON data itself ',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>text_value</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'same as the id column on the observation table',</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the observation table',</td>
<td>  text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',</td>
</tr>
</tr>
<tr>
<tr>
<td>property_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (i.e. significance)',</td>
<td> COMMENT 'same as the id column on the observation table',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (i.e. significance)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the observation table',</td>
</tr>
</tr>
<tr>
<tr>
<td>text_value</td>
<td>property_id</td>
<td>varchar(255)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',</td>
<td> COMMENT 'IM reference (i.e. significance)',</td>
<td>  text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (i.e. significance)',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Organization</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Organization</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>type_desc</td>
<td>type_code</td>
<td>varchar(255)</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the type of organisation eg GP Practice',</td>
<td> COMMENT 'The type of organisation',</td>
<td>  type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice',</td>
<td>  type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>Postcode</td>
<td>parent_organization_id</td>
<td>varchar(10)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The postcode of the organisation',</td>
<td> COMMENT 'The id of the parent organisation',</td>
<td>  Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation',</td>
<td>  parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>ods_code</td>
<td>Postcode</td>
<td>varchar(50)</td>
<td>varchar(10)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'ODS Code of the organisation',</td>
<td> COMMENT 'The postcode of the organisation',</td>
<td>  ods_code varchar(50) DEFAULT NULL COMMENT 'ODS Code of the organisation',</td>
<td>  Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>type_code</td>
<td>ods_code</td>
<td>varchar(50)</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The type of organisation',</td>
<td> COMMENT 'ODS Code of the organisation',</td>
<td>  type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation',</td>
<td>  ods_code varchar(50) DEFAULT NULL COMMENT 'ODS Code of the organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>Name</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the organisation',</td>
<td> COMMENT 'Name of the organisation',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the organisation',</td>
<td>  Name varchar(255) DEFAULT NULL COMMENT 'Name of the organisation',</td>
</tr>
</tr>
<tr>
<tr>
<td>parent_organization_id</td>
<td>type_desc</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The id of the parent organisation',</td>
<td> COMMENT 'Textual description of the type of organisation eg GP Practice',</td>
<td>  parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation',</td>
<td>  type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice',</td>
</tr>
</tr>
<tr>
<tr>
<td>Name</td>
<td>id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Name of the organisation',</td>
<td> COMMENT 'Unique Id of the organisation',</td>
<td>  Name varchar(255) DEFAULT NULL COMMENT 'Name of the organisation',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the organisation',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Organization_metadata</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Organization_metadata</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>publishing_software</td>
<td>last_data_to_dds</td>
<td>varchar(50)</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'software name of publishing system, i.e. SystmOne',</td>
<td> COMMENT 'date time data was last sent to DDS',</td>
<td>  publishing_software varchar(50) NOT NULL COMMENT 'software name of publishing system, i.e. SystmOne',</td>
<td>  last_data_to_dds datetime DEFAULT NULL COMMENT 'date time data was last sent to DDS',</td>
</tr>
</tr>
<tr>
<tr>
<td>last_data_to_dds</td>
<td>id</td>
<td>datetime</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'date time data was last sent to DDS',</td>
<td> COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>
<td>  last_data_to_dds datetime DEFAULT NULL COMMENT 'date time data was last sent to DDS',</td>
<td>  id bigint NOT NULL COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>
</tr>
</tr>
<tr>
<tr>
<td>last_data_cutoff</td>
<td>last_data_cutoff</td>
<td>datetime</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'cutoff date time of the last extract from the publishing system',</td>
<td> COMMENT 'cutoff date time of the last extract from the publishing system',</td>
<td>  last_data_cutoff datetime DEFAULT NULL COMMENT 'cutoff date time of the last extract from the publishing system',</td>
<td>  last_data_cutoff datetime DEFAULT NULL COMMENT 'cutoff date time of the last extract from the publishing system',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>publishing_software</td>
<td>bigint</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>
<td> COMMENT 'software name of publishing system, i.e. SystmOne',</td>
<td>  id bigint NOT NULL COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>
<td>  publishing_software varchar(50) NOT NULL COMMENT 'software name of publishing system, i.e. SystmOne',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>date_of_birth</td>
<td>ethnic_code_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of birth of the patient',</td>
<td> COMMENT 'Reference to the ethnicity of the patient',</td>
<td>  date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the patient',</td>
<td>  ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>nhs_number</td>
<td>nhs_number</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The NHS number of the patient',</td>
<td> COMMENT 'The NHS number of the patient',</td>
<td>  nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the patient',</td>
<td>  nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>date_of_birth</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the ethnicity of the patient',</td>
<td> COMMENT 'The date of birth of the patient',</td>
<td>  ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient',</td>
<td>  date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>gender_concept_id</td>
<td>gender_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the gender of the patient',</td>
<td> COMMENT 'Reference to the gender of the patient',</td>
<td>  gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the patient',</td>
<td>  gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>current_address_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the current address of the patient',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>first_names</td>
<td>id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The first names of the patient',</td>
<td> COMMENT 'Unique Id of the patient',</td>
<td>  first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_of_death</td>
<td>registered_practice_organization_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of death of the patient',</td>
<td> COMMENT 'Reference to the organisation the patient is registered at',</td>
<td>  date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient',</td>
<td>  registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at',</td>
</tr>
</tr>
<tr>
<tr>
<td>registered_practice_organization_id</td>
<td>current_address_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the organisation the patient is registered at',</td>
<td> COMMENT 'Reference to the current address of the patient',</td>
<td>  registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at',</td>
<td>  current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>title</td>
<td>last_name</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The title of the patient',</td>
<td> COMMENT 'The last name of the patient',</td>
<td>  title varchar(255) DEFAULT NULL COMMENT 'The title of the patient',</td>
<td>  last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>first_names</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The first names of the patient',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>date_of_death</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'The date of death of the patient',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient',</td>
</tr>
</tr>
<tr>
<tr>
<td>last_name</td>
<td>title</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The last name of the patient',</td>
<td> COMMENT 'The title of the patient',</td>
<td>  last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient',</td>
<td>  title varchar(255) DEFAULT NULL COMMENT 'The title of the patient',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_additional</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_additional</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>text_value</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'same as the id column on the patient table ',</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>json_value</td>
<td>value_id</td>
<td>json</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td> COMMENT 'IM reference (e.g. COVID)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)',</td>
</tr>
</tr>
<tr>
<tr>
<td>text_value</td>
<td>id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)',</td>
<td> COMMENT 'same as the id column on the patient table ',</td>
<td>  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)',</td>
<td>  id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>
</tr>
</tr>
<tr>
<tr>
<td>value_id</td>
<td>property_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (e.g. COVID)',</td>
<td> COMMENT 'IM reference (e.g. Cause of death)',</td>
<td>  value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (e.g. Cause of death)',</td>
</tr>
</tr>
<tr>
<tr>
<td>property_id</td>
<td>json_value</td>
<td>int</td>
<td>json</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (e.g. Cause of death)',</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>
<td>  property_id int NOT NULL COMMENT 'IM reference (e.g. Cause of death)',</td>
<td>  json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_address</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>address_line_3</td>
<td>start_date</td>
<td>varchar(255)</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The third line of the address',</td>
<td> COMMENT 'The start date of this address being relevant',</td>
<td>  address_line_3 varchar(255) DEFAULT NULL COMMENT 'The third line of the address',</td>
<td>  start_date date NOT NULL COMMENT 'The start date of this address being relevant',</td>
</tr>
</tr>
<tr>
<tr>
<td>lsoa_2011_code</td>
<td>patient_id</td>
<td>varchar(9)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'A reference to the LSOA_2011 code',</td>
<td> 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',</td>
<td>  lsoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2011 code',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>postcode</td>
<td>lsoa_2001_code</td>
<td>varchar(255)</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The postcode',</td>
<td> COMMENT 'A reference to the LSOA_2001 code',</td>
<td>  postcode varchar(255) DEFAULT NULL COMMENT 'The postcode',</td>
<td>  lsoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2001 code',</td>
</tr>
</tr>
<tr>
<tr>
<td>use_concept_id</td>
<td>organization_id</td>
<td>int</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'use of address (e.g. home, temporary)',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  use_concept_id int NOT NULL COMMENT 'use of address (e.g. home, temporary)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>msoa_2001_code</td>
<td>address_line_4</td>
<td>varchar(9)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the MSOA_2001 code',</td>
<td> COMMENT 'The fourth line of the address',</td>
<td>  msoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2001 code',</td>
<td>  address_line_4 varchar(255) DEFAULT NULL COMMENT 'The fourth line of the address',</td>
</tr>
</tr>
<tr>
<tr>
<td>address_line_4</td>
<td>postcode</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The fourth line of the address',</td>
<td> COMMENT 'The postcode',</td>
<td>  address_line_4 varchar(255) DEFAULT NULL COMMENT 'The fourth line of the address',</td>
<td>  postcode varchar(255) DEFAULT NULL COMMENT 'The postcode',</td>
</tr>
</tr>
<tr>
<tr>
<td>start_date</td>
<td>Id</td>
<td>date</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The start date of this address being relevant',</td>
<td> COMMENT 'Unique Id of the address',</td>
<td>  start_date date NOT NULL COMMENT 'The start date of this address being relevant',</td>
<td>  Id bigint NOT NULL COMMENT 'Unique Id of the address',</td>
</tr>
</tr>
<tr>
<tr>
<td>lsoa_2001_code</td>
<td>lsoa_2011_code</td>
<td>varchar(9)</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the LSOA_2001 code',</td>
<td> COMMENT 'A reference to the LSOA_2011 code',</td>
<td>  lsoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2001 code',</td>
<td>  lsoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2011 code',</td>
</tr>
</tr>
<tr>
<tr>
<td>address_line_1</td>
<td>ward_code</td>
<td>varchar(255)</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The first line of the address',</td>
<td> COMMENT 'The ward the address belongs to',</td>
<td>  address_line_1 varchar(255) DEFAULT NULL COMMENT 'The first line of the address',</td>
<td>  ward_code varchar(9) DEFAULT NULL COMMENT 'The ward the address belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>Id</td>
<td>use_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the address',</td>
<td> COMMENT 'use of address (e.g. home, temporary)',</td>
<td>  Id bigint NOT NULL COMMENT 'Unique Id of the address',</td>
<td>  use_concept_id int NOT NULL COMMENT 'use of address (e.g. home, temporary)',</td>
</tr>
</tr>
<tr>
<tr>
<td>city</td>
<td>msoa_2011_code</td>
<td>varchar(255)</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The city',</td>
<td> COMMENT 'A reference to the MSOA_2011 code',</td>
<td>  city varchar(255) DEFAULT NULL COMMENT 'The city',</td>
<td>  msoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2011 code',</td>
</tr>
</tr>
<tr>
<tr>
<td>end_date</td>
<td>address_line_2</td>
<td>date</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of this address being relevant',</td>
<td> COMMENT 'The second line of the address',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of this address being relevant',</td>
<td>  address_line_2 varchar(255) DEFAULT NULL COMMENT 'The second line of the address',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>msoa_2001_code</td>
<td>bigint</td>
<td>varchar(9)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'A reference to the MSOA_2001 code',</td>
<td>  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',</td>
<td>  msoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2001 code',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>address_line_1</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The first line of the address',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  address_line_1 varchar(255) DEFAULT NULL COMMENT 'The first line of the address',</td>
</tr>
</tr>
<tr>
<tr>
<td>ward_code</td>
<td>person_id</td>
<td>varchar(9)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The ward the address belongs to',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  ward_code varchar(9) DEFAULT NULL COMMENT 'The ward the address belongs to',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>city</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'The city',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  city varchar(255) DEFAULT NULL COMMENT 'The city',</td>
</tr>
</tr>
<tr>
<tr>
<td>local_authority_code</td>
<td>end_date</td>
<td>varchar(9)</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The local authority the address belongs to',</td>
<td> COMMENT 'The end date of this address being relevant',</td>
<td>  local_authority_code varchar(9) DEFAULT NULL COMMENT 'The local authority the address belongs to',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of this address being relevant',</td>
</tr>
</tr>
<tr>
<tr>
<td>address_line_2</td>
<td>local_authority_code</td>
<td>varchar(255)</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The second line of the address',</td>
<td> COMMENT 'The local authority the address belongs to',</td>
<td>  address_line_2 varchar(255) DEFAULT NULL COMMENT 'The second line of the address',</td>
<td>  local_authority_code varchar(9) DEFAULT NULL COMMENT 'The local authority the address belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>msoa_2011_code</td>
<td>address_line_3</td>
<td>varchar(9)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the MSOA_2011 code',</td>
<td> COMMENT 'The third line of the address',</td>
<td>  msoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2011 code',</td>
<td>  address_line_3 varchar(255) DEFAULT NULL COMMENT 'The third line of the address',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address_match</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_address_match</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address_ralf</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_address_ralf</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_contact</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_contact</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient contact',</td>
<td> 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',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient contact',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>type_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'type of contact (e.g. phone, email)',</td>
<td>  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',</td>
<td>  type_concept_id int DEFAULT NULL COMMENT 'type of contact (e.g. phone, email)',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>start_date</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The start date of the contact being valid',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the contact being valid',</td>
</tr>
</tr>
<tr>
<tr>
<td>end_date</td>
<td>organization_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The end date of the contact being valid',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the contact being valid',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>use_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'use of contact (e.g. mobile, home,work',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  use_concept_id int DEFAULT NULL COMMENT 'use of contact (e.g. mobile, home,work',</td>
</tr>
</tr>
<tr>
<tr>
<td>use_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'use of contact (e.g. mobile, home,work',</td>
<td> COMMENT 'Unique Id of the patient contact',</td>
<td>  use_concept_id int DEFAULT NULL COMMENT 'use of contact (e.g. mobile, home,work',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient contact',</td>
</tr>
</tr>
<tr>
<tr>
<td>start_date</td>
<td>person_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The start date of the contact being valid',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the contact being valid',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>value</td>
<td>value</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the contact information eg phone number, email address',</td>
<td> COMMENT 'The value of the contact information eg phone number, email address',</td>
<td>  value varchar(255) DEFAULT NULL COMMENT 'The value of the contact information eg phone number, email address',</td>
<td>  value varchar(255) DEFAULT NULL COMMENT 'The value of the contact information eg phone number, email address',</td>
</tr>
</tr>
<tr>
<tr>
<td>type_concept_id</td>
<td>end_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'type of contact (e.g. phone, email)',</td>
<td> COMMENT 'The end date of the contact being valid',</td>
<td>  type_concept_id int DEFAULT NULL COMMENT 'type of contact (e.g. phone, email)',</td>
<td>  end_date date DEFAULT NULL COMMENT 'The end date of the contact being valid',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_pseudo_id</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_pseudo_id</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient pseudo id',</td>
<td> COMMENT 'Unique Id of the patient pseudo id',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient pseudo id',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the patient pseudo id',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_nhs_number_valid</td>
<td>Skid</td>
<td>tinyint(1)</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the nhs number is valid',</td>
<td> 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)',</td>
<td>  is_nhs_number_valid tinyint(1) NOT NULL COMMENT 'Whether the nhs number is valid',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>salt_name</td>
<td>bigint</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The name of the salt used to create the pseudo id',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  salt_name varchar(50) NOT NULL COMMENT 'The name of the salt used to create the pseudo id',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_nhs_number_verified_by_publisher</td>
<td>person_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the nhs number has been verified by the publisher',</td>
<td> COMMENT 'Reference to the person this registration status history belongs to',</td>
<td>  is_nhs_number_verified_by_publisher tinyint(1) NOT NULL COMMENT 'Whether the nhs number has been verified by the publisher',</td>
<td>  person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>is_nhs_number_valid</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the person this registration status history belongs to',</td>
<td> COMMENT 'Whether the nhs number is valid',</td>
<td>  person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>
<td>  is_nhs_number_valid tinyint(1) NOT NULL COMMENT 'Whether the nhs number is valid',</td>
</tr>
</tr>
<tr>
<tr>
<td>Skid</td>
<td>patient_id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> 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)',</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td>  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)',</td>
<td>  patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>salt_name</td>
<td>is_nhs_number_verified_by_publisher</td>
<td>varchar(50)</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The name of the salt used to create the pseudo id',</td>
<td> COMMENT 'Whether the nhs number has been verified by the publisher',</td>
<td>  salt_name varchar(50) NOT NULL COMMENT 'The name of the salt used to create the pseudo id',</td>
<td>  is_nhs_number_verified_by_publisher tinyint(1) NOT NULL COMMENT 'Whether the nhs number has been verified by the publisher',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_uprn</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Patient_uprn</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>patient_id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> 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',</td>
<td> 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',</td>
<td>  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',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Person</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Person</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>current_address_id</td>
<td>gender_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the current address of the person',</td>
<td> COMMENT 'Reference to the gender of the person',</td>
<td>  current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',</td>
<td>  gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>first_names</td>
<td>first_names</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The first names of the person',</td>
<td> COMMENT 'The first names of the person',</td>
<td>  first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the person',</td>
<td>  first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>gender_concept_id</td>
<td>date_of_death</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the gender of the person',</td>
<td> COMMENT 'The date of death of the person',</td>
<td>  gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person',</td>
<td>  date_of_death date DEFAULT NULL COMMENT 'The date of death of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>nhs_number</td>
<td>organization_id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The NHS number of the person',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>ethnic_code_concept_id</td>
<td>int</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the ethnicity of the person',</td>
<td> COMMENT 'Reference to the ethnicity of the person',</td>
<td>  ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the person',</td>
<td>  ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>registered_practice_organization_id</td>
<td>registered_practice_organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the organisation the person is registered at',</td>
<td> COMMENT 'Reference to the organisation the person is registered at',</td>
<td>  registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the person is registered at',</td>
<td>  registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the person is registered at',</td>
</tr>
</tr>
<tr>
<tr>
<td>title</td>
<td>last_name</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The title of the person',</td>
<td> COMMENT 'The last name of the person',</td>
<td>  title varchar(255) DEFAULT NULL COMMENT 'The title of the person',</td>
<td>  last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_of_death</td>
<td>title</td>
<td>date</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of death of the person',</td>
<td> COMMENT 'The title of the person',</td>
<td>  date_of_death date DEFAULT NULL COMMENT 'The date of death of the person',</td>
<td>  title varchar(255) DEFAULT NULL COMMENT 'The title of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>date_of_birth</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The date of birth of the person',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_of_birth</td>
<td>current_address_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date of birth of the person',</td>
<td> COMMENT 'Reference to the current address of the person',</td>
<td>  date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person',</td>
<td>  current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>last_name</td>
<td>id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The last name of the person',</td>
<td> COMMENT 'Unique Id of the person',</td>
<td>  last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the person',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>nhs_number</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the person',</td>
<td> COMMENT 'The NHS number of the person',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the person',</td>
<td>  nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Practitioner</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Practitioner</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>name</td>
<td>gmc_code</td>
<td>varchar(1024)</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Name of the practitioner',</td>
<td> COMMENT 'The GMC code of the practitioner',</td>
<td>  name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner',</td>
<td>  gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>role_code</td>
<td>bigint</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the practitioner',</td>
<td> COMMENT 'The code representing the role of the practitioner',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the practitioner',</td>
<td>  role_code varchar(50) DEFAULT NULL COMMENT 'The code representing the role of the practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>role_desc</td>
<td>bigint</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  role_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>role_code</td>
<td>organization_id</td>
<td>varchar(50)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The code representing the role of the practitioner',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  role_code varchar(50) DEFAULT NULL COMMENT 'The code representing the role of the practitioner',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>gmc_code</td>
<td>id</td>
<td>varchar(50)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The GMC code of the practitioner',</td>
<td> COMMENT 'Unique Id of the practitioner',</td>
<td>  gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the practitioner',</td>
</tr>
</tr>
<tr>
<tr>
<td>role_desc</td>
<td>name</td>
<td>varchar(255)</td>
<td>varchar(1024)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>
<td> COMMENT 'Name of the practitioner',</td>
<td>  role_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>
<td>  name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Procedure_request</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Procedure_request</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>date_recorded</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the procedure',</td>
<td> COMMENT 'The date the procedure was recorded in the source system',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the procedure',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the procedure was recorded in the source system',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>patient_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the encounter the procedure was administered at',</td>
<td> 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',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the procedure was administered at',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>encounter_id</td>
<td>decimal(5,2)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age of the patient at the time of the procedure',</td>
<td> COMMENT 'Reference to the encounter the procedure was administered at',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the procedure',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the procedure was administered at',</td>
</tr>
</tr>
<tr>
<tr>
<td>core_concept_id</td>
<td>id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the procedure',</td>
<td> COMMENT 'Unique Id of the procedure',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the procedure',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>age_at_event</td>
<td>int</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'The age of the patient at the time of the procedure',</td>
<td>  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)',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the procedure',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>non_core_concept_id</td>
<td>date</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the procedure was administered by a clinician',</td>
<td> COMMENT 'Reference to the clinical coding of the procedure',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the procedure was administered by a clinician',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>status_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'Reference to the status of the procedure',</td>
<td>  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',</td>
<td>  status_concept_id int DEFAULT NULL COMMENT 'Reference to the status of the procedure',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>date_precision_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>core_concept_id</td>
<td>datetime</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the procedure was recorded in the source system',</td>
<td> COMMENT 'Reference to the clinical coding of the procedure',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the procedure was recorded in the source system',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>person_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>status_concept_id</td>
<td>clinical_effective_date</td>
<td>int</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the status of the procedure',</td>
<td> COMMENT 'The date the procedure was administered by a clinician',</td>
<td>  status_concept_id int DEFAULT NULL COMMENT 'Reference to the status of the procedure',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the procedure was administered by a clinician',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>practitioner_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the procedure',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Referral_request</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Referral_request</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>core_concept_id</td>
<td>recipient_organization_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the referral',</td>
<td> COMMENT 'Reference to the organization receiving the referral',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>
<td>  recipient_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organization receiving the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>non_core_concept_id</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the referral',</td>
<td> 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',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>
<td>  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',</td>
</tr>
</tr>
<tr>
<tr>
<td>requester_organization_id</td>
<td>referral_request_priority_concept_id</td>
<td>bigint</td>
<td>smallint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the organisation that made the refereral request',</td>
<td> COMMENT 'Reference to the priority of the referral',</td>
<td>  requester_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation that made the refereral request',</td>
<td>  referral_request_priority_concept_id smallint DEFAULT NULL COMMENT 'Reference to the priority of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>encounter_id</td>
<td>age_at_event</td>
<td>bigint</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the encounter the referral was made in',</td>
<td> COMMENT 'The age of the patient at the time of the referral',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the referral was made in',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>referral_request_type_concept_id</td>
<td>Mode</td>
<td>int</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of referral request',</td>
<td> COMMENT 'The mode of the referral',</td>
<td>  referral_request_type_concept_id int DEFAULT NULL COMMENT 'Reference to the type of referral request',</td>
<td>  Mode varchar(50) DEFAULT NULL COMMENT 'The mode of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>outgoing_referral</td>
<td>core_concept_id</td>
<td>tinyint(1)</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether this is an outgoing referral',</td>
<td> COMMENT 'Reference to the clinical coding of the referral',</td>
<td>  outgoing_referral tinyint(1) DEFAULT NULL COMMENT 'Whether this is an outgoing referral',</td>
<td>  core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>encounter_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the referral',</td>
<td> COMMENT 'Reference to the encounter the referral was made in',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the referral',</td>
<td>  encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the referral was made in',</td>
</tr>
</tr>
<tr>
<tr>
<td>referral_request_priority_concept_id</td>
<td>organization_id</td>
<td>smallint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the priority of the referral',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  referral_request_priority_concept_id smallint DEFAULT NULL COMMENT 'Reference to the priority of the referral',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>is_review</td>
<td>requester_organization_id</td>
<td>tinyint(1)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether this referral is a review',</td>
<td> COMMENT 'Reference to the organisation that made the refereral request',</td>
<td>  is_review tinyint(1) DEFAULT NULL COMMENT 'Whether this referral is a review',</td>
<td>  requester_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation that made the refereral request',</td>
</tr>
</tr>
<tr>
<tr>
<td>Mode</td>
<td>id</td>
<td>varchar(50)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The mode of the referral',</td>
<td> COMMENT 'Unique Id of the referral',</td>
<td>  Mode varchar(50) DEFAULT NULL COMMENT 'The mode of the referral',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>age_at_event</td>
<td>is_review</td>
<td>decimal(5,2)</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The age of the patient at the time of the referral',</td>
<td> COMMENT 'Whether this referral is a review',</td>
<td>  age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the referral',</td>
<td>  is_review tinyint(1) DEFAULT NULL COMMENT 'Whether this referral is a review',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>clinical_effective_date</td>
<td>bigint</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td> COMMENT 'The date the referral was made',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the referral was made',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_recorded</td>
<td>date_precision_concept_id</td>
<td>datetime</td>
<td>smallint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the referral request was added to the source system',</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the referral request was added to the source system',</td>
<td>  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)',</td>
</tr>
</tr>
<tr>
<tr>
<td>date_precision_concept_id</td>
<td>referral_request_type_concept_id</td>
<td>smallint</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)',</td>
<td> COMMENT 'Reference to the type of referral request',</td>
<td> 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)',</td>
<td>  referral_request_type_concept_id int DEFAULT NULL COMMENT 'Reference to the type of referral request',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>date_recorded</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> 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',</td>
<td> COMMENT 'The date the referral request was added to the source system',</td>
<td>  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',</td>
<td>  date_recorded datetime DEFAULT NULL COMMENT 'The date the referral request was added to the source system',</td>
</tr>
</tr>
<tr>
<tr>
<td>clinical_effective_date</td>
<td>person_id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The date the referral was made',</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td>  clinical_effective_date date DEFAULT NULL COMMENT 'The date the referral was made',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>outgoing_referral</td>
<td>bigint</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'Whether this is an outgoing referral',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  outgoing_referral tinyint(1) DEFAULT NULL COMMENT 'Whether this is an outgoing referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>non_core_concept_id</td>
<td>bigint</td>
<td>int</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique individual across all organisations',</td>
<td> COMMENT 'Reference to the clinical coding of the referral',</td>
<td>  person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>
<td>  non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>
</tr>
</tr>
<tr>
<tr>
<td>recipient_organization_id</td>
<td>practitioner_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the organization receiving the referral',</td>
<td> COMMENT 'The clinician the activity is recorded against',</td>
<td>  recipient_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organization receiving the referral',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Registration_status_history</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Registration_status_history</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>id</td>
<td>end_date</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the registration status history',</td>
<td> COMMENT 'The end date for the period this registration status history was valid',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the registration status history',</td>
<td>  end_date datetime DEFAULT NULL COMMENT 'The end date for the period this registration status history was valid',</td>
</tr>
</tr>
<tr>
<tr>
<td>end_date</td>
<td>registration_status_concept_id</td>
<td>datetime</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date for the period this registration status history was valid',</td>
<td> COMMENT 'Reference to the registration status',</td>
<td>  end_date datetime DEFAULT NULL COMMENT 'The end date for the period this registration status history was valid',</td>
<td>  registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>start_date</td>
<td>bigint</td>
<td>datetime</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The start date for the period this registration status history was valid',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  start_date datetime DEFAULT NULL COMMENT 'The start date for the period this registration status history was valid',</td>
</tr>
</tr>
<tr>
<tr>
<td>patient_id</td>
<td>organization_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>person_id</td>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the person this registration status history belongs to',</td>
<td> COMMENT 'Reference to the episode of care this status history belongs to',</td>
<td>  person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this status history belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>start_date</td>
<td>person_id</td>
<td>datetime</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The start date for the period this registration status history was valid',</td>
<td> COMMENT 'Reference to the person this registration status history belongs to',</td>
<td>  start_date datetime DEFAULT NULL COMMENT 'The start date for the period this registration status history was valid',</td>
<td>  person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>
</tr>
</tr>
<tr>
<tr>
<td>episode_of_care_id</td>
<td>id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the episode of care this status history belongs to',</td>
<td> COMMENT 'Unique Id of the registration status history',</td>
<td>  episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this status history belongs to',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the registration status history',</td>
</tr>
</tr>
<tr>
<tr>
<td>registration_status_concept_id</td>
<td>patient_id</td>
<td>int</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the registration status',</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>
<td>  registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status',</td>
<td>  patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>
</tr>
</tr>
</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Schedule</span></h2>
</tbody>
</table>
<h2><span class="mw-headline" id="Sortable_tables">Schedule</span></h2>
<table class="sortable" border="1" style="border-collapse:collapse">
<table class="sortable" border="1" style="border-collapse:collapse">
<tr>
<tr>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Column Name</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Type</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Constraint</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Comment</th>
<th scope="col" width="10%">Sql</th>
<th scope="col" width="10%">Sql</th>
</tr>
</tr>
<tbody>
<tbody>
<tr>
<tr>
<td>start_date</td>
<td>id</td>
<td>date</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The start date of the schedule',</td>
<td> COMMENT 'Unique Id of the schedule',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the schedule',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the schedule',</td>
</tr>
</tr>
<tr>
<tr>
<td>name</td>
<td>type</td>
<td>varchar(150)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The name of the schedule',</td>
<td> COMMENT 'The type of schedule eg Timed Appointments',</td>
<td>  name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule',</td>
<td>  type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments',</td>
</tr>
</tr>
<tr>
<tr>
<td>organization_id</td>
<td>name</td>
<td>bigint</td>
<td>varchar(150)</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td> COMMENT 'The name of the schedule',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule',</td>
</tr>
</tr>
<tr>
<tr>
<td>type</td>
<td>organization_id</td>
<td>varchar(255)</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>NOT NULL</td>
<td> COMMENT 'The type of schedule eg Timed Appointments',</td>
<td> COMMENT 'Owning organisation (i.e. publisher)',</td>
<td>  type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments',</td>
<td>  organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>
</tr>
</tr>
<tr>
<tr>
<td>location</td>
<td>location</td>
<td>varchar(255)</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the location the schedule was held at',</td>
<td> COMMENT 'Textual description of the location the schedule was held at',</td>
<td>  location varchar(255) DEFAULT NULL COMMENT 'Textual description of the location the schedule was held at',</td>
<td>  location varchar(255) DEFAULT NULL COMMENT 'Textual description of the location the schedule was held at',</td>
</tr>
</tr>
<tr>
<tr>
<td>practitioner_id</td>
<td>practitioner_id</td>
<td>bigint</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the practitioner who owns the schedule',</td>
<td> COMMENT 'Reference to the practitioner who owns the schedule',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the practitioner who owns the schedule',</td>
<td>  practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the practitioner who owns the schedule',</td>
</tr>
</tr>
<tr>
<tr>
<td>id</td>
<td>start_date</td>
<td>bigint</td>
<td>date</td>
<td>NOT NULL</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Unique Id of the schedule',</td>
<td> COMMENT 'The start date of the schedule',</td>
<td>  id bigint NOT NULL COMMENT 'Unique Id of the schedule',</td>
<td>  start_date date DEFAULT NULL COMMENT 'The start date of the schedule',</td>
</tr>
</tr>
</tbody></table>
</tbody>
</table>

Revision as of 13:58, 22 November 2022

<html class="client-nojs" lang="en-GB" dir="ltr"> <head> <meta charset="UTF-8"/> <title>Discovery Data Service Compass v2.1.1 Database Dump</title>

<a id="top"></a>

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

From Discovery Data Service

Allergy_intolerance

<tbody> </tbody>
Column Name Type Constraint Comment Sql
is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter', is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter',
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', 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',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for', clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
non_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',
id bigint NOT NULL COMMENT 'Unique Id of the allergy', id bigint NOT NULL COMMENT 'Unique Id of the allergy',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event', age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was at the time of this event',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy', core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',
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)', 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)',
date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded', date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', 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', 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', practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',

Appointment

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

Appointment_additional

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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)', 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)',
property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)', property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)',
value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)', value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)',
id bigint NOT NULL COMMENT 'same as the id column on the patient table ', id bigint NOT NULL COMMENT 'same as the id column on the patient table ',
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON', json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',

Concept

<tbody> </tbody>
Column Name Type Constraint Comment Sql
Name varchar(255) DEFAULT NULL COMMENT 'Short name', Name varchar(255) DEFAULT NULL COMMENT 'Short name',
Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)', Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)',
Id varchar(150) NOT NULL COMMENT 'Unique concept identifier', Id varchar(150) NOT NULL COMMENT 'Unique concept identifier',
dbid int NOT NULL COMMENT 'Unique Id of the concept', dbid int NOT NULL COMMENT 'Unique Id of the concept',
Scheme bigint DEFAULT NULL COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)', 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)', Code varchar(40) DEFAULT NULL COMMENT 'The code (non-unique unless coupled with a scheme)',
Document int NOT NULL COMMENT 'Concept grouping construct, deprecated', Document int NOT NULL COMMENT 'Concept grouping construct, deprecated',
updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept', updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept',
Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept', Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',

Concept_map

<tbody> </tbody>
Column Name Type Constraint Comment Sql
updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added', updated datetime NOT NULL COMMENT 'Timestamp the map was last updated/added',
legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept', 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', core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',

Diagnostic_order

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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', 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',
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', 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',
result_value double DEFAULT NULL COMMENT 'The value of the result of the observation', result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',
problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem', problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',
result_date date DEFAULT NULL COMMENT 'The date of the result', result_date date DEFAULT NULL COMMENT 'The date of the result',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician', clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation', core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem', is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation', age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',
is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the diagnostic order is a primary order', is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the diagnostic order is a primary order',
result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation', result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',
non_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',
episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare', episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',
result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result', result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',
is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem', is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',
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)', 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)',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order', id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order',
encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at', 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', practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',

Encounter

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

Encounter_additional

<tbody> </tbody>
Column Name Type Constraint Comment Sql
id bigint NOT NULL COMMENT 'same as the id column on the encounter table', 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)', property_id int NOT NULL COMMENT 'IM concept id reference (i.e. Admission method)',
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)', json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',
value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)', value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)',
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)', 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)',

Encounter_event

<tbody> </tbody>
Column Name Type Constraint Comment Sql
date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter', date_precision_concept_id int DEFAULT NULL COMMENT 'Reference to the precision of the date of the encounter',
end_date date DEFAULT NULL COMMENT 'The end date of the encounter', end_date date DEFAULT NULL COMMENT 'The end date of the encounter',
date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded', date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
person_id bigint NOT NULL COMMENT 'The person this event belongs to', person_id bigint NOT NULL COMMENT 'The person this event belongs to',
admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter', admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',
appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on', appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the encounter', age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the encounter',
finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished', finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished',
id bigint NOT NULL COMMENT 'Unique Id of the encounter event', id bigint NOT NULL COMMENT 'Unique Id of the encounter event',
clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place', clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place',
non_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',
episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this encounter belongs to', 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', 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', core_concept_id int DEFAULT NULL COMMENT 'Reference to the type of encounter',
institution_location_id bigint DEFAULT NULL COMMENT 'Reference to the institution the encounter took place at', institution_location_id bigint DEFAULT NULL COMMENT 'Reference to the institution the encounter took place at',
practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against', practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',
patient_id bigint NOT NULL COMMENT 'The patient this event belongs to', patient_id bigint NOT NULL COMMENT 'The patient this event belongs to',
encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record', encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record',

Episode_of_care

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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', 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',
id bigint NOT NULL COMMENT 'Unique Id of the episode of care', id bigint NOT NULL COMMENT 'Unique Id of the episode of care',
registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient', registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient', registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient',
date_registered date DEFAULT NULL COMMENT 'The date the registration was started  for this episode of care', date_registered date DEFAULT NULL COMMENT 'The date the registration was started  for this episode of care',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
date_registered_end date DEFAULT NULL COMMENT 'The date the registration was ended  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', usual_gp_practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the usual GP for this episode of care',

Event_log

<tbody> </tbody>
Column Name Type Constraint Comment Sql
dt_change datetime(3) NOT NULL COMMENT 'date time the change was made to this DB', 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', 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', table_id tinyint NOT NULL COMMENT 'identifier of the table changed',
record_id bigint NOT NULL COMMENT 'id of the record changed' record_id bigint NOT NULL COMMENT 'id of the record changed'

Flag

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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', 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',
effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record', effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record',
is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not', is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
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)', 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)',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
id bigint NOT NULL COMMENT 'Unique Id of the flag', id bigint NOT NULL COMMENT 'Unique Id of the flag',

Location

<tbody> </tbody>
Column Name Type Constraint Comment Sql
name varchar(255) DEFAULT NULL COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary', name varchar(255) DEFAULT NULL COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',
type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of location eg GP Practice', 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', 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', managing_organization_id bigint DEFAULT NULL COMMENT 'Reference to the managing organisation of the location',
id bigint NOT NULL COMMENT 'Unique Id of the location', id bigint NOT NULL COMMENT 'Unique Id of the location',
type_code varchar(50) DEFAULT NULL COMMENT 'The type of location', type_code varchar(50) DEFAULT NULL COMMENT 'The type of location',

Medication_order

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

Medication_statement

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

Observation

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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', 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',
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', 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',
result_value double DEFAULT NULL COMMENT 'The value of the result of the observation', result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',
problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem', problem_end_date date DEFAULT NULL COMMENT 'The end date of the problem',
result_date date DEFAULT NULL COMMENT 'The date of the result', result_date date DEFAULT NULL COMMENT 'The date of the result',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation', core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the observation',
id bigint NOT NULL COMMENT 'Unique Id of the observation', id bigint NOT NULL COMMENT 'Unique Id of the observation',
is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem', is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',
date_recorded datetime DEFAULT NULL COMMENT 'The date the observation was recorded in the system', date_recorded datetime DEFAULT NULL COMMENT 'The date the observation was recorded in the system',
age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation', age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age of the patient at the time of the observation',
clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician', clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician',
is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation', is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation',
result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation', result_value_units varchar(50) DEFAULT NULL COMMENT 'The units of the result of the observation',
non_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',
episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare', episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',
result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result', result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',
is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem', is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',
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)', 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)',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', 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', 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', practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',

Observation_additional

<tbody> </tbody>
Column Name Type Constraint Comment Sql
json_value json DEFAULT NULL COMMENT 'the JSON data itself ', json_value json DEFAULT NULL COMMENT 'the JSON data itself ',
value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)', value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)',
text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value', text_value varchar(255) DEFAULT NULL COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',
id bigint NOT NULL COMMENT 'same as the id column on the observation table', 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)', property_id int NOT NULL COMMENT 'IM reference (i.e. significance)',

Organization

<tbody> </tbody>
Column Name Type Constraint Comment Sql
type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation', type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation',
parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation', parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation',
Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation', Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation',
ods_code varchar(50) DEFAULT NULL COMMENT 'ODS Code 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', Name varchar(255) DEFAULT NULL COMMENT 'Name of the organisation',
type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice', type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice',
id bigint NOT NULL COMMENT 'Unique Id of the organisation', id bigint NOT NULL COMMENT 'Unique Id of the organisation',

Organization_metadata

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

Patient

<tbody> </tbody>
Column Name Type Constraint Comment Sql
ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient', ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient',
nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number 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_birth date DEFAULT NULL COMMENT 'The date of birth of the patient',
gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the patient', gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the patient',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
id bigint NOT NULL COMMENT 'Unique Id of the patient', id bigint NOT NULL COMMENT 'Unique Id of the patient',
registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at', registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at',
current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient', current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient',
last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient', last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',
first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient', first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient',
date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient', date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient',
title varchar(255) DEFAULT NULL COMMENT 'The title of the patient', title varchar(255) DEFAULT NULL COMMENT 'The title of the patient',

Patient_additional

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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)', 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)',
value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)', value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)',
id bigint NOT NULL COMMENT 'same as the id column on the patient table ', 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)', property_id int NOT NULL COMMENT 'IM reference (e.g. Cause of death)',
json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON', json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',

Patient_address

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

Patient_address_match

<tbody> </tbody>
Column Name Type Constraint Comment Sql

Patient_address_ralf

<tbody> </tbody>
Column Name Type Constraint Comment Sql

Patient_contact

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

Patient_pseudo_id

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

Patient_uprn

<tbody> </tbody>
Column Name Type Constraint Comment Sql
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', 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)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
person_id bigint NOT NULL COMMENT 'Unique individual across all organisations', person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',

Person

<tbody> </tbody>
Column Name Type Constraint Comment Sql
gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person', gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person',
first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the person', first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the person',
date_of_death date DEFAULT NULL COMMENT 'The date of death of the person', date_of_death date DEFAULT NULL COMMENT 'The date of death of the person',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity 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', registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the person is registered at',
last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person', last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person',
title varchar(255) DEFAULT NULL COMMENT 'The title of the person', title varchar(255) DEFAULT NULL COMMENT 'The title of the person',
date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person', date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person',
current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person', current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',
id bigint NOT NULL COMMENT 'Unique Id of the person', id bigint NOT NULL COMMENT 'Unique Id of the person',
nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person', nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person',

Practitioner

<tbody> </tbody>
Column Name Type Constraint Comment Sql
gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner', gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner',
role_code varchar(50) DEFAULT NULL COMMENT 'The code representing the role 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', role_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
id bigint NOT NULL COMMENT 'Unique Id of the practitioner', id bigint NOT NULL COMMENT 'Unique Id of the practitioner',
name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner', name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner',

Procedure_request

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

Referral_request

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

Registration_status_history

<tbody> </tbody>
Column Name Type Constraint Comment Sql
end_date datetime DEFAULT NULL COMMENT 'The end 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',
registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status', 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', start_date datetime DEFAULT NULL COMMENT 'The start date for the period this registration status history was valid',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this status history belongs to', episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this status history belongs to',
person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to', person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',
id bigint NOT NULL COMMENT 'Unique Id of the registration status history', id bigint NOT NULL COMMENT 'Unique Id of the registration status history',
patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to', patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',

Schedule

<tbody> </tbody>
Column Name Type Constraint Comment Sql
id bigint NOT NULL COMMENT 'Unique Id of the schedule', id bigint NOT NULL COMMENT 'Unique Id of the schedule',
type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments', type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments',
name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule', name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule',
organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)', organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',
location varchar(255) DEFAULT NULL COMMENT 'Textual description of the location the schedule was held at', location varchar(255) DEFAULT NULL COMMENT 'Textual description of the location the schedule was held at',
practitioner_id bigint DEFAULT NULL COMMENT 'Reference to the practitioner who owns the schedule', 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', start_date date DEFAULT NULL COMMENT 'The start date of the schedule',
Retrieved from ‘https://wiki.discoverydataservice.org/index.php?title=CompassV2TestPages:v2.X&oldid=6080’

Navigation menu