CompassV2TestPages:v2.X: Difference between revisions

Revision as of 13:21, 22 November 2022

<head>

<title>Discovery Data Service Compass v1.2.1 Database Dump</title>

"wgCSPNonce":!1,"wgIsProbablyEditable":!1,"wgRelevantPageIsProbablyEditable":!1,"wgRestrictionEdit":[],"wgRestrictionMove":[],"wgPopupsReferencePreviews":!0,"wgPopupsConflictsWithNavPopupGadget":!1,"wgVisualEditor":{"pageLanguageCode":"en-GB","pageLanguageDir":"ltr","pageVariantFallbacks":"en-gb"},"wgEditSubmitButtonLabelPublish":!1};RLSTATE={"site.styles":"ready","noscript":"ready","user.styles":"ready","user":"ready","user.options":"loading","user.tokens":"loading","mediawiki.legacy.shared":"ready","mediawiki.legacy.commonPrint":"ready","jquery.tablesorter.styles":"ready","jquery.makeCollapsible.styles":"ready","mediawiki.toc.styles":"ready","ext.visualEditor.desktopArticleTarget.noscript":"ready","mediawiki.skinning.interface":"ready","skins.vector.styles":"ready"};RLPAGEMODULES=["site","mediawiki.page.startup","mediawiki.page.ready","jquery.tablesorter","jquery.makeCollapsible","mediawiki.toc","mediawiki.searchSuggest","ext.SimpleTooltip","ext.popups",

"ext.visualEditor.desktopArticleTarget.init","ext.visualEditor.targetLoader","skins.vector.js"];</script>

});mw.loader.implement("user.tokens@tffin",function($,jQuery,require,module){/*@nomin*/mw.user.tokens.set({"editToken":"+\\","patrolToken":"+\\","watchToken":"+\\","csrfToken":"+\\"});

});});</script>

</head>

</div>

<h1 id="firstHeading" class="firstHeading" lang="en-GB">Remote Subscriber Database (RSD) Schema (Compass 2) Version: v1.2.1</h1>

<div id="siteSub" class="noprint">From Discovery Data Service</div>

<a class="mw-jump-link" href="#mw-head">Jump to navigation</a>

<a class="mw-jump-link" href="#p-search">Jump to search</a>

<div id="mw-content-text" lang="en-GB" dir="ltr" class="mw-content-ltr"><div class="mw-parser-output"><div id="toc" class="toc"><input type="checkbox" role="button" id="toctogglecheckbox" class="toctogglecheckbox" style="display:none" /><div class="toctitle" lang="en-GB" dir="ltr"><h2>Contents</h2><span class="toctogglespan"><label class="toctogglelabel" for="toctogglecheckbox"></label></span></div>

<ul>

<li class="toclevel-1 tocsection-1"><a href="#Table:_allergy_intolerance"><span class="tocnumber">1</span> <span class="toctext">Table: allergy_intolerance</span></a></li>

<li class="toclevel-1 tocsection-2"><a href="#Table:_appointment"><span class="tocnumber">2</span> <span class="toctext">Table: appointment</span></a></li>

<li class="toclevel-1 tocsection-3"><a href="#Table:_concept"><span class="tocnumber">3</span> <span class="toctext">Table: concept</span></a></li>

<li class="toclevel-1 tocsection-4"><a href="#Table:_concept_map"><span class="tocnumber">4</span> <span class="toctext">Table: concept_map</span></a></li>

<li class="toclevel-1 tocsection-5"><a href="#Table:_diagnostic_order"><span class="tocnumber">5</span> <span class="toctext">Table: diagnostic_order</span></a></li>

<li class="toclevel-1 tocsection-6"><a href="#Table:_encounter"><span class="tocnumber">6</span> <span class="toctext">Table: encounter</span></a></li>

<li class="toclevel-1 tocsection-7"><a href="#Table:_encounter_additional"><span class="tocnumber">7</span> <span class="toctext">Table: encounter_additional</span></a></li>

<li class="toclevel-1 tocsection-8"><a href="#Table:_encounter_event"><span class="tocnumber">8</span> <span class="toctext">Table: encounter_event</span></a></li>

<li class="toclevel-1 tocsection-9"><a href="#Table:_episode_of_care"><span class="tocnumber">9</span> <span class="toctext">Table: episode_of_care</span></a></li>

<li class="toclevel-1 tocsection-10"><a href="#Table:_event_log"><span class="tocnumber">10</span> <span class="toctext">Table: event_log</span></a></li>

<li class="toclevel-1 tocsection-11"><a href="#Table:_flag"><span class="tocnumber">11</span> <span class="toctext">Table: flag</span></a></li>

<li class="toclevel-1 tocsection-12"><a href="#Table:_location"><span class="tocnumber">12</span> <span class="toctext">Table: location</span></a></li>

<li class="toclevel-1 tocsection-13"><a href="#Table:_medication_order"><span class="tocnumber">13</span> <span class="toctext">Table: medication_order</span></a></li>

<li class="toclevel-1 tocsection-14"><a href="#Table:_medication_statement"><span class="tocnumber">14</span> <span class="toctext">Table: medication_statement</span></a></li>

<li class="toclevel-1 tocsection-15"><a href="#Table:_observation"><span class="tocnumber">15</span> <span class="toctext">Table: observation</span></a></li>

<li class="toclevel-1 tocsection-16"><a href="#Table:_observation_additional"><span class="tocnumber">16</span> <span class="toctext">Table: observation_additional</span></a></li>

<li class="toclevel-1 tocsection-17"><a href="#Table:_organization"><span class="tocnumber">17</span> <span class="toctext">Table: organization</span></a></li>

<li class="toclevel-1 tocsection-18"><a href="#Table:_organization_metadata"><span class="tocnumber">18</span> <span class="toctext">Table: organization_metadata</span></a></li>

<li class="toclevel-1 tocsection-19"><a href="#Table:_patient"><span class="tocnumber">19</span> <span class="toctext">Table: patient</span></a></li>

<li class="toclevel-1 tocsection-20"><a href="#Table:_patient_additional"><span class="tocnumber">20</span> <span class="toctext">Table: patient_additional</span></a></li>

<li class="toclevel-1 tocsection-21"><a href="#Table:_patient_address"><span class="tocnumber">21</span> <span class="toctext">Table: patient_address</span></a></li>

<li class="toclevel-1 tocsection-22"><a href="#Table:_patient_address_match"><span class="tocnumber">22</span> <span class="toctext">Table: patient_address_match</span></a></li>

<li class="toclevel-1 tocsection-23"><a href="#Table:_patient_address_ralf"><span class="tocnumber">23</span> <span class="toctext">Table: patient_address_ralf</span></a></li>

<li class="toclevel-1 tocsection-24"><a href="#Table:_patient_contact"><span class="tocnumber">24</span> <span class="toctext">Table: patient_contact</span></a></li>

<li class="toclevel-1 tocsection-25"><a href="#Table:_patient_pseudo_id"><span class="tocnumber">25</span> <span class="toctext">Table: patient_pseudo_id</span></a></li>

<li class="toclevel-1 tocsection-26"><a href="#Table:_patient_uprn"><span class="tocnumber">26</span> <span class="toctext">Table: patient_uprn</span></a></li>

<li class="toclevel-1 tocsection-27"><a href="#Table:_person"><span class="tocnumber">27</span> <span class="toctext">Table: person</span></a></li>

<li class="toclevel-1 tocsection-28"><a href="#Table:_practitioner"><span class="tocnumber">28</span> <span class="toctext">Table: practitioner</span></a></li>

<li class="toclevel-1 tocsection-29"><a href="#Table:_procedure_request"><span class="tocnumber">29</span> <span class="toctext">Table: procedure_request</span></a></li>

<li class="toclevel-1 tocsection-30"><a href="#Table:_pseudo_id"><span class="tocnumber">30</span> <span class="toctext">Table: pseudo_id</span></a></li>

<li class="toclevel-1 tocsection-31"><a href="#Table:_referral_request"><span class="tocnumber">31</span> <span class="toctext">Table: referral_request</span></a></li>

<li class="toclevel-1 tocsection-32"><a href="#Table:_registration_status_history"><span class="tocnumber">32</span> <span class="toctext">Table: registration_status_history</span></a></li>

<li class="toclevel-1 tocsection-33"><a href="#Table:_schedule"><span class="tocnumber">33</span> <span class="toctext">Table: schedule</span></a></li>

</ul>

</div>

<h2><span class="mw-headline" id="Sortable_tables">Allergy_intolerance</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the allergy',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the allergy',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the allergy',</td>

<td> non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter this allergy was record in',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this allergy was record in',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age the patient was at the time of this event',</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>

<td>date_precision_concept_id</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> 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>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the allergy',</td>

<td> core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the allergy',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td> COMMENT 'The date the allergy was recorded',</td>

<td> date_recorded datetime NOT NULL COMMENT 'The date the allergy was recorded',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the clinical code is recorded for',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>

</tr>

<tr>

<td>is_review</td>

<td>tinyint(1)</td>

<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>

<td> is_review tinyint(1) NOT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Appointment</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>patient_wait</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'How long the patient waited from being marked as arrived to being sent in',</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>

<td>schedule_id</td>

<td>bigint</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> 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>

<td>appointment_status_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA',</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>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Date and time the patient was sent into the practitioner',</td>

<td> date_time_sent_in datetime DEFAULT NULL COMMENT 'Date and time the patient was sent into the practitioner',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>datetime</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the appointment',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the appointment',</td>

</tr>

<tr>

<td>start_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The start date of the appointment',</td>

<td> start_date date DEFAULT NULL COMMENT 'The start date of the appointment',</td>

</tr>

<tr>

<td>patient_delay</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>source_id</td>

<td>varchar(36)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Unique reference to the source of the appointment',</td>

<td> source_id varchar(36) DEFAULT NULL COMMENT 'Unique reference to the source of the appointment',</td>

</tr>

<tr>

<td>planned_duration</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes',</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>

<td>actual_duration</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>cancelled_date</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the appointment was cancelled',</td>

<td> cancelled_date datetime DEFAULT NULL COMMENT 'The date the appointment was cancelled',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Appointment_additional</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'same as the id column on the patient table ',</td>

<td> id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>

</tr>

<tr>

<td>json_value</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>

<td> json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>

</tr>

<tr>

<td>text_value</td>

<td>varchar(255)</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> 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>

<td>property_id</td>

<td> COMMENT 'IM reference (e.g. appointment type)',</td>

<td> property_id int NOT NULL COMMENT 'IM reference (e.g. appointment type)',</td>

</tr>

<tr>

<td>value_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'IM reference (e.g. Appointment Type)',</td>

<td> value_id int DEFAULT NULL COMMENT 'IM reference (e.g. Appointment Type)',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Concept</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Short name',</td>

<td> Name varchar(255) DEFAULT NULL COMMENT 'Short name',</td>

</tr>

<tr>

<td>updated</td>

<td>datetime</td>

<td> COMMENT 'The timestamp of the last update to the concept',</td>

<td> updated datetime NOT NULL COMMENT 'The timestamp of the last update to the concept',</td>

</tr>

<tr>

<td>Draft</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>

<td> Draft tinyint(1) NOT NULL COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept',</td>

</tr>

<tr>

<td>Description</td>

<td>varchar(400)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Full name (or term for ontological concepts)',</td>

<td> Description varchar(400) DEFAULT NULL COMMENT 'Full name (or term for ontological concepts)',</td>

</tr>

<tr>

<td> COMMENT 'Unique Id of the concept',</td>

<td> dbid int NOT NULL COMMENT 'Unique Id of the concept',</td>

</tr>

<tr>

<td>Document</td>

<td> COMMENT 'Concept grouping construct, deprecated',</td>

<td> Document int NOT NULL COMMENT 'Concept grouping construct, deprecated',</td>

</tr>

<tr>

<td>varchar(150)</td>

<td> COMMENT 'Unique concept identifier',</td>

<td> Id varchar(150) NOT NULL COMMENT 'Unique concept identifier',</td>

</tr>

<tr>

<td>Scheme</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>

<td> Scheme bigint DEFAULT NULL COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)',</td>

</tr>

<tr>

<td>varchar(40)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The code (non-unique unless coupled with a scheme)',</td>

<td> Code varchar(40) DEFAULT NULL COMMENT 'The code (non-unique unless coupled with a scheme)',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Concept_map</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>updated</td>

<td>datetime</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>

</tr>

<tr>

<td> COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>

<td> core int NOT NULL COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to',</td>

</tr>

<tr>

<td>legacy</td>

<td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>

<td> legacy int NOT NULL COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Diagnostic_order</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>is_problem</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the observation is marked as a problem',</td>

<td> is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>

</tr>

<tr>

<td>result_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the result',</td>

<td> result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>

</tr>

<tr>

<td>problem_end_date</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>date_precision_concept_id</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> 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>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>episodicity_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>

<td> episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>

</tr>

<tr>

<td>result_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of the result',</td>

<td> result_date date DEFAULT NULL COMMENT 'The date of the result',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>result_value_units</td>

<td>varchar(50)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>is_primary</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the diagnostic order was identified by a clinician',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the diagnostic order was identified by a clinician',</td>

</tr>

<tr>

<td>is_review</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the observation is a review of an existing problem',</td>

<td> is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>

</tr>

<tr>

<td>result_value</td>

<td>double</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The value of the result of the observation',</td>

<td> result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>

</tr>

<tr>

<td>parent_observation_id</td>

<td>bigint</td>

<td>DEFAULT 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> 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>

<td>bigint</td>

<td> COMMENT 'Unique Id of the diagnostic order',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the diagnostic order',</td>

</tr>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age of the patient at the time 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>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>DEFAULT NULL</td>

<td> COMMENT 'The end date of the encounter',</td>

<td> end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>

</tr>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>appointment_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the appointment this encounter took part on',</td>

<td> appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age the patient was when this encounter took place',</td>

<td> age_at_event decimal(5,2) DEFAULT NULL COMMENT 'The age the patient was when this encounter took place',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the encounter was recorded',</td>

<td> date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>

</tr>

<tr>

<td>admission_method</td>

<td>varchar(40)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The admission method of the encounter',</td>

<td> admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the encounter',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the encounter',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>service_provider_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the service provider organisation',</td>

<td> service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the clinical code is recorded for',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the clinical code is recorded for',</td>

</tr>

<tr>

<td>date_precision_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the precision of the 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>

<td>episode_of_care_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Is this instance of the code a review of a previous encounter',</td>

<td> episode_of_care_id bigint DEFAULT NULL COMMENT 'Is this instance of the code a review of a previous encounter',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter_additional</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>value_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>

<td> value_id int DEFAULT NULL COMMENT 'IM concept id reference (i.e. Emergency admission)',</td>

</tr>

<tr>

<td>json_value</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)',</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>

<td>property_id</td>

<td> COMMENT 'IM concept id reference (i.e. Admission method)',</td>

<td> property_id int NOT NULL COMMENT 'IM concept id reference (i.e. Admission method)',</td>

</tr>

<tr>

<td>text_value</td>

<td>varchar(255)</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> 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>

<td>bigint</td>

<td> COMMENT 'same as the id column on the encounter table',</td>

<td> id bigint NOT NULL COMMENT 'same as the id column on the encounter table',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Encounter_event</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>DEFAULT NULL</td>

<td> COMMENT 'The end date of the encounter',</td>

<td> end_date date DEFAULT NULL COMMENT 'The end date of the encounter',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age of the patient at the time 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>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the parent encounter record',</td>

<td> encounter_id bigint NOT NULL COMMENT 'Reference to the parent encounter record',</td>

</tr>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>clinical_effective_date</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the encounter took place',</td>

<td> clinical_effective_date datetime DEFAULT NULL COMMENT 'The date the encounter took place',</td>

</tr>

<tr>

<td>appointment_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the appointment this encounter took part on',</td>

<td> appointment_id bigint DEFAULT NULL COMMENT 'Reference to the appointment this encounter took part on',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'The person this event belongs to',</td>

<td> person_id bigint NOT NULL COMMENT 'The person this event belongs to',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the encounter was recorded',</td>

<td> date_recorded datetime DEFAULT NULL COMMENT 'The date the encounter was recorded',</td>

</tr>

<tr>

<td>admission_method</td>

<td>varchar(40)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The admission method of the encounter',</td>

<td> admission_method varchar(40) DEFAULT NULL COMMENT 'The admission method of the encounter',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>service_provider_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the service provider organisation',</td>

<td> service_provider_organization_id bigint DEFAULT NULL COMMENT 'Reference to the service provider organisation',</td>

</tr>

<tr>

<td>finished</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Whether the encounter is finished',</td>

<td> finished tinyint(1) DEFAULT NULL COMMENT 'Whether the encounter is finished',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the encounter event',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the encounter event',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>institution_location_id</td>

<td>bigint</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</td>

<td> COMMENT 'The patient this event belongs to',</td>

<td> patient_id bigint NOT NULL COMMENT 'The patient this event belongs to',</td>

</tr>

<tr>

<td>episode_of_care_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the episode of care this encounter belongs to',</td>

<td> episode_of_care_id bigint DEFAULT NULL COMMENT 'Reference to the episode of care this encounter belongs to',</td>

</tr>

<tr>

<td>date_precision_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the precision of the 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>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Episode_of_care</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the episode of care',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the episode of care',</td>

</tr>

<tr>

<td>registration_status_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the registration status of the patient',</td>

<td> registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status of the patient',</td>

</tr>

<tr>

<td>date_registered</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the registration was started 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>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>usual_gp_practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the usual GP 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>

</tr>

<tr>

<td>date_registered_end</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the registration was ended for this episode of care',</td>

<td> date_registered_end date DEFAULT NULL COMMENT 'The date the registration was ended for this episode of care',</td>

</tr>

<tr>

<td>registration_type_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the registration type of the patient',</td>

<td> registration_type_concept_id int DEFAULT NULL COMMENT 'Reference to the registration type of the patient',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Event_log</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>dt_change</td>

<td>datetime(3)</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>

</tr>

<tr>

<td>change_type</td>

<td>tinyint</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>

</tr>

<tr>

<td>table_id</td>

<td>tinyint</td>

<td> COMMENT 'identifier of the table changed',</td>

<td> table_id tinyint NOT NULL COMMENT 'identifier of the table changed',</td>

</tr>

<tr>

<td>record_id</td>

<td>bigint</td>

<td> COMMENT 'id of the record changed'</td>

<td> record_id bigint NOT NULL COMMENT 'id of the record changed'</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Flag</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the flag was entered onto the patients record',</td>

<td> effective_date date DEFAULT NULL COMMENT 'The date the flag was entered onto the patients record',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>date_precision_concept_id</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> 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>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>is_active</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the flag is active or not',</td>

<td> is_active tinyint(1) NOT NULL COMMENT 'Whether the flag is active or not',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the flag',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the flag',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Location</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the location',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the location',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The type of location',</td>

<td> type_code varchar(50) DEFAULT NULL COMMENT 'The type of location',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary',</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>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Textual description of the type of location eg GP Practice',</td>

<td> type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of location eg GP Practice',</td>

</tr>

<tr>

<td>postcode</td>

<td>varchar(10)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The postcode of the location',</td>

<td> postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the location',</td>

</tr>

<tr>

<td>managing_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the managing organisation of the location',</td>

<td> managing_organization_id bigint DEFAULT NULL COMMENT 'Reference to the managing organisation of the location',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Medication_order</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>medication_statement_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the medication statement. A medication statement can have many medication orders',</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>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>quantity_unit</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>

<td> quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>

</tr>

<tr>

<td>duration_days</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'How many days the medication is prescribed for',</td>

<td> duration_days int DEFAULT NULL COMMENT 'How many days the medication is prescribed for',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age the patient was at the time of this event',</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>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter the medication order was issued in',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the medication order was issued in',</td>

</tr>

<tr>

<td>date_precision_concept_id</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> 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>

<td>quantity_value</td>

<td>double</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>

<td> quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>

</tr>

<tr>

<td>estimated_cost</td>

<td>double</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The estimated cost of the medication',</td>

<td> estimated_cost double DEFAULT NULL COMMENT 'The estimated cost of the medication',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the medication order was issued',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication order was issued',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>varchar(1000)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Textual description of the dose',</td>

<td> dose varchar(1000) DEFAULT NULL COMMENT 'Textual description of the dose',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the medication order',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the medication order',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>bnf_reference</td>

<td>varchar(6)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>

<td> bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Medication_statement</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>cancellation_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the medication was cancelled',</td>

<td> cancellation_date date DEFAULT NULL COMMENT 'The date the medication was cancelled',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>quantity_unit</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The unit of the medication that was prescribed eg tablets',</td>

<td> quantity_unit varchar(255) DEFAULT NULL COMMENT 'The unit of the medication that was prescribed eg tablets',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age the patient was at the time of this event',</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>

<td>date_precision_concept_id</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> 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>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter this medication was recorded in',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter this medication was recorded in',</td>

</tr>

<tr>

<td>quantity_value</td>

<td>double</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The value of the medication that was prescribed eg 50',</td>

<td> quantity_value double DEFAULT NULL COMMENT 'The value of the medication that was prescribed eg 50',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the medication',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the medication',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the medication was clinical relevant',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the medication was clinical relevant',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>varchar(1000)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Texual description of the dose of the medication',</td>

<td> dose varchar(1000) DEFAULT NULL COMMENT 'Texual description of the dose of the medication',</td>

</tr>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>authorisation_type_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the authorisation type',</td>

<td> authorisation_type_concept_id int DEFAULT NULL COMMENT 'Reference to the authorisation type',</td>

</tr>

<tr>

<td>is_active</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Whether the medication is active or not',</td>

<td> is_active tinyint(1) DEFAULT NULL COMMENT 'Whether the medication is active or not',</td>

</tr>

<tr>

<td>bnf_reference</td>

<td>varchar(6)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the drug in the BNF dictionary',</td>

<td> bnf_reference varchar(6) DEFAULT NULL COMMENT 'A reference to the drug in the BNF dictionary',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Observation</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the observation was identified by a clinician',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the observation was identified by a clinician',</td>

</tr>

<tr>

<td>is_problem</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the observation is marked as a problem',</td>

<td> is_problem tinyint(1) NOT NULL COMMENT 'Whether the observation is marked as a problem',</td>

</tr>

<tr>

<td>result_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the result',</td>

<td> result_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the result',</td>

</tr>

<tr>

<td>problem_end_date</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>date_precision_concept_id</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> 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>

<td>bigint</td>

<td> COMMENT 'Unique Id of the observation',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the observation',</td>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>episodicity_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>

<td> episodicity_concept_id int DEFAULT NULL COMMENT 'Reference to the episodicity of the problem eg First, review, flare',</td>

</tr>

<tr>

<td>is_primary</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Whether the observation is a primary observation',</td>

<td> is_primary tinyint(1) DEFAULT NULL COMMENT 'Whether the observation is a primary observation',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>result_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of the result',</td>

<td> result_date date DEFAULT NULL COMMENT 'The date of the result',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>result_value_units</td>

<td>varchar(50)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>is_review</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the observation is a review of an existing problem',</td>

<td> is_review tinyint(1) NOT NULL COMMENT 'Whether the observation is a review of an existing problem',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter the observation was recorded at',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the observation was recorded at',</td>

</tr>

<tr>

<td>result_value</td>

<td>double</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The value of the result of the observation',</td>

<td> result_value double DEFAULT NULL COMMENT 'The value of the result of the observation',</td>

</tr>

<tr>

<td>parent_observation_id</td>

<td>bigint</td>

<td>DEFAULT 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> 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>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> 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>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age of the patient at the time 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>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Observation_additional</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>value_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'IM reference (i.e. minor, significant)',</td>

<td> value_id int DEFAULT NULL COMMENT 'IM reference (i.e. minor, significant)',</td>

</tr>

<tr>

<td>json_value</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'the JSON data itself ',</td>

<td> json_value json DEFAULT NULL COMMENT 'the JSON data itself ',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'same as the id column on the observation table',</td>

<td> id bigint NOT NULL COMMENT 'same as the id column on the observation table',</td>

</tr>

<tr>

<td>property_id</td>

<td> COMMENT 'IM reference (i.e. significance)',</td>

<td> property_id int NOT NULL COMMENT 'IM reference (i.e. significance)',</td>

</tr>

<tr>

<td>text_value</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value',</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>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Organization</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Textual description of the type of organisation eg GP Practice',</td>

<td> type_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the type of organisation eg GP Practice',</td>

</tr>

<tr>

<td>Postcode</td>

<td>varchar(10)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The postcode of the organisation',</td>

<td> Postcode varchar(10) DEFAULT NULL COMMENT 'The postcode of the organisation',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'ODS Code of the organisation',</td>

<td> ods_code varchar(50) DEFAULT NULL COMMENT 'ODS Code of the organisation',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The type of organisation',</td>

<td> type_code varchar(50) DEFAULT NULL COMMENT 'The type of organisation',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the organisation',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the organisation',</td>

</tr>

<tr>

<td>parent_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The id of the parent organisation',</td>

<td> parent_organization_id bigint DEFAULT NULL COMMENT 'The id of the parent organisation',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Name of the organisation',</td>

<td> Name varchar(255) DEFAULT NULL COMMENT 'Name of the organisation',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Organization_metadata</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>publishing_software</td>

<td>varchar(50)</td>

<td> COMMENT 'software name of publishing system, i.e. SystmOne',</td>

<td> publishing_software varchar(50) NOT NULL COMMENT 'software name of publishing system, i.e. SystmOne',</td>

</tr>

<tr>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'date time data was last sent to DDS',</td>

<td> last_data_to_dds datetime DEFAULT NULL COMMENT 'date time data was last sent to DDS',</td>

</tr>

<tr>

<td>last_data_cutoff</td>

<td>datetime</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>

<td> id bigint NOT NULL COMMENT 'organisation ID, corresponds to same ID in the organizaton table',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>date_of_birth</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of birth of the patient',</td>

<td> date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the patient',</td>

</tr>

<tr>

<td>nhs_number</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>ethnic_code_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the ethnicity of the patient',</td>

<td> ethnic_code_concept_id int DEFAULT NULL COMMENT 'Reference to the ethnicity of the patient',</td>

</tr>

<tr>

<td>gender_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>current_address_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the current address of the patient',</td>

<td> current_address_id bigint DEFAULT NULL COMMENT 'Reference to the current address of the patient',</td>

</tr>

<tr>

<td>first_names</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The first names of the patient',</td>

<td> first_names varchar(255) DEFAULT NULL COMMENT 'The first names of the patient',</td>

</tr>

<tr>

<td>date_of_death</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of death of the patient',</td>

<td> date_of_death date DEFAULT NULL COMMENT 'The date of death of the patient',</td>

</tr>

<tr>

<td>registered_practice_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the organisation the patient is registered at',</td>

<td> registered_practice_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation the patient is registered at',</td>

</tr>

<tr>

<td>title</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The title of the patient',</td>

<td> title varchar(255) DEFAULT NULL COMMENT 'The title of the patient',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the patient',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the patient',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The last name of the patient',</td>

<td> last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the patient',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_additional</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'same as the id column on the patient table ',</td>

<td> id bigint NOT NULL COMMENT 'same as the id column on the patient table ',</td>

</tr>

<tr>

<td>json_value</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'where there is no mapped value_id, just raw JSON',</td>

<td> json_value json DEFAULT NULL COMMENT 'where there is no mapped value_id, just raw JSON',</td>

</tr>

<tr>

<td>text_value</td>

<td>varchar(255)</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> 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>

<td>value_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'IM reference (e.g. COVID)',</td>

<td> value_id int DEFAULT NULL COMMENT 'IM reference (e.g. COVID)',</td>

</tr>

<tr>

<td>property_id</td>

<td> COMMENT 'IM reference (e.g. Cause of death)',</td>

<td> property_id int NOT NULL COMMENT 'IM reference (e.g. Cause of death)',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>address_line_3</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The third line of the address',</td>

<td> address_line_3 varchar(255) DEFAULT NULL COMMENT 'The third line of the address',</td>

</tr>

<tr>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the LSOA_2011 code',</td>

<td> lsoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2011 code',</td>

</tr>

<tr>

<td>postcode</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The postcode',</td>

<td> postcode varchar(255) DEFAULT NULL COMMENT 'The postcode',</td>

</tr>

<tr>

<td>use_concept_id</td>

<td> COMMENT 'use of address (e.g. home, temporary)',</td>

<td> use_concept_id int NOT NULL COMMENT 'use of address (e.g. home, temporary)',</td>

</tr>

<tr>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the MSOA_2001 code',</td>

<td> msoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2001 code',</td>

</tr>

<tr>

<td>address_line_4</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The fourth line of the address',</td>

<td> address_line_4 varchar(255) DEFAULT NULL COMMENT 'The fourth line of the address',</td>

</tr>

<tr>

<td>start_date</td>

<td> COMMENT 'The start date of this address being relevant',</td>

<td> start_date date NOT NULL COMMENT 'The start date of this address being relevant',</td>

</tr>

<tr>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the LSOA_2001 code',</td>

<td> lsoa_2001_code varchar(9) DEFAULT NULL COMMENT 'A reference to the LSOA_2001 code',</td>

</tr>

<tr>

<td>address_line_1</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The first line of the address',</td>

<td> address_line_1 varchar(255) DEFAULT NULL COMMENT 'The first line of the address',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the address',</td>

<td> Id bigint NOT NULL COMMENT 'Unique Id of the address',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The city',</td>

<td> city varchar(255) DEFAULT NULL COMMENT 'The city',</td>

</tr>

<tr>

<td>DEFAULT NULL</td>

<td> COMMENT 'The end date of this address being relevant',</td>

<td> end_date date DEFAULT NULL COMMENT 'The end date of this address being relevant',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The ward the address belongs to',</td>

<td> ward_code varchar(9) DEFAULT NULL COMMENT 'The ward the address belongs to',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>local_authority_code</td>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The local authority the address belongs to',</td>

<td> local_authority_code varchar(9) DEFAULT NULL COMMENT 'The local authority the address belongs to',</td>

</tr>

<tr>

<td>address_line_2</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The second line of the address',</td>

<td> address_line_2 varchar(255) DEFAULT NULL COMMENT 'The second line of the address',</td>

</tr>

<tr>

<td>varchar(9)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'A reference to the MSOA_2011 code',</td>

<td> msoa_2011_code varchar(9) DEFAULT NULL COMMENT 'A reference to the MSOA_2011 code',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address_match</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_address_ralf</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_contact</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the patient contact',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the patient contact',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>DEFAULT NULL</td>

<td> COMMENT 'The end date of the contact being valid',</td>

<td> end_date date DEFAULT NULL COMMENT 'The end date of the contact being valid',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>use_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'use of contact (e.g. mobile, home,work',</td>

<td> use_concept_id int DEFAULT NULL COMMENT 'use of contact (e.g. mobile, home,work',</td>

</tr>

<tr>

<td>start_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The start date of the contact being valid',</td>

<td> start_date date DEFAULT NULL COMMENT 'The start date of the contact being valid',</td>

</tr>

<tr>

<td>value</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>type_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'type of contact (e.g. phone, email)',</td>

<td> type_concept_id int DEFAULT NULL COMMENT 'type of contact (e.g. phone, email)',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_pseudo_id</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</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>

</tr>

<tr>

<td>is_nhs_number_valid</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the nhs number is valid',</td>

<td> is_nhs_number_valid tinyint(1) NOT NULL COMMENT 'Whether the nhs number is valid',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>

<td> patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>

</tr>

<tr>

<td>is_nhs_number_verified_by_publisher</td>

<td>tinyint(1)</td>

<td> COMMENT 'Whether the nhs number has been verified by the publisher',</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>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the person this registration status history belongs to',</td>

<td> person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>

</tr>

<tr>

<td>varchar(255)</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> 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>

<td>varchar(50)</td>

<td> COMMENT 'The name of the salt used to create the pseudo id',</td>

<td> salt_name varchar(50) NOT NULL COMMENT 'The name of the salt used to create the pseudo id',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Patient_uprn</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Person</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>current_address_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the current address of the person',</td>

<td> current_address_id bigint NOT NULL COMMENT 'Reference to the current address of the person',</td>

</tr>

<tr>

<td>first_names</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>gender_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the gender of the person',</td>

<td> gender_concept_id int DEFAULT NULL COMMENT 'Reference to the gender of the person',</td>

</tr>

<tr>

<td>nhs_number</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The NHS number of the person',</td>

<td> nhs_number varchar(255) DEFAULT NULL COMMENT 'The NHS number of the person',</td>

</tr>

<tr>

<td>ethnic_code_concept_id</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>registered_practice_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>title</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The title of the person',</td>

<td> title varchar(255) DEFAULT NULL COMMENT 'The title of the person',</td>

</tr>

<tr>

<td>date_of_death</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of death of the person',</td>

<td> date_of_death date DEFAULT NULL COMMENT 'The date of death of the person',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>date_of_birth</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date of birth of the person',</td>

<td> date_of_birth date DEFAULT NULL COMMENT 'The date of birth of the person',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The last name of the person',</td>

<td> last_name varchar(255) DEFAULT NULL COMMENT 'The last name of the person',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the person',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the person',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Practitioner</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>varchar(1024)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Name of the practitioner',</td>

<td> name varchar(1024) DEFAULT NULL COMMENT 'Name of the practitioner',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the practitioner',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the practitioner',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The code representing the role of the practitioner',</td>

<td> role_code varchar(50) DEFAULT NULL COMMENT 'The code representing the role of the practitioner',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The GMC code of the practitioner',</td>

<td> gmc_code varchar(50) DEFAULT NULL COMMENT 'The GMC code of the practitioner',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>

<td> role_desc varchar(255) DEFAULT NULL COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Procedure_request</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the procedure',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the procedure',</td>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter the procedure was administered at',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the procedure was administered at',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age of the patient at the time of the procedure',</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>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the procedure',</td>

<td> core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>

</tr>

<tr>

<td>date_precision_concept_id</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> 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>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the procedure was administered by a clinician',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the procedure was administered by a clinician',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the procedure was recorded in the source system',</td>

<td> date_recorded datetime DEFAULT NULL COMMENT 'The date the procedure was recorded in the source system',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>status_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the status of the procedure',</td>

<td> status_concept_id int DEFAULT NULL COMMENT 'Reference to the status of the procedure',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the procedure',</td>

<td> non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the procedure',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Referral_request</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the referral',</td>

<td> core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>

</tr>

<tr>

<td>non_core_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the clinical coding of the referral',</td>

<td> non_core_concept_id int DEFAULT NULL COMMENT 'Reference to the clinical coding of the referral',</td>

</tr>

<tr>

<td>requester_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the organisation that made the refereral request',</td>

<td> requester_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organisation that made the refereral request',</td>

</tr>

<tr>

<td>encounter_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the encounter the referral was made in',</td>

<td> encounter_id bigint DEFAULT NULL COMMENT 'Reference to the encounter the referral was made in',</td>

</tr>

<tr>

<td>referral_request_type_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the type of referral request',</td>

<td> referral_request_type_concept_id int DEFAULT NULL COMMENT 'Reference to the type of referral request',</td>

</tr>

<tr>

<td>outgoing_referral</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Whether this is an outgoing referral',</td>

<td> outgoing_referral tinyint(1) DEFAULT NULL COMMENT 'Whether this is an outgoing referral',</td>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the referral',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the referral',</td>

</tr>

<tr>

<td>referral_request_priority_concept_id</td>

<td>smallint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the priority of the referral',</td>

<td> referral_request_priority_concept_id smallint DEFAULT NULL COMMENT 'Reference to the priority of the referral',</td>

</tr>

<tr>

<td>is_review</td>

<td>tinyint(1)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Whether this referral is a review',</td>

<td> is_review tinyint(1) DEFAULT NULL COMMENT 'Whether this referral is a review',</td>

</tr>

<tr>

<td>varchar(50)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The mode of the referral',</td>

<td> Mode varchar(50) DEFAULT NULL COMMENT 'The mode of the referral',</td>

</tr>

<tr>

<td>age_at_event</td>

<td>decimal(5,2)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The age of the patient at the time of the referral',</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>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The clinician the activity is recorded against',</td>

<td> practitioner_id bigint DEFAULT NULL COMMENT 'The clinician the activity is recorded against',</td>

</tr>

<tr>

<td>date_recorded</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the referral request was added to the source system',</td>

<td> date_recorded datetime DEFAULT NULL COMMENT 'The date the referral request was added to the source system',</td>

</tr>

<tr>

<td>date_precision_concept_id</td>

<td>smallint</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> 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>

<td>patient_id</td>

<td>bigint</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>

</tr>

<tr>

<td>clinical_effective_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The date the referral was made',</td>

<td> clinical_effective_date date DEFAULT NULL COMMENT 'The date the referral was made',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Unique individual across all organisations',</td>

<td> person_id bigint NOT NULL COMMENT 'Unique individual across all organisations',</td>

</tr>

<tr>

<td>recipient_organization_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the organization receiving the referral',</td>

<td> recipient_organization_id bigint DEFAULT NULL COMMENT 'Reference to the organization receiving the referral',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Registration_status_history</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the registration status history',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the registration status history',</td>

</tr>

<tr>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The end date for the period this registration status history was valid',</td>

<td> end_date datetime DEFAULT NULL COMMENT 'The end date for the period this registration status history was valid',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>patient_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the patient this registration status history belongs to',</td>

<td> patient_id bigint NOT NULL COMMENT 'Reference to the patient this registration status history belongs to',</td>

</tr>

<tr>

<td>person_id</td>

<td>bigint</td>

<td> COMMENT 'Reference to the person this registration status history belongs to',</td>

<td> person_id bigint NOT NULL COMMENT 'Reference to the person this registration status history belongs to',</td>

</tr>

<tr>

<td>start_date</td>

<td>datetime</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The start date for the period this registration status history was valid',</td>

<td> start_date datetime DEFAULT NULL COMMENT 'The start date for the period this registration status history was valid',</td>

</tr>

<tr>

<td>episode_of_care_id</td>

<td>bigint</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the episode of care this 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>

<td>registration_status_concept_id</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'Reference to the registration status',</td>

<td> registration_status_concept_id int DEFAULT NULL COMMENT 'Reference to the registration status',</td>

</tr>

</tbody></table><h2><span class="mw-headline" id="Sortable_tables">Schedule</span></h2>

<tr>

<th scope="col" width="10%">Column Name</th>

<th scope="col" width="10%">Constraint</th>

<th scope="col" width="10%">Comment</th>

</tr>

<tbody>

<tr>

<td>start_date</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The start date of the schedule',</td>

<td> start_date date DEFAULT NULL COMMENT 'The start date of the schedule',</td>

</tr>

<tr>

<td>varchar(150)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The name of the schedule',</td>

<td> name varchar(150) DEFAULT NULL COMMENT 'The name of the schedule',</td>

</tr>

<tr>

<td>organization_id</td>

<td>bigint</td>

<td> COMMENT 'Owning organisation (i.e. publisher)',</td>

<td> organization_id bigint NOT NULL COMMENT 'Owning organisation (i.e. publisher)',</td>

</tr>

<tr>

<td>varchar(255)</td>

<td>DEFAULT NULL</td>

<td> COMMENT 'The type of schedule eg Timed Appointments',</td>

<td> type varchar(255) DEFAULT NULL COMMENT 'The type of schedule eg Timed Appointments',</td>

</tr>

<tr>

<td>location</td>

<td>varchar(255)</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>practitioner_id</td>

<td>bigint</td>

<td>DEFAULT NULL</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>

</tr>

<tr>

<td>bigint</td>

<td> COMMENT 'Unique Id of the schedule',</td>

<td> id bigint NOT NULL COMMENT 'Unique Id of the schedule',</td>

</tr>

</tbody></table>

CompassV2TestPages:v2.X: Difference between revisions

Revision as of 13:21, 22 November 2022

Navigation menu

Search