Voror_Wiki - User contributions [en-gb]

Compass 2.1 Schema Documentation

2023-01-06T16:47:43Z

DavidHesketh:

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

<div id="bodyContent" class="mw-body-content">
<div id="siteSub" class="noprint">From Discovery Data Service</div>
<div id="contentSub"></div>
<h1><span class="mw-headline" id="Sortable_tables">allergy_intolerance</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the allergy'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter this allergy was record in'</td>
<td>encounter.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the clinical code is recorded for'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Is this instance of the code a review of a previous encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the allergy'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the allergy'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age the patient was at the time of this event'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> 'The date the allergy was recorded'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `allergy_intolerance_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_allergy_intolerance_encounter_id` Index Columns: (encounter_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_allergy_intolerance_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_allergy_intolerance_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `allergy_intolerance_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `allergy_intolerance_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">appointment</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the appointment'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>organization.id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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>No Foreign Key reference</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>schedule_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>start_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The start date of the appointment'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>planned_duration</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'The time allocated for the appointment, not necessarily the actual duration always in minutes'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>actual_duration</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Time between sent in and left always in minutes'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>appointment_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'The status of the appointment e.g. arrived/sent in/left/DNA'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>patient_wait</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'How long the patient waited from being marked as arrived to being sent in'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>patient_delay</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'How long the patient was delayed for'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_time_sent_in</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'Date and time the patient was sent into the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_time_left</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'Date and time the patient left the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>source_id</td>
<td>varchar(36)</td>
<td>DEFAULT NULL</td>
<td> 'Unique reference to the source of the appointment'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>cancelled_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the appointment was cancelled'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `appointment_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_appointment_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `appointment_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">appointment_additional</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'same as the id column on the patient table '</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'IM reference (e.g. appointment type)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'IM reference (e.g. appointment type)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id, just raw JSON'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>text_value</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `appointment_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">concept</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>dbid</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'Unique concept int DB identifier'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>document</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Document this concept originated from'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>id</td>
<td>varchar(150)</td>
<td>CHARACTER SET</td>
<td> 'Unique human-readable concept id'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>draft</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Short name'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>description</td>
<td>varchar(400)</td>
<td>DEFAULT NULL</td>
<td> 'Full name (or term for ontological concepts)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>scheme</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The coding scheme for the code (Read, CTV3, SNOMED etc)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>code</td>
<td>varchar(40)</td>
<td>CHARACTER SET</td>
<td> 'The code (non-unique unless coupled with a scheme)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>use_count</td>
<td>bigint</td>
<td>No Constraint</td>
<td> 'Rough indicator of number of occurences of the concept'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>updated</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> 'The timestamp of the last update to the concept'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `ix_scheme_code` Index Columns: (scheme,code)</td> </tr> <tr>
<td colspan="5">Index name: `ix_code` Index Columns: (code)</td> </tr> <tr>
<td colspan="5">Index name: `ix_dbid_code` Index Columns: (dbid,code)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`dbid`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">concept_map</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>legacy</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'the core (snomed, discovery) concept that the legacy concept maps to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>updated</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> 'Timestamp the map was last updated/added'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>id</td>
<td>int</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>deleted</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `concept_map_uq` Index Columns: (legacy,deleted,updated)</td> </tr> <tr>
<td colspan="5">Index name: `ix_legacy_core` Index Columns: (legacy,core)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">database_version</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>version</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td> 'version of the database'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>valid_from</td>
<td>date</td>
<td>NOT NULL</td>
<td> 'date that the version was updated'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>valid_to</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'date that the version was made outdated'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>wiki_page</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'the wiki page where this database structure is found'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `valid_to_uq` Index Columns: (valid_to)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`valid_from`,`version`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">diagnostic_order</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the diagnostic order'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter the observation was recorded at'</td>
<td>encounter.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the diagnostic order was identified by a clinician'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> 'The value of the result of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_value_units</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The units of the result of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_text</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Any text associated with the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_problem</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the observation is marked as a problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the observation is a review of an existing problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>problem_end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The end date of the problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>parent_observation_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> '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>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age of the patient at the time of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>episodicity_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the episodicity of the problem eg First, review, flare'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_primary</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> 'Whether the diagnostic order is a primary order'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `diagnostic_order_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_diagnostic_order_encounter_id` Index Columns: (encounter_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_diagnostic_order_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_diagnostic_order_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_non_core_concept_id` Index Columns: (non_core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_clinical_effective_date` Index Columns: (clinical_effective_date)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">encounter</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>appointment_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the appointment this encounter took part on'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the clinical code is recorded for'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the precision of the date of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Is this instance of the code a review of a previous encounter'</td>
<td>episode_of_care.id</td>
</tr>
<tr>
<td>service_provider_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the service provider organisation'</td>
<td>organization.id</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the type of encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the type of encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age the patient was when this encounter took place'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Unused'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>sub_type</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Unused'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>admission_method</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td> 'The admission method of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>end_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The end date of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>institution_location_id</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Reference to the institution the encounter took place at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the encounter was recorded'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `encounter_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_episode_of_care_id` Index Columns: (episode_of_care_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_service_provider_organization_id` Index Columns: (service_provider_organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `encounter_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_encounter_appointment_id` Index Columns: (appointment_id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_encounter_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `encounter_core_concept_id_clinical_effective_date` Index Columns: (core_concept_id,clinical_effective_date)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">encounter_additional</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'same as the id column on the encounter table'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'IM concept id reference (i.e. Admission method)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'IM concept id reference (i.e. Emergency admission)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>text_value</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `encounter_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">encounter_event</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the encounter event'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'The patient this event belongs to'</td>
<td>patient.id</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'The person this event belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the parent encounter record'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>appointment_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the appointment this encounter took part on'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the encounter took place'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the precision of the date of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the episode of care this encounter belongs to'</td>
<td>episode_of_care.id</td>
</tr>
<tr>
<td>service_provider_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the service provider organisation'</td>
<td>organization.id</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the type of encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the type of encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age of the patient at the time of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Unused'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>sub_type</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Unused'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>admission_method</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td> 'The admission method of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>end_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The end date of the encounter'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>institution_location_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the institution the encounter took place at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the encounter was recorded'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>finished</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> 'Whether the encounter is finished'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `encounter_event_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_event_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_event_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_event_episode_of_care_id` Index Columns: (episode_of_care_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_encounter_event_service_provider_organization_id` Index Columns: (service_provider_organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">episode_of_care</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the episode of care'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>registration_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the registration type of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>registration_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the registration status of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_registered</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date the registration was started for this episode of care'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_registered_end</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date the registration was ended for this episode of care'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>usual_gp_practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the usual GP for this episode of care'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td colspan="5">Unique index name: `episode_of_care_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_episode_of_care_patient_id_organisation_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_episode_of_care_practitioner_id` Index Columns: (usual_gp_practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_registration_type_concept_id` Index Columns: (registration_type_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_date_registered` Index Columns: (date_registered)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_date_registered_end` Index Columns: (date_registered_end)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">event_log</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>dt_change</td>
<td>datetime(3)</td>
<td>NOT NULL</td>
<td> 'date time the change was made to this DB'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>change_type</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td> 'type of transaction 0=insert, 1=update, 2=delete'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>table_id</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td> 'identifier of the table changed'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>record_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'id of the record changed'</td>
<td>No Foreign Key reference</td>
</tr>
</table>
<h1><span class="mw-headline" id="Sortable_tables">flag</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the flag'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the flag was entered onto the patients record'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_active</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the flag is active or not'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>flag_text</td>
<td>text</td>
<td>No Constraint</td>
<td> 'This is a warning set by the publisher regarding he patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `flag_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_flag_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `flag_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">location</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the location'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The type of location'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Textual description of the type of location eg GP Practice'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>postcode</td>
<td>varchar(10)</td>
<td>DEFAULT NULL</td>
<td> 'The postcode of the location'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>managing_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the managing organisation of the location'</td>
<td>organization.id</td>
</tr>
<tr>
<td colspan="5">Unique index name: `location_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_location_managing_organisation_id` Index Columns: (managing_organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">medication_order</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the medication order'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter the medication order was issued in'</td>
<td>encounter.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the medication order was issued'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>dose</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td> 'Textual description of the dose'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>quantity_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> 'The value of the medication that was prescribed eg 50'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>quantity_unit</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The unit of the medication that was prescribed eg tablets'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>duration_days</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'How many days the medication is prescribed for'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>estimated_cost</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> 'The estimated cost of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>medication_statement_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the medication statement. A medication statement can have many medication orders'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>bnf_reference</td>
<td>varchar(6)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the drug in the BNF dictionary'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age the patient was at the time of this event'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>issue_method</td>
<td>text</td>
<td>No Constraint</td>
<td> 'The issue method of the medication eg hand written'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `medication_order_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_order_encounter_id` Index Columns: (encounter_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_order_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_order_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_order_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_order_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">medication_statement</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter this medication was recorded in'</td>
<td>encounter.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the medication was clinical relevant'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_active</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> 'Whether the medication is active or not'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>cancellation_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date the medication was cancelled'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>dose</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td> 'Texual description of the dose of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>quantity_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> 'The value of the medication that was prescribed eg 50'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>quantity_unit</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The unit of the medication that was prescribed eg tablets'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>authorisation_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the authorisation type'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the medication'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>bnf_reference</td>
<td>varchar(6)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the drug in the BNF dictionary'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age the patient was at the time of this event'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>issue_method</td>
<td>text</td>
<td>No Constraint</td>
<td> 'The issue method of the medication eg hand written'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `medication_statement_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_statement_encounter_id` Index Columns: (encounter_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_statement_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_medication_statement_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_statement_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_statement_dmd_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">observation</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter the observation was recorded at'</td>
<td>encounter.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the observation was identified by a clinician'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> 'The value of the result of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_value_units</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The units of the result of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_text</td>
<td>text</td>
<td>No Constraint</td>
<td> 'Any text associated with the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>result_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the result'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_problem</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the observation is marked as a problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the observation is a review of an existing problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>problem_end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The end date of the problem'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>parent_observation_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> '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>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age of the patient at the time of the observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>episodicity_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the episodicity of the problem eg First, review, flare'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_primary</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> 'Whether the observation is a primary observation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the observation was recorded in the system'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `observation_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_observation_encounter_id` Index Columns: (encounter_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_observation_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_observation_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)</td> </tr> <tr>
<td colspan="5">Index name: `observation_non_core_concept_id` Index Columns: (non_core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_clinical_effective_date` Index Columns: (clinical_effective_date)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">observation_additional</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'same as the id column on the observation table'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'IM reference (i.e. significance)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'IM reference (i.e. minor, significant)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 'the JSON data itself '</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>text_value</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id or raw JSON, just a basic text value'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `observation_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">organization</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>ods_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'ODS Code of the organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Name of the organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The type of organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Textual description of the type of organisation eg GP Practice'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>postcode</td>
<td>varchar(10)</td>
<td>DEFAULT NULL</td>
<td> 'The postcode of the organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>parent_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The id of the parent organisation'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `organization_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_organization_parent_organization_id` Index Columns: (parent_organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">organization_metadata</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'organisation ID, corresponds to same ID in the organizaton table'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>publishing_software</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td> 'software name of publishing system, i.e. SystmOne'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>last_data_to_dds</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'date time data was last sent to DDS'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>last_data_cutoff</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'cutoff date time of the last extract from the publishing system'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`publishing_software`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>organization.id</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>title</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The title of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>first_names</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The first names of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>last_name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The last name of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>gender_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the gender of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>nhs_number</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The NHS number of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_of_birth</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of birth of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_of_death</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of death of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>current_address_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the current address of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the ethnicity of the patient'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>registered_practice_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the organisation the patient is registered at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_year</td>
<td>smallint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_month</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_week</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `patient_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_additional</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'same as the id column on the patient table '</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'IM reference (e.g. Cause of death)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'IM reference (e.g. COVID)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id, just raw JSON'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>text_value</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `ix_patient_additional_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_address</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>address_line_1</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The first line of the address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>address_line_2</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The second line of the address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>address_line_3</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The third line of the address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>address_line_4</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The fourth line of the address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>city</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The city'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>postcode</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The postcode'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>use_concept_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> 'use of address (e.g. home, temporary)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>NOT NULL</td>
<td> 'The start date of this address being relevant'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The end date of this address being relevant'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>lsoa_2001_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the LSOA_2001 code'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>lsoa_2011_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the LSOA_2011 code'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>msoa_2001_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the MSOA_2001 code'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>msoa_2011_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'A reference to the MSOA_2011 code'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>ward_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'The ward the address belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>local_authority_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> 'The local authority the address belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>townsend_deprivation_index</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_address_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_patient_address_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_address_match</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>patient_address_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>patient_address.id</td>
</tr>
<tr>
<td>uprn</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>uprn_ralf00</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>status</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>uprn_property_classification</td>
<td>varchar(45)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>latitude</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>longitude</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>uprn_xcoordinate</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>uprn_ycoordinate</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>qualifier</td>
<td>varchar(50)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_rule</td>
<td>varchar(4096)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_date</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_number</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_street</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_locality</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_town</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_postcode</td>
<td>varchar(10)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>abp_address_organization</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_pattern_postcode</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_pattern_street</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_pattern_number</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_pattern_building</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match_pattern_flat</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>algorithm_version</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>epoch</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `patient_address_uprn_index` Index Columns: (uprn)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_patient_address_id` Index Columns: (id,uprn)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_uprn_patient_address_id_fk` Index Columns: (patient_address_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_address_ralf</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>organization.id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>patient.id</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>person.id</td>
</tr>
<tr>
<td>patient_address_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>patient_address.id</td>
</tr>
<tr>
<td>patient_address_match_uprn_ralf00</td>
<td>varchar(255)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>salt_name</td>
<td>varchar(50)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>ralf</td>
<td>varchar(255)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Index name: `fk_patient_address_ralf_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_patient_address_ralf_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">Index name: `ux_patient_address_ralf_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_address_id` Index Columns: (patient_address_id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_address_match_uprn_ralf_00` Index Columns: (patient_address_match_uprn_ralf00)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`patient_address_id`,`patient_address_match_uprn_ralf00`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_contact</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the patient contact'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>use_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'use of contact (e.g. mobile, home,work'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'type of contact (e.g. phone, email)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The start date of the contact being valid'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The end date of the contact being valid'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>value</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The value of the contact information eg phone number, email address'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_contact_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_patient_contact_patient_id_organisation_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_pseudo_id</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the patient pseudo id'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the patient this registration status history belongs to'</td>
<td>patient.id</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the person this registration status history belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>salt_name</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td> 'The name of the salt used to create the pseudo id'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>skid</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td> '"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>No Foreign Key reference</td>
</tr>
<tr>
<td>is_nhs_number_valid</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the nhs number is valid'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_nhs_number_verified_by_publisher</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether the nhs number has been verified by the publisher'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_pseudo_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_patient_pseudo_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_pseudo_id_patient` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">patient_uprn</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>uprn</td>
<td>bigint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>qualifier</td>
<td>varchar(50)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>algorithm</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>match</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>no_address</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>invalid_address</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>missing_postcode</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>invalid_postcode</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `patient_uprn_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_patient_uprn_patient_id_organisation_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">person</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>title</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The title of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>first_names</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The first names of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>last_name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The last name of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>gender_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the gender of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>nhs_number</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The NHS number of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_of_birth</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of birth of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_of_death</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> 'The date of death of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>current_address_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the current address of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the ethnicity of the person'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>registered_practice_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the organisation the person is registered at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_year</td>
<td>smallint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_month</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>birth_week</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `person_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">practitioner</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>organization.id</td>
</tr>
<tr>
<td>name</td>
<td>varchar(1024)</td>
<td>DEFAULT NULL</td>
<td> 'Name of the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>role_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The code representing the role of the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>role_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Textual description of the role of the practitioner eg General Medical Practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>gmc_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The GMC code of the practitioner'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `practitioner_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_practitioner_organisation_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">procedure_request</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the procedure'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter the procedure was administered at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the procedure was administered by a clinician'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the status of the procedure'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the procedure'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the procedure'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age of the patient at the time of the procedure'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the procedure was recorded in the source system'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `procedure_request_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_procedure_request_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_procedure_request_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `procedure_request_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">referral_request</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>patient.organization_id</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> '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</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique individual across all organisations'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the encounter the referral was made in'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'The clinician the activity is recorded against'</td>
<td>practitioner.id</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the referral was made'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>requester_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the organisation that made the refereral request'</td>
<td>organization.id</td>
</tr>
<tr>
<td>recipient_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the organization receiving the referral'</td>
<td>organization.id</td>
</tr>
<tr>
<td>referral_request_priority_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the priority of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>referral_request_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the type of referral request'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>mode</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> 'The mode of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>outgoing_referral</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> 'Whether this is an outgoing referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> 'Whether this referral is a review'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the clinical coding of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>age_at_event</td>
<td>decimal(5,2)</td>
<td>DEFAULT NULL</td>
<td> 'The age of the patient at the time of the referral'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The date the referral request was added to the source system'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `referral_request_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_referral_request_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_referral_request_practitioner_id` Index Columns: (practitioner_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_referral_request_recipient_organization_id` Index Columns: (recipient_organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_referral_request_requester_organization_id` Index Columns: (requester_organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `referral_request_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `referral_request_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">registration_status_history</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the registration status history'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the patient this registration status history belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Reference to the person this registration status history belongs to'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>episode_of_care_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the episode of care this status history belongs to'</td>
<td>episode_of_care.id</td>
</tr>
<tr>
<td>registration_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the registration status'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>start_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The start date for the period this registration status history was valid'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>end_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The end date for the period this registration status history was valid'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_registration_status_history_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_registration_status_history_episode_id` Index Columns: (episode_of_care_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">schedule</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
<th scope="col">Foreign Key Reference</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Unique Id of the schedule'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 'Owning organisation (i.e. publisher)'</td>
<td>organization.id</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> 'Reference to the practitioner who owns the schedule'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>start_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 'The start date of the schedule'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>type</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'The type of schedule eg Timed Appointments'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>location</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 'Textual description of the location the schedule was held at'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td>name</td>
<td>varchar(150)</td>
<td>DEFAULT NULL</td>
<td> 'The name of the schedule'</td>
<td>No Foreign Key reference</td>
</tr>
<tr>
<td colspan="5">Unique index name: `schedule_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`)</td> </tr> </table>
<h1><span class="mw-headline" id="Sortable_tables">DATABASE UPDATE INFO</span></h1>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">New Version</th>
<th scope="col">Date Updated</th>
</tr>
<tr>
<td>2.1</td>
<td>2023-01-06T16:46:00.566742300</td>
</tr></table>

2022-11-28T13:15:04Z

DavidHesketh:

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

<div id="bodyContent" class="mw-body-content">
<div id="siteSub" class="noprint">From Discovery Data Service</div>
<div id="contentSub"></div>
<h2><span class="mw-headline" id="Sortable_tables">allergy_intolerance</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the allergy'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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 this allergy was record in'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the clinical code is recorded for'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Is this instance of the code a review of a previous encounter'</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the allergy'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the allergy'</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>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> COMMENT 'The date the allergy was recorded'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `allergy_intolerance_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `allergy_intolerance_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `allergy_intolerance_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">appointment</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the appointment'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</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>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The start date of the appointment'</td>
</tr>
<tr>
<td>planned_duration</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes'</td>
</tr>
<tr>
<td>actual_duration</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Time between sent in and left always in minutes'</td>
</tr>
<tr>
<td>appointment_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA'</td>
</tr>
<tr>
<td>patient_wait</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'How long the patient waited from being marked as arrived to being sent in'</td>
</tr>
<tr>
<td>patient_delay</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'How long the patient was delayed for'</td>
</tr>
<tr>
<td>date_time_sent_in</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Date and time the patient was sent into the practitioner'</td>
</tr>
<tr>
<td>date_time_left</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Date and time the patient left the practitioner'</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>
</tr>
<tr>
<td>cancelled_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the appointment was cancelled'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `appointment_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `appointment_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">appointment_additional</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'same as the id column on the patient table '</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (e.g. appointment type)'</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (e.g. Appointment Type)'</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td colspan="5">Index name: `appointment_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">concept</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>dbid</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the concept'</td>
</tr>
<tr>
<td>Document</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'Concept grouping construct, deprecated'</td>
</tr>
<tr>
<td>Id</td>
<td>varchar(150)</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique concept identifier'</td>
</tr>
<tr>
<td>Draft</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept'</td>
</tr>
<tr>
<td>Name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Short name'</td>
</tr>
<tr>
<td>Description</td>
<td>varchar(400)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Full name (or term for ontological concepts)'</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>
</tr>
<tr>
<td>Code</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The code (non-unique unless coupled with a scheme)'</td>
</tr>
<tr>
<td>use_count</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Rough indicator of number of occurences of the concept'</td>
</tr>
<tr>
<td>updated</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> COMMENT 'The timestamp of the last update to the concept'</td>
</tr>
<tr>
<td colspan="5">Index name: `ix_scheme_code` Index Columns: (Scheme,Code)</td> </tr> <tr>
<td colspan="5">Index name: `ix_code` Index Columns: (Code)</td> </tr> <tr>
<td colspan="5">Index name: `ix_dbid_code` Index Columns: (dbid,Code)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`dbid`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">concept_map</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>legacy</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'</td>
</tr>
<tr>
<td>core</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to'</td>
</tr>
<tr>
<td>updated</td>
<td>datetime</td>
<td>NOT NULL</td>
<td> COMMENT 'Timestamp the map was last updated/added'</td>
</tr>
<tr>
<td>id</td>
<td>int</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>deleted</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `concept_map_uq` Index Columns: (legacy,deleted,updated)</td> </tr> <tr>
<td colspan="5">Index name: `ix_legacy_core` Index Columns: (legacy,core)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">diagnostic_order</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the diagnostic order'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the diagnostic order was identified by a clinician'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>result_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the result of the observation'</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>
</tr>
<tr>
<td>result_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of the result'</td>
</tr>
<tr>
<td>result_text</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Any text associated with the result'</td>
</tr>
<tr>
<td>result_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the result'</td>
</tr>
<tr>
<td>is_problem</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is marked as a problem'</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is a review of an existing problem'</td>
</tr>
<tr>
<td>problem_end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the problem'</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>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>episodicity_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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 diagnostic order is a primary order'</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `diagnostic_order_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)</td> </tr> <tr>
<td colspan="5">Index name: `diagnostic_order_non_core_concept_id` Index Columns: (non_core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_clinical_effective_date` Index Columns: (clinical_effective_date)</td> </tr> <tr>
<td colspan="5">Index name: `ix_diagnostic_order_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">encounter</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the encounter'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</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>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the clinical code is recorded for'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>service_provider_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the service provider organisation'</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter'</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>
</tr>
<tr>
<td>Type</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Unused'</td>
</tr>
<tr>
<td>sub_type</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Unused'</td>
</tr>
<tr>
<td>admission_method</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The admission method of the encounter'</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the encounter'</td>
</tr>
<tr>
<td>institution_location_id</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Reference to the institution the encounter took place at'</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the encounter was recorded'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `encounter_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `encounter_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_encounter_appointment_id` Index Columns: (appointment_id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_encounter_patient_id_organization_id` Index Columns: (patient_id,organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `encounter_core_concept_id_clinical_effective_date` Index Columns: (core_concept_id,clinical_effective_date)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">encounter_additional</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'same as the id column on the encounter table'</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'IM concept id reference (i.e. Admission method)'</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM concept id reference (i.e. Emergency admission)'</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)'</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>
</tr>
<tr>
<td colspan="5">Index name: `encounter_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">encounter_event</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the encounter event'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The patient this event belongs to'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The person this event belongs to'</td>
</tr>
<tr>
<td>encounter_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the parent encounter record'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</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>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the encounter took place'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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 'Reference to the episode of care this encounter belongs to'</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>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of encounter'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of 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>
</tr>
<tr>
<td>type</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Unused'</td>
</tr>
<tr>
<td>sub_type</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Unused'</td>
</tr>
<tr>
<td>admission_method</td>
<td>varchar(40)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The admission method of the encounter'</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the 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>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the encounter was recorded'</td>
</tr>
<tr>
<td>finished</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the encounter is finished'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `encounter_event_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">episode_of_care</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the episode of care'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>registration_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the registration type of the patient'</td>
</tr>
<tr>
<td>registration_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the registration status of the patient'</td>
</tr>
<tr>
<td>date_registered</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the registration was started for this episode of care'</td>
</tr>
<tr>
<td>date_registered_end</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the registration was ended for this episode of care'</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>
</tr>
<tr>
<td colspan="5">Unique index name: `episode_of_care_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_registration_type_concept_id` Index Columns: (registration_type_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_date_registered` Index Columns: (date_registered)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_date_registered_end` Index Columns: (date_registered_end)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">Index name: `episode_of_care_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">event_log</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>dt_change</td>
<td>datetime(3)</td>
<td>NOT NULL</td>
<td> COMMENT 'date time the change was made to this DB'</td>
</tr>
<tr>
<td>change_type</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td> COMMENT 'type of transaction 0=insert, 1=update, 2=delete'</td>
</tr>
<tr>
<td>table_id</td>
<td>tinyint</td>
<td>NOT NULL</td>
<td> COMMENT 'identifier of the table changed'</td>
</tr>
<tr>
<td>record_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'id of the record changed'</td>
</tr>
</table>
<h2><span class="mw-headline" id="Sortable_tables">flag</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the flag'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the flag was entered onto the patients record'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>is_active</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the flag is active or not'</td>
</tr>
<tr>
<td>flag_text</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'This is a warning set by the publisher regarding he patient'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `flag_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `flag_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">location</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the location'</td>
</tr>
<tr>
<td>name</td>
<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>
</tr>
<tr>
<td>type_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The type of location'</td>
</tr>
<tr>
<td>type_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 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>
</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>
</tr>
<tr>
<td colspan="5">Unique index name: `location_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fk_location_managing_organisation_id` Index Columns: (managing_organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">medication_order</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the medication order'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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 medication order was issued in'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication order was issued'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>dose</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the dose'</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>
</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>
</tr>
<tr>
<td>duration_days</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'How many days the medication is prescribed for'</td>
</tr>
<tr>
<td>estimated_cost</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The estimated cost of the medication'</td>
</tr>
<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>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication'</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>
</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>
</tr>
<tr>
<td>issue_method</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'The issue method of the medication eg hand written'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `medication_order_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_order_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_order_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">medication_statement</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the medication'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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 this medication was recorded in'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication was clinical relevant'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>is_active</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether the medication is active or not'</td>
</tr>
<tr>
<td>cancellation_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the medication was cancelled'</td>
</tr>
<tr>
<td>dose</td>
<td>varchar(1000)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Texual description of the dose of the medication'</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>
</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>
</tr>
<tr>
<td>authorisation_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the authorisation type'</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the medication'</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>
</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>
</tr>
<tr>
<td>Issue_method</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'The issue method of the medication eg hand written'</td>
</tr>
<tr>
<td>date_recorded</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `medication_statement_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_statement_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `medication_statement_dmd_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">observation</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the observation'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the observation was identified by a clinician'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>result_value</td>
<td>double</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The value of the result of the observation'</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>
</tr>
<tr>
<td>result_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of the result'</td>
</tr>
<tr>
<td>result_text</td>
<td>text</td>
<td>No Constraint</td>
<td> COMMENT 'Any text associated with the result'</td>
</tr>
<tr>
<td>result_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the result'</td>
</tr>
<tr>
<td>is_problem</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is marked as a problem'</td>
</tr>
<tr>
<td>is_review</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the observation is a review of an existing problem'</td>
</tr>
<tr>
<td>problem_end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of the problem'</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>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the observation'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>episodicity_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</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>
</tr>
<tr>
<td colspan="5">Unique index name: `observation_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id_is_problem` Index Columns: (core_concept_id,is_problem)</td> </tr> <tr>
<td colspan="5">Index name: `observation_core_concept_id_result_value` Index Columns: (core_concept_id,result_value)</td> </tr> <tr>
<td colspan="5">Index name: `observation_non_core_concept_id` Index Columns: (non_core_concept_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_organization_id` Index Columns: (organization_id)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_clinical_effective_date` Index Columns: (clinical_effective_date)</td> </tr> <tr>
<td colspan="5">Index name: `ix_observation_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">observation_additional</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'same as the id column on the observation table'</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (i.e. significance)'</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (i.e. minor, significant)'</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'the JSON data itself '</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>
</tr>
<tr>
<td colspan="5">Index name: `observation_additional_value_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">organization</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the organisation'</td>
</tr>
<tr>
<td>ods_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'ODS Code of the organisation'</td>
</tr>
<tr>
<td>Name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Name of the organisation'</td>
</tr>
<tr>
<td>type_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The type of organisation'</td>
</tr>
<tr>
<td>type_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>parent_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The id of the parent organisation'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `organization_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `fki_organization_parent_organization_id` Index Columns: (parent_organization_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">organization_metadata</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'organisation ID, corresponds to same ID in the organizaton table'</td>
</tr>
<tr>
<td>publishing_software</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td> COMMENT 'software name of publishing system, i.e. SystmOne'</td>
</tr>
<tr>
<td>last_data_to_dds</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`publishing_software`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>title</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The title 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>
</tr>
<tr>
<td>last_name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The last name of the patient'</td>
</tr>
<tr>
<td>gender_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the gender 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>
</tr>
<tr>
<td>date_of_birth</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of birth of the patient'</td>
</tr>
<tr>
<td>date_of_death</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of death 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>
</tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the ethnicity 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>
</tr>
<tr>
<td>birth_year</td>
<td>smallint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>birth_month</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>birth_week</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `patient_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_person_id` Index Columns: (person_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_additional</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'same as the id column on the patient table '</td>
</tr>
<tr>
<td>property_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'IM reference (e.g. Cause of death)'</td>
</tr>
<tr>
<td>value_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'IM reference (e.g. COVID)'</td>
</tr>
<tr>
<td>json_value</td>
<td>json</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td colspan="5">Index name: `ix_patient_additional_id` Index Columns: (value_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`property_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_address</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>Id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the address'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</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>
</tr>
<tr>
<td>address_line_2</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The second line of the address'</td>
</tr>
<tr>
<td>address_line_3</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The third line of the address'</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>
</tr>
<tr>
<td>city</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The city'</td>
</tr>
<tr>
<td>postcode</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The postcode'</td>
</tr>
<tr>
<td>use_concept_id</td>
<td>int</td>
<td>NOT NULL</td>
<td> COMMENT 'use of address (e.g. home, temporary)'</td>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>NOT NULL</td>
<td> COMMENT 'The start date of this address being relevant'</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date of this address being relevant'</td>
</tr>
<tr>
<td>lsoa_2001_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the LSOA_2001 code'</td>
</tr>
<tr>
<td>lsoa_2011_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the LSOA_2011 code'</td>
</tr>
<tr>
<td>msoa_2001_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the MSOA_2001 code'</td>
</tr>
<tr>
<td>msoa_2011_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'A reference to the MSOA_2011 code'</td>
</tr>
<tr>
<td>ward_code</td>
<td>varchar(9)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The ward the address belongs to'</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>
</tr>
<tr>
<td>townsend_deprivation_index</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_address_id` Index Columns: (Id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`Id`,`patient_id`,`person_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_address_match</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>patient_address_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>uprn</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>uprn_ralf00</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>status</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>uprn_property_classification</td>
<td>varchar(45)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>latitude</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>longitude</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>uprn_xcoordinate</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>uprn_ycoordinate</td>
<td>double</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>qualifier</td>
<td>varchar(50)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_rule</td>
<td>varchar(4096)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_date</td>
<td>datetime</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_number</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_street</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_locality</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_town</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_postcode</td>
<td>varchar(10)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>abp_address_organization</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_pattern_postcode</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_pattern_street</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_pattern_number</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_pattern_building</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match_pattern_flat</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>algorithm_version</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>epoch</td>
<td>varchar(255)</td>
<td>CHARACTER SET</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Index name: `patient_address_uprn_index` Index Columns: (uprn)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_patient_address_id` Index Columns: (id,uprn)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_address_ralf</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>patient_address_id</td>
<td>bigint</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>patient_address_match_uprn_ralf00</td>
<td>varchar(255)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>salt_name</td>
<td>varchar(50)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>ralf</td>
<td>varchar(255)</td>
<td>NOT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Index name: `ux_patient_address_ralf_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_address_id` Index Columns: (patient_address_id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_address_ralf_patient_address_match_uprn_ralf_00` Index Columns: (patient_address_match_uprn_ralf00)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`,`patient_address_id`,`patient_address_match_uprn_ralf00`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_contact</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient contact'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>use_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'use of contact (e.g. mobile, home,work'</td>
</tr>
<tr>
<td>type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'type of contact (e.g. phone, email)'</td>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The start date of the contact being valid'</td>
</tr>
<tr>
<td>end_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end 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>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_contact_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_pseudo_id</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the patient pseudo id'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the person this registration status history belongs to'</td>
</tr>
<tr>
<td>salt_name</td>
<td>varchar(50)</td>
<td>NOT NULL</td>
<td> COMMENT 'The name of the salt used to create the pseudo id'</td>
</tr>
<tr>
<td>Skid</td>
<td>varchar(255)</td>
<td>NOT NULL</td>
<td> COMMENT '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)'</td>
</tr>
<tr>
<td>is_nhs_number_valid</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the nhs number is valid'</td>
</tr>
<tr>
<td>is_nhs_number_verified_by_publisher</td>
<td>tinyint(1)</td>
<td>NOT NULL</td>
<td> COMMENT 'Whether the nhs number has been verified by the publisher'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_patient_pseudo_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `patient_pseudo_id_patient` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">patient_uprn</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique individual across all organisations'</td>
</tr>
<tr>
<td>uprn</td>
<td>bigint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>qualifier</td>
<td>varchar(50)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>algorithm</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>match</td>
<td>varchar(255)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>no_address</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>invalid_address</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>missing_postcode</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>invalid_postcode</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `patient_uprn_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`patient_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">person</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the person'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>title</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The title 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>
</tr>
<tr>
<td>last_name</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The last name of the person'</td>
</tr>
<tr>
<td>gender_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>date_of_birth</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of birth of the person'</td>
</tr>
<tr>
<td>date_of_death</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date of death of the person'</td>
</tr>
<tr>
<td>current_address_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the current address of the person'</td>
</tr>
<tr>
<td>ethnic_code_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>birth_year</td>
<td>smallint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>birth_month</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td>birth_week</td>
<td>tinyint</td>
<td>DEFAULT NULL,</td>
<td>No comment yet added</td>
</tr>
<tr>
<td colspan="5">Unique index name: `person_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">practitioner</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the practitioner'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>name</td>
<td>varchar(1024)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Name of the practitioner'</td>
</tr>
<tr>
<td>role_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The code representing the role of the practitioner'</td>
</tr>
<tr>
<td>role_desc</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner'</td>
</tr>
<tr>
<td>gmc_code</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The GMC code of the practitioner'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `practitioner_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">procedure_request</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the procedure'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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 procedure was administered at'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the procedure was administered by a clinician'</td>
</tr>
<tr>
<td>date_precision_concept_id</td>
<td>int</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>
</tr>
<tr>
<td>status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the status of the procedure'</td>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the procedure'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the procedure'</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>
</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>
</tr>
<tr>
<td colspan="5">Unique index name: `procedure_request_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `procedure_request_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">referral_request</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the referral'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> 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 referral was made in'</td>
</tr>
<tr>
<td>practitioner_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The clinician the activity is recorded against'</td>
</tr>
<tr>
<td>clinical_effective_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The date the referral was made'</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>
</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>
</tr>
<tr>
<td>recipient_organization_id</td>
<td>bigint</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the organization receiving 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>
</tr>
<tr>
<td>referral_request_type_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the type of referral request'</td>
</tr>
<tr>
<td>Mode</td>
<td>varchar(50)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The mode of the referral'</td>
</tr>
<tr>
<td>outgoing_referral</td>
<td>tinyint(1)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Whether this is an outgoing 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>
</tr>
<tr>
<td>core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding of the referral'</td>
</tr>
<tr>
<td>non_core_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the clinical coding 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>
</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>
</tr>
<tr>
<td colspan="5">Unique index name: `referral_request_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">Index name: `referral_request_patient_id` Index Columns: (patient_id)</td> </tr> <tr>
<td colspan="5">Index name: `referral_request_core_concept_id` Index Columns: (core_concept_id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`person_id`,`id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">registration_status_history</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the registration status history'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</td>
</tr>
<tr>
<td>patient_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the patient this registration status history belongs to'</td>
</tr>
<tr>
<td>person_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Reference to the person this registration status history 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 status history belongs to'</td>
</tr>
<tr>
<td>registration_status_concept_id</td>
<td>int</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'Reference to the registration status'</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>
</tr>
<tr>
<td>end_date</td>
<td>datetime</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The end date for the period this registration status history was valid'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `ux_registration_status_history_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`,`patient_id`,`person_id`)</td> </tr> </table>
<h2><span class="mw-headline" id="Sortable_tables">schedule</span></h2>
<table class="mw-collapsible" border="1" style="border-collapse:collapse">
<tr>
<th scope="col">Column Name</th>
<th scope="col">Data Type</th>
<th scope="col">Constraint</th>
<th scope="col">Comment</th>
</tr>
<tr>
<td>id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Unique Id of the schedule'</td>
</tr>
<tr>
<td>organization_id</td>
<td>bigint</td>
<td>NOT NULL</td>
<td> COMMENT 'Owning organisation (i.e. publisher)'</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>
</tr>
<tr>
<td>start_date</td>
<td>date</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The start date of the schedule'</td>
</tr>
<tr>
<td>type</td>
<td>varchar(255)</td>
<td>DEFAULT NULL</td>
<td> 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>
</tr>
<tr>
<td>name</td>
<td>varchar(150)</td>
<td>DEFAULT NULL</td>
<td> COMMENT 'The name of the schedule'</td>
</tr>
<tr>
<td colspan="5">Unique index name: `schedule_id` Index Columns: (id)</td> </tr> <tr>
<td colspan="5">PRIMARY KEY CONSTRAINT(s) (`organization_id`,`id`)</td> </tr> </table>