CompassV2TestPages:v2.X: Difference between revisions
Jump to navigation
Jump to search
DavidHesketh (talk | contribs) No edit summary |
DavidHesketh (talk | contribs) No edit summary |
||
Line 13: | Line 13: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the allergy'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the encounter this allergy was record in'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date the clinical code is recorded for'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 64: | Line 58: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | <td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 70: | Line 64: | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Is this instance of the code a review of a previous encounter' | <td> COMMENT 'Is this instance of the code a review of a previous encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the allergy'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the allergy'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 88: | Line 82: | ||
<td>decimal(5,2)</td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The age the patient was at the time of this event' | <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> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `allergy_intolerance_core_concept_id` CONSTRAINT COLUMNS core_concept_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `allergy_intolerance_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `allergy_intolerance_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">appointment</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">appointment</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 103: | Line 103: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the appointment'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>schedule_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>start_date</td> | ||
<td> | <td>date</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The start date of the appointment'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>planned_duration</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 154: | Line 154: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Time between sent in and left always in minutes' | <td> COMMENT 'Time between sent in and left always in minutes'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>appointment_status_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_wait</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'How long the patient waited from being marked as arrived to being sent in'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 178: | Line 172: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'How long the patient was delayed for' | <td> COMMENT 'How long the patient was delayed for'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_time_sent_in</td> | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Date and time the patient | <td> COMMENT 'Date and time the patient was sent into the practitioner'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_time_left</td> | ||
<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>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique reference to the source of the appointment'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 196: | Line 196: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the appointment was cancelled' | <td> COMMENT 'The date the appointment was cancelled'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `appointment_patient_id` CONSTRAINT COLUMNS patient_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `appointment_patient_id` CONSTRAINT COLUMNS (patient_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">appointment_additional</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">appointment_additional</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 209: | Line 209: | ||
<th scope="col">Comment</th> | <th scope="col">Comment</th> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td>id</td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'same as the id column on the patient table ' | <td> COMMENT 'same as the id column on the patient table '</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 231: | Line 219: | ||
<td>int</td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'IM reference (e.g. appointment type)' | <td> COMMENT 'IM reference (e.g. appointment type)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 237: | Line 225: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'IM reference (e.g. Appointment Type)',</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> | ||
<tr> | <tr> | ||
<td colspan="5">CONSTRAINT | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),</td> </tr> <tr> | ||
<td colspan="5"> | <td colspan="5">CONSTRAINT NAME `appointment_additional_value_id` CONSTRAINT COLUMNS (value_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">concept</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">concept</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 251: | Line 251: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>dbid</td> | ||
<td> | <td>int</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 260: | Line 266: | ||
<td>varchar(150)</td> | <td>varchar(150)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique concept identifier' | <td> COMMENT 'Unique concept identifier'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Draft</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>Description</td> | ||
<td> | <td>varchar(400)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Full name (or term for ontological concepts)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 278: | Line 290: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)' | <td> COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Code</td> | ||
<td>varchar(40)</td> | |||
<td>varchar( | |||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The code (non-unique unless coupled with a scheme)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 296: | Line 302: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept' | <td> DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 302: | Line 308: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The timestamp of the last update to the concept' | <td> COMMENT 'The timestamp of the last update to the concept'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `dbid`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `dbid`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `ix_scheme_code` CONSTRAINT COLUMNS (Scheme,Code),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `ix_code` CONSTRAINT COLUMNS Code),</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `ix_code` CONSTRAINT COLUMNS (Code),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `ix_dbid_code` CONSTRAINT COLUMNS (dbid,Code)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">concept_map</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">concept_map</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 327: | Line 327: | ||
<td>int</td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept' | <td> COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core</td> | ||
<td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>updated</td> | ||
<td> | <td>datetime</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'the | <td> COMMENT 'Timestamp the map was last updated/added'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 355: | Line 355: | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `ix_legacy_core` CONSTRAINT COLUMNS legacy,core)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `ix_legacy_core` CONSTRAINT COLUMNS (legacy,core)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">diagnostic_order</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">diagnostic_order</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 365: | Line 365: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the diagnostic order'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The | <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> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the encounter the observation was recorded at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td> | <td>date</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date the diagnostic order was identified by a clinician'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>result_value</td> | ||
<td> | <td>double</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The value of the result of the observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>result_value_units</td> | ||
<td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The units of the result of the observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 440: | Line 428: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date of the result' | <td> COMMENT 'The date of the result'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>result_text</td> | ||
<td> | <td>text</td> | ||
<td> | <td>COMMENT 'Any</td> | ||
<td> | <td> text associated with the result'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>result_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the clinical coding of the | <td> COMMENT 'Reference to the clinical coding of the result'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>is_problem</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether the observation is marked as a problem'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 470: | Line 452: | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Whether the observation is a review of an existing problem' | <td> COMMENT 'Whether the observation is a review of an existing problem'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>problem_end_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The end date of the problem'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 482: | Line 464: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</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> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 494: | Line 482: | ||
<td>decimal(5,2)</td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The age of the patient at the time of the observation',</td> | <td> COMMENT 'The 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> | ||
<tr> | <tr> | ||
<td> | <td>is_primary</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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></td> | |||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `diagnostic_order_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `diagnostic_order_core_concept_id_is_problem` CONSTRAINT COLUMNS core_concept_id,is_problem),</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `diagnostic_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `diagnostic_order_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `diagnostic_order_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `diagnostic_order_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_diagnostic_order_organization_id` CONSTRAINT COLUMNS (organization_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_diagnostic_order_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_diagnostic_order_person_id` CONSTRAINT COLUMNS (person_id)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">encounter</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">encounter</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 521: | Line 521: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> | <td> COMMENT 'Unique Id of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 536: | Line 542: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 542: | Line 548: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The clinician the activity is recorded against' | <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> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the | <td> COMMENT 'The date the clinical code is recorded for'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 554: | Line 566: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the precision of the date of the encounter' | <td> COMMENT 'Reference to the precision of the date of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 560: | Line 572: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Is this instance of the code a review of a previous encounter' | <td> COMMENT 'Is this instance of the code a review of a previous encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>service_provider_organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the service provider organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the type of encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the type of encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>age_at_event</td> | ||
<td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The age the patient was when this encounter took place'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Type</td> | ||
<td>text</td> | <td>text</td> | ||
<td>COMMENT 'Unused',</td> | <td>COMMENT 'Unused',</td> | ||
Line 593: | Line 605: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>sub_type</td> | ||
<td> | <td>text</td> | ||
<td>COMMENT 'Unused',</td> | |||
<td> COMMENT ' | <td></td> | ||
<td> | |||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 608: | Line 614: | ||
<td>varchar(40)</td> | <td>varchar(40)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The admission method of the encounter' | <td> COMMENT 'The admission method of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 626: | Line 620: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The end date of the encounter' | <td> COMMENT 'The end date of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>institution_location_id</td> | ||
<td>text</td> | <td>text</td> | ||
<td>COMMENT ' | <td>COMMENT 'Reference</td> | ||
<td></td> | <td> 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> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `encounter_patient_id` CONSTRAINT COLUMNS patient_id),</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `encounter_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `fki_encounter_appointment_id` CONSTRAINT COLUMNS (appointment_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `fki_encounter_patient_id_organization_id` CONSTRAINT COLUMNS (patient_id,organization_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `encounter_core_concept_id_clinical_effective_date` CONSTRAINT COLUMNS (core_concept_id,clinical_effective_date)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">encounter_additional</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">encounter_additional</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 649: | Line 649: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'same as the id column on the encounter table'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>property_id</td> | ||
<td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'IM concept id reference (i.e. Admission method)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 670: | Line 664: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'IM concept id reference (i.e. Emergency admission)',</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> | ||
<tr> | <tr> | ||
<td> | <td>text_value</td> | ||
<td> | <td>varchar(255)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">CONSTRAINT | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),</td> </tr> <tr> | ||
<td colspan="5"> | <td colspan="5">CONSTRAINT NAME `encounter_additional_value_id` CONSTRAINT COLUMNS (value_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">encounter_event</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">encounter_event</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 690: | Line 690: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <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>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The patient this event belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td></td> | <td> COMMENT 'The person this event belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the parent encounter record'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>appointment_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the appointment this encounter took part on'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 729: | Line 735: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the encounter took place' | <td> COMMENT 'The date the encounter took place'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the precision of the date of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>episode_of_care_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the episode of care this encounter belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>service_provider_organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the service provider organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the type of encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the type of encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 765: | Line 771: | ||
<td>decimal(5,2)</td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The age of the patient at the time of the encounter' | <td> COMMENT 'The age of the patient at the time of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 774: | Line 780: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>sub_type</td> | ||
<td> | <td>text</td> | ||
<td> | <td>COMMENT 'Unused',</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>admission_method</td> | ||
<td> | <td>varchar(40)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The admission method of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td>end_date</td> | |||
<td>end_date</td> | |||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The end date of the encounter' | <td> COMMENT 'The end date of the encounter'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>institution_location_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the institution the encounter took place at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_recorded</td> | ||
<td> | <td>datetime</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date the encounter was recorded'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 813: | Line 813: | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Whether the encounter is finished' | <td> COMMENT 'Whether the encounter is finished'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 825: | Line 825: | ||
<th scope="col">Comment</th> | <th scope="col">Comment</th> | ||
</tr> | </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> | <tr> | ||
<td>patient_id</td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | <td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>registration_type_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the registration status of the patient' | <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> | ||
<tr> | <tr> | ||
Line 847: | Line 865: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the registration was started for this episode of care' | <td> COMMENT 'The date the registration was started for this episode of care'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 859: | Line 871: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the registration was ended for this episode of care' | <td> COMMENT 'The date the registration was ended for this episode of care'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>usual_gp_practitioner_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the usual GP for this episode of care'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `episode_of_care_patient_id` CONSTRAINT COLUMNS patient_id),</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `episode_of_care_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `episode_of_care_registration_type_concept_id` CONSTRAINT COLUMNS (registration_type_concept_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `episode_of_care_date_registered` CONSTRAINT COLUMNS (date_registered),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `episode_of_care_date_registered_end` CONSTRAINT COLUMNS (date_registered_end),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `episode_of_care_person_id` CONSTRAINT COLUMNS (person_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `episode_of_care_organization_id` CONSTRAINT COLUMNS (organization_id)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">event_log</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">event_log</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 896: | Line 896: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>dt_change</td> | ||
<td> | <td>datetime(3)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'date time the change was made to this DB'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>change_type</td> | ||
<td> | <td>tinyint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'type of transaction 0=insert, 1=update, 2=delete'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>table_id</td> | ||
<td>tinyint</td> | <td>tinyint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'identifier of the table changed'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 929: | Line 929: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> | <td> COMMENT 'Unique Id of the flag'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 956: | Line 956: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the flag was entered onto the patients record' | <td> COMMENT 'The date the flag was entered onto the patients record'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>is_active</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether the flag is active or not'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>flag_text</td> | ||
<td> | <td>text</td> | ||
<td> | <td>COMMENT 'This</td> | ||
<td> | <td> is a warning set by the publisher regarding he patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `flag_patient_id` CONSTRAINT COLUMNS patient_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `flag_patient_id` CONSTRAINT COLUMNS (patient_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">location</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">location</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 988: | Line 988: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <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>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>type_code</td> | ||
<td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The type of location'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>type_desc</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Textual description of the type of location eg GP Practice'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,009: | Line 1,015: | ||
<td>varchar(10)</td> | <td>varchar(10)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The postcode of the location' | <td> COMMENT 'The postcode of the location'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>managing_organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the managing organisation of the location'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `fk_location_managing_organisation_id` CONSTRAINT COLUMNS managing_organization_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `fk_location_managing_organisation_id` CONSTRAINT COLUMNS (managing_organization_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">medication_order</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">medication_order</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,035: | Line 1,035: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the medication order'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 1,080: | Line 1,056: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the encounter the medication order was issued in'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td> | <td>date</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date the medication order was issued'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,104: | Line 1,080: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | <td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>dose</td> | ||
<td> | <td>varchar(1000)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Textual description of the dose'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,116: | Line 1,092: | ||
<td>double</td> | <td>double</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The value of the medication that was prescribed eg 50' | <td> 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> | ||
<tr> | <tr> | ||
<td> | <td>duration_days</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'How many days the medication is prescribed for'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>estimated_cost</td> | ||
<td> | <td>double</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The estimated cost of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>medication_statement_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to | <td> COMMENT 'Reference to the medication statement. A medication statement can have many medication orders'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td>bnf_reference</td> | ||
<td colspan="5">CONSTRAINT NAME `medication_order_patient_id` CONSTRAINT COLUMNS patient_id),</td> </tr> <tr> | <td>varchar(6)</td> | ||
<td colspan="5">CONSTRAINT NAME `medication_order_core_concept_id` CONSTRAINT COLUMNS core_concept_id)</td> </tr> </table> | <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>COMMENT 'The</td> | |||
<td> issue method of the medication eg hand written'</td> | |||
</tr> | |||
<tr> | |||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `medication_order_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `medication_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">medication_statement</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">medication_statement</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,161: | Line 1,161: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 1,170: | Line 1,182: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The clinician the activity is recorded against' | <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> | ||
<tr> | <tr> | ||
Line 1,182: | Line 1,200: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the medication was clinical relevant' | <td> COMMENT 'The date the medication was clinical relevant'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>cancellation_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date the medication was cancelled'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>dose</td> | ||
<td> | <td>varchar(1000)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Texual description of the dose of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>quantity_value</td> | ||
<td> | <td>double</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The value of the medication that was prescribed eg 50'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,212: | Line 1,236: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The unit of the medication that was prescribed eg tablets' | <td> COMMENT 'The unit of the medication that was prescribed eg tablets'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>authorisation_type_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the authorisation type'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> | <td> COMMENT 'Reference to the clinical coding of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the clinical coding of the medication' | <td> COMMENT 'Reference to the clinical coding of the medication'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>bnf_reference</td> | ||
<td>varchar( | <td>varchar(6)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'A reference to the drug in the BNF dictionary'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>age_at_event</td> | ||
<td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The age the patient was at the time of this event'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Issue_method</td> | ||
<td> | <td>text</td> | ||
<td> | <td>COMMENT 'The</td> | ||
<td> | <td> issue method of the medication eg hand written'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_recorded</td> | ||
<td> | <td>datetime</td> | ||
<td>DEFAULT NULL | <td>DEFAULT NULL,</td> | ||
<td></td> | |||
<td> | |||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `medication_statement_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `medication_statement_dmd_id` CONSTRAINT COLUMNS (patient_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">observation</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">observation</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,293: | Line 1,293: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td> | |||
<td> COMMENT 'The | |||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,332: | Line 1,314: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the encounter the observation was recorded at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date | <td> COMMENT 'The date the observation was identified by a clinician'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>result_value</td> | ||
<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>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date of the result'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>result_text</td> | ||
<td> | <td>text</td> | ||
<td> | <td>COMMENT 'Any</td> | ||
<td> | <td> text associated with the result'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>result_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 1,386: | Line 1,380: | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Whether the observation is a review of an existing problem' | <td> COMMENT 'Whether the observation is a review of an existing problem'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>problem_end_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The end date of the problem'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,398: | Line 1,392: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</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> COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,416: | Line 1,410: | ||
<td>decimal(5,2)</td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The age of the patient at the time of the observation',</td> | <td> COMMENT 'The 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> | ||
<tr> | <tr> | ||
<td> | <td>is_primary</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether the observation is a primary observation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_recorded</td> | ||
<td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the observation was | <td> COMMENT 'The date the observation was recorded in the system'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `observation_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `observation_core_concept_id` CONSTRAINT COLUMNS core_concept_id),</td> </tr> <tr> | <td colspan="5">CONSTRAINT NAME `observation_core_concept_id` CONSTRAINT COLUMNS (core_concept_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `observation_non_core_concept_id` CONSTRAINT COLUMNS non_core_concept_id),</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `observation_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `observation_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `observation_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_observation_organization_id` CONSTRAINT COLUMNS (organization_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_observation_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `ix_observation_person_id` CONSTRAINT COLUMNS (person_id)</td> </tr> </table> | |||
<h2><span class="mw-headline" id="Sortable_tables">observation_additional</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">observation_additional</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,448: | Line 1,448: | ||
<th scope="col">Comment</th> | <th scope="col">Comment</th> | ||
</tr> | </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> | <tr> | ||
<td>property_id</td> | <td>property_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'IM reference (i.e. significance)' | <td> COMMENT 'IM reference (i.e. significance)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>value_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'IM reference (i.e. minor, significant)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,470: | Line 1,470: | ||
<td>json</td> | <td>json</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'the JSON data itself ' | <td> COMMENT 'the JSON data itself '</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>text_value</td> | ||
<td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">CONSTRAINT | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),</td> </tr> <tr> | ||
<td colspan="5"> | <td colspan="5">CONSTRAINT NAME `observation_additional_value_id` CONSTRAINT COLUMNS (value_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">organization</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">organization</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,493: | Line 1,493: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique Id of the organisation' | <td> COMMENT 'Unique Id of the organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>ods_code</td> | ||
<td>varchar( | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'ODS Code of the organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Name</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Name of the organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>type_code</td> | ||
<td>varchar(50)</td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The type of organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>type_desc</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Textual description of the type of organisation eg GP Practice'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,523: | Line 1,523: | ||
<td>varchar(10)</td> | <td>varchar(10)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The postcode of the organisation' | <td> COMMENT 'The postcode of the organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,529: | Line 1,529: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The id of the parent organisation' | <td> COMMENT 'The id of the parent organisation'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `fki_organization_parent_organization_id` CONSTRAINT COLUMNS parent_organization_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `fki_organization_parent_organization_id` CONSTRAINT COLUMNS (parent_organization_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">organization_metadata</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">organization_metadata</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,543: | Line 1,543: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'organisation ID, corresponds to same ID in the organizaton table'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,552: | Line 1,552: | ||
<td>varchar(50)</td> | <td>varchar(50)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'software name of publishing system, i.e. SystmOne' | <td> COMMENT 'software name of publishing system, i.e. SystmOne'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>last_data_to_dds</td> | ||
<td> | <td>datetime</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'date time data was last sent to DDS'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,564: | Line 1,564: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'cutoff date time of the last extract from the publishing system' | <td> COMMENT 'cutoff date time of the last extract from the publishing system'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,577: | Line 1,577: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,598: | Line 1,598: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The title of the patient' | <td> COMMENT 'The title of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>first_names</td> | ||
<td> | <td>varchar(255)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The first names of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>last_name</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The last name of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,616: | Line 1,616: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the gender of the patient' | <td> COMMENT 'Reference to the gender of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>nhs_number</td> | ||
<td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL | <td>DEFAULT NULL</td> | ||
<td></td> | <td> COMMENT 'The NHS number of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_of_birth</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date of birth of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_of_death</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date of death of the patient'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>current_address_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>registered_practice_organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the organisation the patient is registered at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,655: | Line 1,661: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>birth_month</td> | ||
<td> | <td>tinyint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>birth_week</td> | ||
<td>tinyint</td> | <td>tinyint</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `patient_person_id` CONSTRAINT COLUMNS person_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `patient_person_id` CONSTRAINT COLUMNS (person_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">patient_additional</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">patient_additional</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,683: | Line 1,683: | ||
<th scope="col">Comment</th> | <th scope="col">Comment</th> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td>id</td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'same as the id column on the patient table ' | <td> COMMENT 'same as the id column on the patient table '</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,699: | Line 1,693: | ||
<td>int</td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'IM reference (e.g. Cause of death)' | <td> COMMENT 'IM reference (e.g. Cause of death)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,705: | Line 1,699: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'IM reference (e.g. COVID)',</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> | ||
<tr> | <tr> | ||
Line 1,711: | Line 1,711: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' | <td> COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`property_id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `ix_patient_additional_id` CONSTRAINT COLUMNS value_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `ix_patient_additional_id` CONSTRAINT COLUMNS (value_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">patient_address</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">patient_address</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,725: | Line 1,725: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 1,734: | Line 1,740: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | <td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <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>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The third line of the address'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,752: | Line 1,770: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The fourth line of the address' | <td> COMMENT 'The fourth line of the address'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>city</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The city'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,764: | Line 1,782: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The postcode' | <td> COMMENT 'The postcode'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>use_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'use of address (e.g. home, temporary)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>start_date</td> | ||
<td> | <td>date</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The start date of this address being relevant'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>end_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The end date of this address being relevant'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>lsoa_2001_code</td> | ||
<td> | <td>varchar(9)</td> | ||
<td>DEFAULT NULL | <td>DEFAULT NULL</td> | ||
<td></td> | <td> COMMENT 'A reference to the LSOA_2001 code'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>lsoa_2011_code</td> | ||
<td>varchar(9)</td> | <td>varchar(9)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'A reference to the | <td> COMMENT 'A reference to the LSOA_2011 code'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>msoa_2001_code</td> | ||
<td> | <td>varchar(9)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'A reference to the MSOA_2001 code'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>msoa_2011_code</td> | ||
<td> | <td>varchar(9)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'A reference to the MSOA_2011 code'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 1,812: | Line 1,830: | ||
<td>varchar(9)</td> | <td>varchar(9)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The ward the address belongs to' | <td> COMMENT 'The ward the address belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>local_authority_code</td> | ||
<td>varchar(9)</td> | |||
<td>varchar( | |||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The local authority the address belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>townsend_deprivation_index</td> | ||
<td> | <td>double</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`Id`,`patient_id`,`person_id`),</td> </tr> </table> | |||
<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> | <h2><span class="mw-headline" id="Sortable_tables">patient_address_match</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 1,855: | Line 1,855: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> AUTO_INCREMENT</td> | |||
</tr> | |||
<tr> | |||
<td>patient_address_id</td> | |||
<td>bigint</td> | |||
<td>NOT NULL,</td> | |||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE | <td> utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn_ralf00</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>status</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn_property_classification</td> | ||
<td>varchar( | <td>varchar(45)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>latitude</td> | ||
<td>double</td> | <td>double</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
Line 1,891: | Line 1,897: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>longitude</td> | ||
<td> | <td>double</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn_xcoordinate</td> | ||
<td> | <td>double</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn_ycoordinate</td> | ||
<td> | <td>double</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
Line 1,912: | Line 1,918: | ||
<td>varchar(50)</td> | <td>varchar(50)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_rule</td> | ||
<td>varchar( | <td>varchar(4096)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_date</td> | ||
<td> | <td>datetime</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_number</td> | ||
<td> | <td>varchar(255)</td> | ||
<td> | <td>CHARACTER SET</td> | ||
<td></td> | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_street</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_locality</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_town</td> | ||
<td> | <td>varchar(255)</td> | ||
<td> | <td>CHARACTER SET</td> | ||
<td></td> | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_postcode</td> | ||
<td>varchar( | <td>varchar(10)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>abp_address_organization</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_pattern_postcode</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_pattern_street</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_pattern_number</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td> | <td>CHARACTER SET</td> | ||
<td></td> | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_pattern_building</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match_pattern_flat</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>algorithm_version</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | <td> utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>epoch</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>CHARACTER SET</td> | <td>CHARACTER SET</td> | ||
<td> utf8mb3 COLLATE | <td> utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `patient_address_uprn_index` CONSTRAINT COLUMNS (uprn),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `patient_address_patient_address_id` CONSTRAINT COLUMNS (id,uprn)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">patient_address_ralf</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">patient_address_ralf</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 2,022: | Line 2,022: | ||
<th scope="col">Comment</th> | <th scope="col">Comment</th> | ||
</tr> | </tr> | ||
<tr> | |||
<td>id</td> | |||
<td>bigint</td> | |||
<td>NOT NULL,</td> | |||
<td></td> | |||
</tr> | |||
<tr> | <tr> | ||
<td>organization_id</td> | <td>organization_id</td> | ||
Line 2,035: | Line 2,041: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL,</td> | <td>NOT NULL,</td> | ||
Line 2,041: | Line 2,047: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_address_id</td> | ||
<td> | <td>bigint</td> | ||
<td>NOT NULL,</td> | <td>NOT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_address_match_uprn_ralf00</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>NOT NULL,</td> | <td>NOT NULL,</td> | ||
Line 2,053: | Line 2,059: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>salt_name</td> | ||
<td> | <td>varchar(50)</td> | ||
<td>NOT NULL,</td> | <td>NOT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>ralf</td> | ||
<td>varchar(255)</td> | |||
<td>varchar( | |||
<td>NOT NULL,</td> | <td>NOT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">CONSTRAINT | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `id`,`patient_address_id`,`patient_address_match_uprn_ralf00`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `ux_patient_address_ralf_id` CONSTRAINT COLUMNS id),</td> </tr> <tr> | <td colspan="5">CONSTRAINT NAME `ux_patient_address_ralf_id` CONSTRAINT COLUMNS (id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `patient_address_ralf_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME ` | <td colspan="5">CONSTRAINT NAME `patient_address_ralf_patient_address_id` CONSTRAINT COLUMNS (patient_address_id),</td> </tr> <tr> | ||
<td colspan="5"> | <td colspan="5">CONSTRAINT NAME `patient_address_ralf_patient_address_match_uprn_ralf_00` CONSTRAINT COLUMNS (patient_address_match_uprn_ralf00)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">patient_contact</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">patient_contact</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 2,085: | Line 2,085: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the patient contact'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,106: | Line 2,094: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Owning organisation (i.e. publisher)' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,124: | Line 2,112: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'use of contact (e.g. mobile, home,work' | <td> COMMENT 'use of contact (e.g. mobile, home,work'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,130: | Line 2,118: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'type of contact (e.g. phone, email)' | <td> COMMENT 'type of contact (e.g. phone, email)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,136: | Line 2,124: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The start date of the contact being valid',</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> | ||
<tr> | <tr> | ||
Line 2,149: | Line 2,149: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the patient pseudo id'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the patient this registration status history belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 2,176: | Line 2,182: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)' | <td> COMMENT '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>is_nhs_number_valid</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether the nhs number is valid'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>is_nhs_number_verified_by_publisher</td> | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Whether the nhs number | <td> COMMENT 'Whether the nhs number has been verified by the publisher'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `patient_pseudo_id_patient` CONSTRAINT COLUMNS patient_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `patient_pseudo_id_patient` CONSTRAINT COLUMNS (patient_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">patient_uprn</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">patient_uprn</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 2,211: | Line 2,211: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | <td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td></td> | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,223: | Line 2,223: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <td> COMMENT 'Unique individual across all organisations'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>uprn</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>qualifier</td> | ||
<td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>algorithm</td> | ||
<td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>match</td> | ||
<td>varchar( | <td>varchar(255)</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
<td></td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>no_address</td> | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
Line 2,256: | Line 2,256: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>invalid_address</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>missing_postcode</td> | ||
<td>tinyint(1)</td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
Line 2,284: | Line 2,284: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 2,293: | Line 2,299: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The title of the person' | <td> COMMENT 'The title of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>first_names</td> | ||
<td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The first names of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>last_name</td> | ||
<td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The last name of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>gender_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the gender of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,323: | Line 2,323: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The NHS number of the person' | <td> COMMENT 'The NHS number of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_of_birth</td> | ||
<td> | <td>date</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date of birth of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_of_death</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The date of death of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>current_address_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td></td> | <td> COMMENT 'Reference to the current address of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,347: | Line 2,347: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the ethnicity of the person' | <td> COMMENT 'Reference to the ethnicity of the person'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>registered_practice_organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the organisation the person is registered at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>birth_year</td> | ||
<td> | <td>smallint</td> | ||
<td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>birth_month</td> | ||
<td> | <td>tinyint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL,</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>birth_week</td> | ||
<td>tinyint</td> | <td>tinyint</td> | ||
<td>DEFAULT NULL,</td> | <td>DEFAULT NULL,</td> | ||
Line 2,387: | Line 2,387: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique Id of the practitioner' | <td> COMMENT 'Unique Id of the practitioner'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,399: | Line 2,393: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Owning organisation (i.e. publisher)' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>name</td> | ||
<td>varchar( | <td>varchar(1024)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Name of the practitioner'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,411: | Line 2,405: | ||
<td>varchar(50)</td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The code representing the role of the practitioner' | <td> COMMENT 'The code representing the role of the practitioner'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,417: | Line 2,411: | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner' | <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> | ||
<tr> | <tr> | ||
Line 2,430: | Line 2,430: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the procedure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 2,451: | Line 2,451: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique individual across all organisations' | <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> | ||
<tr> | <tr> | ||
Line 2,457: | Line 2,463: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The clinician the activity is recorded against' | <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> | ||
<tr> | <tr> | ||
Line 2,463: | Line 2,475: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | <td> COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>status_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the status of the procedure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the clinical coding of the procedure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,481: | Line 2,493: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the clinical coding of the procedure' | <td> COMMENT 'Reference to the clinical coding of the procedure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,493: | Line 2,499: | ||
<td>decimal(5,2)</td> | <td>decimal(5,2)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The age of the patient at the time of the procedure' | <td> COMMENT 'The age of the patient at the time of the procedure'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,499: | Line 2,505: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The date the procedure was recorded in the source system' | <td> COMMENT 'The date the procedure was recorded in the source system'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | ||
<td colspan="5">CONSTRAINT NAME `procedure_request_patient_id` CONSTRAINT COLUMNS patient_id)</td> </tr> </table> | <td colspan="5">CONSTRAINT NAME `procedure_request_patient_id` CONSTRAINT COLUMNS (patient_id)</td> </tr> </table> | ||
<h2><span class="mw-headline" id="Sortable_tables">referral_request</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">referral_request</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 2,519: | Line 2,519: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,534: | Line 2,534: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | <td> 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> | ||
<tr> | <tr> | ||
<td> | <td>encounter_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the encounter the referral was made in'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>practitioner_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The clinician the activity is recorded against'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>clinical_effective_date</td> | ||
<td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date the referral was made'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_precision_concept_id</td> | ||
<td> | <td>smallint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
<td> | <td>requester_organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the organisation that made the refereral request'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>recipient_organization_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the | <td> COMMENT 'Reference to the organization receiving the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>referral_request_priority_concept_id</td> | ||
<td> | <td>smallint</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the priority of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>referral_request_type_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the type of referral request'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>Mode</td> | ||
<td> | <td>varchar(50)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The mode of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>outgoing_referral</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether this is an outgoing referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>is_review</td> | ||
<td> | <td>tinyint(1)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Whether this referral is a review'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>non_core_concept_id</td> | ||
<td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the clinical coding of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>age_at_event</td> | ||
<td> | <td>decimal(5,2)</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The age of the patient at the time of the referral'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>date_recorded</td> | ||
<td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The date the referral request was added to the source system'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `referral_request_patient_id` CONSTRAINT COLUMNS (patient_id),</td> </tr> <tr> | |||
<td colspan="5">CONSTRAINT NAME `referral_request_core_concept_id` CONSTRAINT COLUMNS (core_concept_id)</td> </tr> </table> | |||
<td colspan="5">CONSTRAINT | |||
<td colspan="5">CONSTRAINT NAME ` | |||
<td colspan="5"> | |||
<h2><span class="mw-headline" id="Sortable_tables">registration_status_history</span></h2> | <h2><span class="mw-headline" id="Sortable_tables">registration_status_history</span></h2> | ||
<table class="sortable" border="1" style="border-collapse:collapse"> | <table class="sortable" border="1" style="border-collapse:collapse"> | ||
Line 2,645: | Line 2,645: | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Unique Id of the registration status history'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>organization_id</td> | ||
<td> | <td>bigint</td> | ||
<td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Owning organisation (i.e. publisher)'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>patient_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Reference to the patient this registration status history belongs to'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>person_id</td> | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT ' | <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> | ||
<tr> | <tr> | ||
Line 2,672: | Line 2,678: | ||
<td>int</td> | <td>int</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the registration status' | <td> COMMENT 'Reference to the registration status'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>start_date</td> | ||
<td> | <td>datetime</td> | ||
<td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The start date for the period this registration status history was valid'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,684: | Line 2,690: | ||
<td>datetime</td> | <td>datetime</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The end date for the period this registration status history was valid' | <td> COMMENT 'The end date for the period this registration status history was valid'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,706: | Line 2,706: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>NOT NULL</td> | <td>NOT NULL</td> | ||
<td> COMMENT 'Unique Id of the schedule' | <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> | ||
<tr> | <tr> | ||
Line 2,712: | Line 2,718: | ||
<td>bigint</td> | <td>bigint</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'Reference to the practitioner who owns the schedule' | <td> COMMENT 'Reference to the practitioner who owns the schedule'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
Line 2,718: | Line 2,724: | ||
<td>date</td> | <td>date</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The start date of the schedule' | <td> COMMENT 'The start date of the schedule'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>type</td> | ||
<td>varchar(255)</td> | |||
<td>varchar( | |||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT 'The | <td> COMMENT 'The type of schedule eg Timed Appointments'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>location</td> | ||
<td>varchar(255)</td> | <td>varchar(255)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'Textual description of the location the schedule was held at'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td> | <td>name</td> | ||
<td>varchar( | <td>varchar(150)</td> | ||
<td>DEFAULT NULL</td> | <td>DEFAULT NULL</td> | ||
<td> COMMENT ' | <td> COMMENT 'The name of the schedule'</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`),</td> </tr> </table> | <td colspan="5">PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`),</td> </tr> </table> |
Revision as of 15:04, 23 November 2022
Remote Subscriber Database (RSD) Schema (Compass 2) Version: v2.1.2
allergy_intolerance
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the allergy' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter this allergy was record in' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the clinical code is recorded for' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
is_review | tinyint(1) | NOT NULL | COMMENT 'Is this instance of the code a review of a previous encounter' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the allergy' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the allergy' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event' | |
date_recorded | datetime | NOT NULL | COMMENT 'The date the allergy was recorded' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `allergy_intolerance_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `allergy_intolerance_core_concept_id` CONSTRAINT COLUMNS (core_concept_id) |
appointment
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the appointment' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
schedule_id | bigint | DEFAULT NULL | COMMENT 'The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule' | |
start_date | date | DEFAULT NULL | COMMENT 'The start date of the appointment' | |
planned_duration | int | DEFAULT NULL | COMMENT 'The time allocated for the appointment, not necessarily the actual duration always in minutes' | |
actual_duration | int | DEFAULT NULL | COMMENT 'Time between sent in and left always in minutes' | |
appointment_status_concept_id | int | DEFAULT NULL | COMMENT 'The status of the appointment e.g. arrived/sent in/left/DNA' | |
patient_wait | int | DEFAULT NULL | COMMENT 'How long the patient waited from being marked as arrived to being sent in' | |
patient_delay | int | DEFAULT NULL | COMMENT 'How long the patient was delayed for' | |
date_time_sent_in | datetime | DEFAULT NULL | COMMENT 'Date and time the patient was sent into the practitioner' | |
date_time_left | datetime | DEFAULT NULL | COMMENT 'Date and time the patient left the practitioner' | |
source_id | varchar(36) | DEFAULT NULL | COMMENT 'Unique reference to the source of the appointment' | |
cancelled_date | datetime | DEFAULT NULL | COMMENT 'The date the appointment was cancelled' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `appointment_patient_id` CONSTRAINT COLUMNS (patient_id) |
appointment_additional
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'same as the id column on the patient table ' | |
property_id | int | NOT NULL | COMMENT 'IM reference (e.g. appointment type)' | |
value_id | int | DEFAULT NULL | COMMENT 'IM reference (e.g. Appointment Type)' | |
json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON' | |
text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' | |
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
CONSTRAINT NAME `appointment_additional_value_id` CONSTRAINT COLUMNS (value_id) |
concept
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
dbid | int | NOT NULL | COMMENT 'Unique Id of the concept' | |
Document | int | NOT NULL | COMMENT 'Concept grouping construct, deprecated' | |
Id | varchar(150) | NOT NULL | COMMENT 'Unique concept identifier' | |
Draft | tinyint(1) | NOT NULL | COMMENT 'Whether its draft/autocreated or confirmed as a "proper" concept' | |
Name | varchar(255) | DEFAULT NULL | COMMENT 'Short name' | |
Description | varchar(400) | DEFAULT NULL | COMMENT 'Full name (or term for ontological concepts)' | |
Scheme | bigint | DEFAULT NULL | COMMENT 'The coding scheme for the code (Read, CTV3, SNOMED etc)' | |
Code | varchar(40) | DEFAULT NULL | COMMENT 'The code (non-unique unless coupled with a scheme)' | |
use_count | bigint | NOT NULL | DEFAULT '0' COMMENT 'Rough indicator of number of occurences of the concept' | |
updated | datetime | NOT NULL | COMMENT 'The timestamp of the last update to the concept' | |
PRIMARY KEY CONSTRAINT(s) `dbid`), | ||||
CONSTRAINT NAME `ix_scheme_code` CONSTRAINT COLUMNS (Scheme,Code), | ||||
CONSTRAINT NAME `ix_code` CONSTRAINT COLUMNS (Code), | ||||
CONSTRAINT NAME `ix_dbid_code` CONSTRAINT COLUMNS (dbid,Code) |
concept_map
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
legacy | int | NOT NULL | COMMENT 'the legacy (read 2, ctv3, icd10, opcs4, emis local, etc) concept' | |
core | int | NOT NULL | COMMENT 'the core (snomed, discovery) concept that the legacy concept maps to' | |
updated | datetime | NOT NULL | COMMENT 'Timestamp the map was last updated/added' | |
id | int | NOT NULL, | ||
deleted | tinyint(1) | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `id`), | ||||
CONSTRAINT NAME `ix_legacy_core` CONSTRAINT COLUMNS (legacy,core) |
diagnostic_order
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the diagnostic order' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the observation was recorded at' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the diagnostic order was identified by a clinician' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
result_value | double | DEFAULT NULL | COMMENT 'The value of the result of the observation' | |
result_value_units | varchar(50) | DEFAULT NULL | COMMENT 'The units of the result of the observation' | |
result_date | date | DEFAULT NULL | COMMENT 'The date of the result' | |
result_text | text | COMMENT 'Any | text associated with the result' | |
result_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the result' | |
is_problem | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is marked as a problem' | |
is_review | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is a review of an existing problem' | |
problem_end_date | date | DEFAULT NULL | COMMENT 'The end date of the problem' | |
parent_observation_id | bigint | DEFAULT NULL | COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the observation' | |
episodicity_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the episodicity of the problem eg First, review, flare' | |
is_primary | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the diagnostic order is a primary order' | |
date_recorded | datetime | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `diagnostic_order_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `diagnostic_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id), | ||||
CONSTRAINT NAME `diagnostic_order_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem), | ||||
CONSTRAINT NAME `diagnostic_order_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value), | ||||
CONSTRAINT NAME `diagnostic_order_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id), | ||||
CONSTRAINT NAME `ix_diagnostic_order_organization_id` CONSTRAINT COLUMNS (organization_id), | ||||
CONSTRAINT NAME `ix_diagnostic_order_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date), | ||||
CONSTRAINT NAME `ix_diagnostic_order_person_id` CONSTRAINT COLUMNS (person_id) |
encounter
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the encounter' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
appointment_id | bigint | DEFAULT NULL | COMMENT 'Reference to the appointment this encounter took part on' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the clinical code is recorded for' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the precision of the date of the encounter' | |
episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Is this instance of the code a review of a previous encounter' | |
service_provider_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the service provider organisation' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was when this encounter took place' | |
Type | text | COMMENT 'Unused', | ||
sub_type | text | COMMENT 'Unused', | ||
admission_method | varchar(40) | DEFAULT NULL | COMMENT 'The admission method of the encounter' | |
end_date | date | DEFAULT NULL | COMMENT 'The end date of the encounter' | |
institution_location_id | text | COMMENT 'Reference | to the institution the encounter took place at' | |
date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the encounter was recorded' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `encounter_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `fki_encounter_appointment_id` CONSTRAINT COLUMNS (appointment_id), | ||||
CONSTRAINT NAME `fki_encounter_patient_id_organization_id` CONSTRAINT COLUMNS (patient_id,organization_id), | ||||
CONSTRAINT NAME `encounter_core_concept_id_clinical_effective_date` CONSTRAINT COLUMNS (core_concept_id,clinical_effective_date) |
encounter_additional
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'same as the id column on the encounter table' | |
property_id | int | NOT NULL | COMMENT 'IM concept id reference (i.e. Admission method)' | |
value_id | int | DEFAULT NULL | COMMENT 'IM concept id reference (i.e. Emergency admission)' | |
json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON (i.e. birth delivery details)' | |
text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id, or raw JSON, just a basic text value (i.e. FIN number)' | |
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
CONSTRAINT NAME `encounter_additional_value_id` CONSTRAINT COLUMNS (value_id) |
encounter_event
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the encounter event' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The patient this event belongs to' | |
person_id | bigint | NOT NULL | COMMENT 'The person this event belongs to' | |
encounter_id | bigint | NOT NULL | COMMENT 'Reference to the parent encounter record' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
appointment_id | bigint | DEFAULT NULL | COMMENT 'Reference to the appointment this encounter took part on' | |
clinical_effective_date | datetime | DEFAULT NULL | COMMENT 'The date the encounter took place' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the precision of the date of the encounter' | |
episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Reference to the episode of care this encounter belongs to' | |
service_provider_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the service provider organisation' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of encounter' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the encounter' | |
type | text | COMMENT 'Unused', | ||
sub_type | text | COMMENT 'Unused', | ||
admission_method | varchar(40) | DEFAULT NULL | COMMENT 'The admission method of the encounter' | |
end_date | date | DEFAULT NULL | COMMENT 'The end date of the encounter' | |
institution_location_id | bigint | DEFAULT NULL | COMMENT 'Reference to the institution the encounter took place at' | |
date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the encounter was recorded' | |
finished | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the encounter is finished' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), |
episode_of_care
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the episode of care' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
registration_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration type of the patient' | |
registration_status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration status of the patient' | |
date_registered | date | DEFAULT NULL | COMMENT 'The date the registration was started for this episode of care' | |
date_registered_end | date | DEFAULT NULL | COMMENT 'The date the registration was ended for this episode of care' | |
usual_gp_practitioner_id | bigint | DEFAULT NULL | COMMENT 'Reference to the usual GP for this episode of care' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `episode_of_care_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `episode_of_care_registration_type_concept_id` CONSTRAINT COLUMNS (registration_type_concept_id), | ||||
CONSTRAINT NAME `episode_of_care_date_registered` CONSTRAINT COLUMNS (date_registered), | ||||
CONSTRAINT NAME `episode_of_care_date_registered_end` CONSTRAINT COLUMNS (date_registered_end), | ||||
CONSTRAINT NAME `episode_of_care_person_id` CONSTRAINT COLUMNS (person_id), | ||||
CONSTRAINT NAME `episode_of_care_organization_id` CONSTRAINT COLUMNS (organization_id) |
event_log
Column Name | Type | Constraint | Comment |
---|---|---|---|
dt_change | datetime(3) | NOT NULL | COMMENT 'date time the change was made to this DB' |
change_type | tinyint | NOT NULL | COMMENT 'type of transaction 0=insert, 1=update, 2=delete' |
table_id | tinyint | NOT NULL | COMMENT 'identifier of the table changed' |
record_id | bigint | NOT NULL | COMMENT 'id of the record changed' |
flag
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the flag' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
effective_date | date | DEFAULT NULL | COMMENT 'The date the flag was entered onto the patients record' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
is_active | tinyint(1) | NOT NULL | COMMENT 'Whether the flag is active or not' | |
flag_text | text | COMMENT 'This | is a warning set by the publisher regarding he patient' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `flag_patient_id` CONSTRAINT COLUMNS (patient_id) |
location
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the location' | |
name | varchar(255) | DEFAULT NULL | COMMENT 'The name of a location set by the publisher. E.g. ward, clinic, domiciliary' | |
type_code | varchar(50) | DEFAULT NULL | COMMENT 'The type of location' | |
type_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the type of location eg GP Practice' | |
postcode | varchar(10) | DEFAULT NULL | COMMENT 'The postcode of the location' | |
managing_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the managing organisation of the location' | |
PRIMARY KEY CONSTRAINT(s) `id`), | ||||
CONSTRAINT NAME `fk_location_managing_organisation_id` CONSTRAINT COLUMNS (managing_organization_id) |
medication_order
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the medication order' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the medication order was issued in' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the medication order was issued' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
dose | varchar(1000) | DEFAULT NULL | COMMENT 'Textual description of the dose' | |
quantity_value | double | DEFAULT NULL | COMMENT 'The value of the medication that was prescribed eg 50' | |
quantity_unit | varchar(255) | DEFAULT NULL | COMMENT 'The unit of the medication that was prescribed eg tablets' | |
duration_days | int | DEFAULT NULL | COMMENT 'How many days the medication is prescribed for' | |
estimated_cost | double | DEFAULT NULL | COMMENT 'The estimated cost of the medication' | |
medication_statement_id | bigint | DEFAULT NULL | COMMENT 'Reference to the medication statement. A medication statement can have many medication orders' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication' | |
bnf_reference | varchar(6) | DEFAULT NULL | COMMENT 'A reference to the drug in the BNF dictionary' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event' | |
issue_method | text | COMMENT 'The | issue method of the medication eg hand written' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `medication_order_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `medication_order_core_concept_id` CONSTRAINT COLUMNS (core_concept_id) |
medication_statement
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the medication' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter this medication was recorded in' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the medication was clinical relevant' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
is_active | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the medication is active or not' | |
cancellation_date | date | DEFAULT NULL | COMMENT 'The date the medication was cancelled' | |
dose | varchar(1000) | DEFAULT NULL | COMMENT 'Texual description of the dose of the medication' | |
quantity_value | double | DEFAULT NULL | COMMENT 'The value of the medication that was prescribed eg 50' | |
quantity_unit | varchar(255) | DEFAULT NULL | COMMENT 'The unit of the medication that was prescribed eg tablets' | |
authorisation_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the authorisation type' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the medication' | |
bnf_reference | varchar(6) | DEFAULT NULL | COMMENT 'A reference to the drug in the BNF dictionary' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age the patient was at the time of this event' | |
Issue_method | text | COMMENT 'The | issue method of the medication eg hand written' | |
date_recorded | datetime | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `medication_statement_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `medication_statement_dmd_id` CONSTRAINT COLUMNS (patient_id) |
observation
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the observation' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the observation was recorded at' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the observation was identified by a clinician' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
result_value | double | DEFAULT NULL | COMMENT 'The value of the result of the observation' | |
result_value_units | varchar(50) | DEFAULT NULL | COMMENT 'The units of the result of the observation' | |
result_date | date | DEFAULT NULL | COMMENT 'The date of the result' | |
result_text | text | COMMENT 'Any | text associated with the result' | |
result_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the result' | |
is_problem | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is marked as a problem' | |
is_review | tinyint(1) | NOT NULL | COMMENT 'Whether the observation is a review of an existing problem' | |
problem_end_date | date | DEFAULT NULL | COMMENT 'The end date of the problem' | |
parent_observation_id | bigint | DEFAULT NULL | COMMENT 'Reference to the parent observation in a complex observation eg systolic and diastolic blood pressures will have a parent observation of Blood pressure' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the observation' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the observation' | |
episodicity_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the episodicity of the problem eg First, review, flare' | |
is_primary | tinyint(1) | DEFAULT NULL | COMMENT 'Whether the observation is a primary observation' | |
date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the observation was recorded in the system' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `observation_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `observation_core_concept_id` CONSTRAINT COLUMNS (core_concept_id), | ||||
CONSTRAINT NAME `observation_core_concept_id_is_problem` CONSTRAINT COLUMNS (core_concept_id,is_problem), | ||||
CONSTRAINT NAME `observation_core_concept_id_result_value` CONSTRAINT COLUMNS (core_concept_id,result_value), | ||||
CONSTRAINT NAME `observation_non_core_concept_id` CONSTRAINT COLUMNS (non_core_concept_id), | ||||
CONSTRAINT NAME `ix_observation_organization_id` CONSTRAINT COLUMNS (organization_id), | ||||
CONSTRAINT NAME `ix_observation_clinical_effective_date` CONSTRAINT COLUMNS (clinical_effective_date), | ||||
CONSTRAINT NAME `ix_observation_person_id` CONSTRAINT COLUMNS (person_id) |
observation_additional
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'same as the id column on the observation table' | |
property_id | int | NOT NULL | COMMENT 'IM reference (i.e. significance)' | |
value_id | int | DEFAULT NULL | COMMENT 'IM reference (i.e. minor, significant)' | |
json_value | json | DEFAULT NULL | COMMENT 'the JSON data itself ' | |
text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value' | |
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
CONSTRAINT NAME `observation_additional_value_id` CONSTRAINT COLUMNS (value_id) |
organization
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the organisation' | |
ods_code | varchar(50) | DEFAULT NULL | COMMENT 'ODS Code of the organisation' | |
Name | varchar(255) | DEFAULT NULL | COMMENT 'Name of the organisation' | |
type_code | varchar(50) | DEFAULT NULL | COMMENT 'The type of organisation' | |
type_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the type of organisation eg GP Practice' | |
Postcode | varchar(10) | DEFAULT NULL | COMMENT 'The postcode of the organisation' | |
parent_organization_id | bigint | DEFAULT NULL | COMMENT 'The id of the parent organisation' | |
PRIMARY KEY CONSTRAINT(s) `id`), | ||||
CONSTRAINT NAME `fki_organization_parent_organization_id` CONSTRAINT COLUMNS (parent_organization_id) |
organization_metadata
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'organisation ID, corresponds to same ID in the organizaton table' | |
publishing_software | varchar(50) | NOT NULL | COMMENT 'software name of publishing system, i.e. SystmOne' | |
last_data_to_dds | datetime | DEFAULT NULL | COMMENT 'date time data was last sent to DDS' | |
last_data_cutoff | datetime | DEFAULT NULL | COMMENT 'cutoff date time of the last extract from the publishing system' | |
PRIMARY KEY CONSTRAINT(s) `id`,`publishing_software`) |
patient
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the patient' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
title | varchar(255) | DEFAULT NULL | COMMENT 'The title of the patient' | |
first_names | varchar(255) | DEFAULT NULL | COMMENT 'The first names of the patient' | |
last_name | varchar(255) | DEFAULT NULL | COMMENT 'The last name of the patient' | |
gender_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the gender of the patient' | |
nhs_number | varchar(255) | DEFAULT NULL | COMMENT 'The NHS number of the patient' | |
date_of_birth | date | DEFAULT NULL | COMMENT 'The date of birth of the patient' | |
date_of_death | date | DEFAULT NULL | COMMENT 'The date of death of the patient' | |
current_address_id | bigint | DEFAULT NULL | COMMENT 'Reference to the current address of the patient' | |
ethnic_code_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the ethnicity of the patient' | |
registered_practice_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation the patient is registered at' | |
birth_year | smallint | DEFAULT NULL, | ||
birth_month | tinyint | DEFAULT NULL, | ||
birth_week | tinyint | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `patient_person_id` CONSTRAINT COLUMNS (person_id) |
patient_additional
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'same as the id column on the patient table ' | |
property_id | int | NOT NULL | COMMENT 'IM reference (e.g. Cause of death)' | |
value_id | int | DEFAULT NULL | COMMENT 'IM reference (e.g. COVID)' | |
json_value | json | DEFAULT NULL | COMMENT 'where there is no mapped value_id, just raw JSON' | |
text_value | varchar(255) | DEFAULT NULL | COMMENT 'where there is no mapped value_id or raw JSON, just a basic text value (i.e. GP Practitioner number)' | |
PRIMARY KEY CONSTRAINT(s) `id`,`property_id`), | ||||
CONSTRAINT NAME `ix_patient_additional_id` CONSTRAINT COLUMNS (value_id) |
patient_address
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
Id | bigint | NOT NULL | COMMENT 'Unique Id of the address' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
address_line_1 | varchar(255) | DEFAULT NULL | COMMENT 'The first line of the address' | |
address_line_2 | varchar(255) | DEFAULT NULL | COMMENT 'The second line of the address' | |
address_line_3 | varchar(255) | DEFAULT NULL | COMMENT 'The third line of the address' | |
address_line_4 | varchar(255) | DEFAULT NULL | COMMENT 'The fourth line of the address' | |
city | varchar(255) | DEFAULT NULL | COMMENT 'The city' | |
postcode | varchar(255) | DEFAULT NULL | COMMENT 'The postcode' | |
use_concept_id | int | NOT NULL | COMMENT 'use of address (e.g. home, temporary)' | |
start_date | date | NOT NULL | COMMENT 'The start date of this address being relevant' | |
end_date | date | DEFAULT NULL | COMMENT 'The end date of this address being relevant' | |
lsoa_2001_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the LSOA_2001 code' | |
lsoa_2011_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the LSOA_2011 code' | |
msoa_2001_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the MSOA_2001 code' | |
msoa_2011_code | varchar(9) | DEFAULT NULL | COMMENT 'A reference to the MSOA_2011 code' | |
ward_code | varchar(9) | DEFAULT NULL | COMMENT 'The ward the address belongs to' | |
local_authority_code | varchar(9) | DEFAULT NULL | COMMENT 'The local authority the address belongs to' | |
townsend_deprivation_index | double | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `organization_id`,`Id`,`patient_id`,`person_id`), |
patient_address_match
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | AUTO_INCREMENT | |
patient_address_id | bigint | NOT NULL, | ||
uprn | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL | |
uprn_ralf00 | varchar(255) | DEFAULT NULL, | ||
status | tinyint(1) | DEFAULT NULL, | ||
uprn_property_classification | varchar(45) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
latitude | double | DEFAULT NULL, | ||
longitude | double | DEFAULT NULL, | ||
uprn_xcoordinate | double | DEFAULT NULL, | ||
uprn_ycoordinate | double | DEFAULT NULL, | ||
qualifier | varchar(50) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_rule | varchar(4096) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_date | datetime | DEFAULT NULL, | ||
abp_address_number | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
abp_address_street | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
abp_address_locality | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
abp_address_town | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
abp_address_postcode | varchar(10) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
abp_address_organization | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_pattern_postcode | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_pattern_street | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_pattern_number | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_pattern_building | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
match_pattern_flat | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
algorithm_version | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL | |
epoch | varchar(255) | CHARACTER SET | utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL | |
PRIMARY KEY CONSTRAINT(s) `id`), | ||||
CONSTRAINT NAME `patient_address_uprn_index` CONSTRAINT COLUMNS (uprn), | ||||
CONSTRAINT NAME `patient_address_patient_address_id` CONSTRAINT COLUMNS (id,uprn) |
patient_address_ralf
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL, | ||
organization_id | bigint | NOT NULL, | ||
patient_id | bigint | NOT NULL, | ||
person_id | bigint | NOT NULL, | ||
patient_address_id | bigint | NOT NULL, | ||
patient_address_match_uprn_ralf00 | varchar(255) | NOT NULL, | ||
salt_name | varchar(50) | NOT NULL, | ||
ralf | varchar(255) | NOT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `id`,`patient_address_id`,`patient_address_match_uprn_ralf00`), | ||||
CONSTRAINT NAME `ux_patient_address_ralf_id` CONSTRAINT COLUMNS (id), | ||||
CONSTRAINT NAME `patient_address_ralf_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `patient_address_ralf_patient_address_id` CONSTRAINT COLUMNS (patient_address_id), | ||||
CONSTRAINT NAME `patient_address_ralf_patient_address_match_uprn_ralf_00` CONSTRAINT COLUMNS (patient_address_match_uprn_ralf00) |
patient_contact
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the patient contact' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
use_concept_id | int | DEFAULT NULL | COMMENT 'use of contact (e.g. mobile, home,work' | |
type_concept_id | int | DEFAULT NULL | COMMENT 'type of contact (e.g. phone, email)' | |
start_date | date | DEFAULT NULL | COMMENT 'The start date of the contact being valid' | |
end_date | date | DEFAULT NULL | COMMENT 'The end date of the contact being valid' | |
value | varchar(255) | DEFAULT NULL | COMMENT 'The value of the contact information eg phone number, email address' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`), |
patient_pseudo_id
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the patient pseudo id' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'Reference to the patient this registration status history belongs to' | |
person_id | bigint | NOT NULL | COMMENT 'Reference to the person this registration status history belongs to' | |
salt_name | varchar(50) | NOT NULL | COMMENT 'The name of the salt used to create the pseudo id' | |
Skid | varchar(255) | NOT NULL | COMMENT '"Secure Key IDentifier" - the pseudonymised ID generated for this patient using the salt specified in the salt_name column. Typically generated from patient NHS number, but is configurable on a case by case basis (e.g. may include date of birth)' | |
is_nhs_number_valid | tinyint(1) | NOT NULL | COMMENT 'Whether the nhs number is valid' | |
is_nhs_number_verified_by_publisher | tinyint(1) | NOT NULL | COMMENT 'Whether the nhs number has been verified by the publisher' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `patient_pseudo_id_patient` CONSTRAINT COLUMNS (patient_id) |
patient_uprn
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
uprn | bigint | DEFAULT NULL, | ||
qualifier | varchar(50) | DEFAULT NULL, | ||
algorithm | varchar(255) | DEFAULT NULL, | ||
match | varchar(255) | DEFAULT NULL, | ||
no_address | tinyint(1) | DEFAULT NULL, | ||
invalid_address | tinyint(1) | DEFAULT NULL, | ||
missing_postcode | tinyint(1) | DEFAULT NULL, | ||
invalid_postcode | tinyint(1) | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`patient_id`), |
person
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the person' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
title | varchar(255) | DEFAULT NULL | COMMENT 'The title of the person' | |
first_names | varchar(255) | DEFAULT NULL | COMMENT 'The first names of the person' | |
last_name | varchar(255) | DEFAULT NULL | COMMENT 'The last name of the person' | |
gender_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the gender of the person' | |
nhs_number | varchar(255) | DEFAULT NULL | COMMENT 'The NHS number of the person' | |
date_of_birth | date | DEFAULT NULL | COMMENT 'The date of birth of the person' | |
date_of_death | date | DEFAULT NULL | COMMENT 'The date of death of the person' | |
current_address_id | bigint | NOT NULL | COMMENT 'Reference to the current address of the person' | |
ethnic_code_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the ethnicity of the person' | |
registered_practice_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation the person is registered at' | |
birth_year | smallint | DEFAULT NULL, | ||
birth_month | tinyint | DEFAULT NULL, | ||
birth_week | tinyint | DEFAULT NULL, | ||
PRIMARY KEY CONSTRAINT(s) `id`), |
practitioner
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the practitioner' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
name | varchar(1024) | DEFAULT NULL | COMMENT 'Name of the practitioner' | |
role_code | varchar(50) | DEFAULT NULL | COMMENT 'The code representing the role of the practitioner' | |
role_desc | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the role of the practitioner eg General Medical Practitioner' | |
gmc_code | varchar(50) | DEFAULT NULL | COMMENT 'The GMC code of the practitioner' | |
PRIMARY KEY CONSTRAINT(s) `id`), |
procedure_request
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the procedure' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the procedure was administered at' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the procedure was administered by a clinician' | |
date_precision_concept_id | int | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the status of the procedure' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the procedure' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the procedure' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the procedure' | |
date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the procedure was recorded in the source system' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `procedure_request_patient_id` CONSTRAINT COLUMNS (patient_id) |
referral_request
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the referral' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times' | |
person_id | bigint | NOT NULL | COMMENT 'Unique individual across all organisations' | |
encounter_id | bigint | DEFAULT NULL | COMMENT 'Reference to the encounter the referral was made in' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'The clinician the activity is recorded against' | |
clinical_effective_date | date | DEFAULT NULL | COMMENT 'The date the referral was made' | |
date_precision_concept_id | smallint | DEFAULT NULL | COMMENT 'Identifies the precision of the clinical effectiveness date to either year (1) month (2) day (5) minute (12) second (13) millisecond (14)' | |
requester_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organisation that made the refereral request' | |
recipient_organization_id | bigint | DEFAULT NULL | COMMENT 'Reference to the organization receiving the referral' | |
referral_request_priority_concept_id | smallint | DEFAULT NULL | COMMENT 'Reference to the priority of the referral' | |
referral_request_type_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the type of referral request' | |
Mode | varchar(50) | DEFAULT NULL | COMMENT 'The mode of the referral' | |
outgoing_referral | tinyint(1) | DEFAULT NULL | COMMENT 'Whether this is an outgoing referral' | |
is_review | tinyint(1) | DEFAULT NULL | COMMENT 'Whether this referral is a review' | |
core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the referral' | |
non_core_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the clinical coding of the referral' | |
age_at_event | decimal(5,2) | DEFAULT NULL | COMMENT 'The age of the patient at the time of the referral' | |
date_recorded | datetime | DEFAULT NULL | COMMENT 'The date the referral request was added to the source system' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`person_id`,`id`), | ||||
CONSTRAINT NAME `referral_request_patient_id` CONSTRAINT COLUMNS (patient_id), | ||||
CONSTRAINT NAME `referral_request_core_concept_id` CONSTRAINT COLUMNS (core_concept_id) |
registration_status_history
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the registration status history' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
patient_id | bigint | NOT NULL | COMMENT 'Reference to the patient this registration status history belongs to' | |
person_id | bigint | NOT NULL | COMMENT 'Reference to the person this registration status history belongs to' | |
episode_of_care_id | bigint | DEFAULT NULL | COMMENT 'Reference to the episode of care this status history belongs to' | |
registration_status_concept_id | int | DEFAULT NULL | COMMENT 'Reference to the registration status' | |
start_date | datetime | DEFAULT NULL | COMMENT 'The start date for the period this registration status history was valid' | |
end_date | datetime | DEFAULT NULL | COMMENT 'The end date for the period this registration status history was valid' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`,`patient_id`,`person_id`), |
schedule
Column Name | Type | Constraint | Comment | |
---|---|---|---|---|
id | bigint | NOT NULL | COMMENT 'Unique Id of the schedule' | |
organization_id | bigint | NOT NULL | COMMENT 'Owning organisation (i.e. publisher)' | |
practitioner_id | bigint | DEFAULT NULL | COMMENT 'Reference to the practitioner who owns the schedule' | |
start_date | date | DEFAULT NULL | COMMENT 'The start date of the schedule' | |
type | varchar(255) | DEFAULT NULL | COMMENT 'The type of schedule eg Timed Appointments' | |
location | varchar(255) | DEFAULT NULL | COMMENT 'Textual description of the location the schedule was held at' | |
name | varchar(150) | DEFAULT NULL | COMMENT 'The name of the schedule' | |
PRIMARY KEY CONSTRAINT(s) `organization_id`,`id`), |