Schema

Core

The core schema represents the MKB Mapping DMD Preparation 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
preparation_code_id	bigint	The unique EMIS code identifier for the drug preparation	11678741000033113
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.	13327711000001100
snomed_description_id	bigint	The unique identifier for the preferred term that describes the concept within the SNOMED CT clinical terminology.	2914521000001118
other_code	varchar	The code from EMIS legacy coding system	NULL
other_code_system	varchar	Other system http address	https://fhir.hl7.org.uk/Id/egton-codes
other_display	varchar	The description from the legacy EMIS coding system	NULL
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.000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type
is_deleted	boolean
dmd_product_code_id	bigint
snomed_description_id	bigint
preparation_code_id	bigint
other_code	varchar
other_code_system	varchar
other_display	varchar
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code	varchar	✓	✓
other_code_system	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

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.

Column Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_mkb_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
data_filter	integer	✓	✗
is_deleted	boolean	✗	✓
drug_preparation_code_id	bigint	✓	✓	preparation_code_id
snomed_concept_id	bigint	✓	✓	dmd_product_code_id
snomed_description_id	bigint	✓	✓
other_code_system	varchar	✓	✓
other_code	varchar	✓	✓
other_display	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓