Schema

Core

The core schema represents the MKB Drug Pack 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_pack_id	bigint	The unique internal identifier for the preparation in a given pack	78901
preparation_code_id	bigint	The unique EMIS code identifier for the drug preparation	11678741000033113
supplier_name	varchar	The name of the supplier/manufacturer	‘Examplus Healthcare Ltd’
term	varchar	The EMIS description of the drug pack	‘Metformin 500mg tablets’
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
pack_description	varchar	A description of the pack	‘28 tablet’
units_per_pack	double	The number of unit of measure contained in the pack	28.0
unit_of_measure	varchar	The unit of measure of the drug pack (e.g., tablets, ml)	‘tablet’
pack_price	real	The cost of the drug pack	1.42
price_per_unit	real	The cost of an individual unit of measure (pack price / units per pack)	0.0507
is_guide_price_pack	boolean	Indicates if the pack price is the amount the NHS will reimburse the prescriber	TRUE
is_breakable	boolean	Indicates whether a pack can be broken into smaller quantities; for example, splitting a pack of 12 to use only 10.	FALSE
is_non_divisible	boolean	Indicates whether the pack cannot be broken into smaller quantities; for example, an inhaler.	FALSE
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-09-01 09:00:00.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’

Vanilla

Not used in this schema

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.

Columns Name	Data Types	Data in V1	Data in V2	Core Mapping	Comments
_mkb_version	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	preparation_code_id
prep_pack_id	bigint	✓	✓	preparation_pack_id
supplier_name	varchar	✓	✓
emis_term	varchar	✓	✓	term
dmd_productcode_id	bigint	✓	✓	dmd_product_code_id
pack_description	varchar	✓	✓
units_per_pack	double	✓	✓
uom	varchar	✓	✓	unit_of_measure
pack_price	real	✓	✓
price_per_unit	real	✓	✓
is_guide_price_pack_flag	boolean	✓	✓	is_guide_price_pack
is_breakable_flag	boolean	✓	✓	is_breakable
non_divisible_flag	boolean	✓	✓	is_non_divisible
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✗	✓

Artemis

Not used in this schema

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type
is_deleted	boolean
preparation_code_id	bigint
preparation_pack_id	bigint
supplier_name	varchar
term	varchar
dmd_product_code_id	bigint
pack_description	varchar
units_per_pack	double
unit_of_measure	varchar
pack_price	real
price_per_unit	real
is_guide_price_pack	boolean
is_breakable	boolean
is_non_divisible	boolean
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

Not used in this schema

Olympus

Not used in this schema

Prometheus

Not used in this schema

Pseudo_Anon

Not used in this schema

Themis

Not used in this schema

Zeus

Not used in this schema