Schema
The core schema represents the Drug Code schema derived from EMIS source systems.
| Column Name | Data Type | Description | Example |
|---|---|---|---|
| is_deleted | boolean | Indicates if this record should be considered soft deleted | FALSE |
| code_id | bigint | The unique EMIS code identifier for the clinical code | 11678741000033113 |
| old_code | varchar | The legacy code that provide a standard vocabulary for clinicians to record patient findings and procedures, in health and social care IT systems across primary and secondary care. | ‘META1807’ |
| term | varchar | The description of the EMIS clinical code | ‘Electrode Gel’ |
| dmd_product_code_id | bigint | NHS Dictionary of Medicines and Devices (dm+d) product code identifier; includes both actual and virtual medicinal products. In practice, these ids are directly mapped to SNOMED CT concepts. | 12354611500001104 |
| is_sensitive | boolean | Indicates whether the clinical code is flagged as sensitive. The sensitive reference set codes are 999004351000000109( gender related issues), 999004371000000100(assisted fertilisation), 999004361000000107(termination of pregnancy) and 999004381000000103 (sexually transmitted disease) | TRUE |
| organisation | varchar | Set to ‘EMIS’ | ‘EMIS’ |
| transform_datetime | timestamp(6) with time zone | The timestamp indicating when the record was last processed and updated in the data model. | ‘2023-05-12 14:30:15.000000 UTC’ |
| _execution_date | varchar | The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility. | ‘20230512143015’ |
Vanilla
Section titled “Vanilla”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _record_version | varchar | ✓ | ✗ | ||
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| dmd_productcode_id | bigint | ✓ | ✓ | dmd_product_code_id | |
| emis_old_code | varchar | ✓ | ✓ | old_code | |
| emis_term | varchar | ✓ | ✓ | term | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Apollo
Section titled “Apollo”Not used in this schema
Artemis
Section titled “Artemis”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _record_version | varchar | ✓ | ✗ | ||
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| dmd_productcode_id | bigint | ✓ | ✓ | dmd_product_code_id | |
| emis_old_code | varchar | ✓ | ✓ | old_code | |
| emis_term | varchar | ✓ | ✓ | term | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Hermes
Section titled “Hermes”The table below presents the flavour specific schema.
| Column Name | Data Type |
|---|---|
| is_deleted | boolean |
| code_id | bigint |
| old_code | varchar |
| term | varchar |
| dmd_product_code_id | bigint |
| organisation | varchar |
| transform_datetime | timestamp(6) with time zone |
Hestia
Section titled “Hestia”Not used in this schema
Olympus
Section titled “Olympus”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| emis_term | varchar | ✓ | ✓ | term | |
| dmd_product_code_id | bigint | ✓ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Prometheus
Section titled “Prometheus”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| emis_term | varchar | ✓ | ✓ | term | |
| dmd_product_code_id | bigint | ✓ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Pseudo_Anon
Section titled “Pseudo_Anon”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| code_id | bigint | ✓ | ✓ | ||
| term | varchar | ✓ | ✓ | ||
| dmd_product_code_id | bigint | ✓ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Themis
Section titled “Themis”The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _record_version | varchar | ✓ | ✗ | ||
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| dmd_productcode_id | bigint | ✓ | ✓ | dmd_product_code_id | |
| emis_old_code | varchar | ✓ | ✓ | old_code | |
| emis_term | varchar | ✓ | ✓ | term | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.
| Columns Name | Data Types | Data in V1 | Data in V2 | Core Mapping | Comments |
|---|---|---|---|---|---|
| _record_version | varchar | ✓ | ✗ | ||
| _ingest_time | varchar | ✓ | ✗ | ||
| is_deleted | boolean | ✗ | ✓ | ||
| emis_code_id | bigint | ✓ | ✓ | code_id | |
| dmd_productcode_id | bigint | ✓ | ✓ | dmd_product_code_id | |
| emis_old_code | varchar | ✓ | ✓ | old_code | |
| emis_term | varchar | ✓ | ✓ | term | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ | ||
| data_filter | integer | ✓ | ✗ |