Definition

The Allergy data model provides a comprehensive record of patient allergy information derived from observation data. It captures coded allergy entries recorded by clinicians, including the associated clinical code, episodicity, date of recording, and contextual consultation information. This model is essential for understanding a patient’s allergy history and supporting safe clinical decision-making across healthcare services.

Information

The Allergy data model is a structured representation of allergy observations recorded against patients in EMIS Web. Each allergy record is derived from the broader observation data and filtered specifically to entries categorised as allergies. This includes both medication allergies and environmental allergies, distinguished via the code_category field.

Each allergy record is uniquely identified by a combination of observation_id and organisation, ensuring a distinct entry for every allergy within a specific healthcare organisation.

The model includes several key data points to provide a complete clinical picture of patient allergies:

This model includes some of the key identifiers:

• observation_id: The unique internal identifier for the observation record.
• observation_guid: GUID for the observation record
• observation_uuid: UUID for the observation record

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the allergy record has been soft-deleted in the source system.
• is_sensitive: Indicates whether the record is flagged as sensitive.
• is_confidential: Indicates whether the record is confidential.
• transform_datetime: The timestamp indicating when the record was last processed in the data warehouse.

Overview

flowchart TB
  subgraph container["Data Collection"]
    n10["Allergy Code"]
    n11["Clinical Dates"]
    n12["Episodicity & Context"]
  end

  n17["Organisation 1"] --> n7
  n17["Organisation 1"] --> n5
  n18["Organisation 2"] --> n6
  n18["Organisation 2"] --> n8
  n18["Organisation 2"] --> n4

  n7["Patient 123"] --> container
  n5["Patient 98"] --> container
  n6["Patient 456"] --> container
  n4["Patient 20"] --> container
  n8["Patient 47"] --> container

  container --> n16["Gather allergy data"]
  n16 --> n14["ETL"]
  n14 --> n15["Allergy Data Model"]

  n7["Patient 123"]:::rect
  n5["Patient 98"]:::rect
  n6["Patient 456"]:::rect
  n4["Patient 20"]:::rect

Changes in iPCV v2

1. Removal of Columns

Several columns previously available directly in the allergy (v1) table have been removed from the allergy (v2) table. These include:

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data:

• MKB reference columns: No longer included to reduce complexity and avoid duplication, as this data is now available through dedicated reference tables
• Patient opt flags columns: Removed as these flags are maintained in the patient scope dimension table, providing a single source of truth

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
confidential_patient_flag	patientid	patient_v2
dummy_patient_flag	patientid	patient_v2
non_regular_and_current_active_flag	patientid	patient_v2
opt_out_93c1_flag	patientid	patient_v2
opt_out_9nd19nu09nu4_flag	patientid	patient_v2
opt_out_9nd19nu0_flag	patientid	patient_v2
opt_out_9nu0_flag	patientid	patient_v2
regular_and_current_active_flag	patientid	patient_v2
regular_current_active_and_inactive_flag	patientid	patient_v2
regular_patient_flag	patientid	patient_v2
sensitive_patient_flag	patientid	patient_v2
data_filter		Removed as it is deprecated in v2
_record_version		No longer available in v2
_update_date		Changes tracked differently in v2
_update_hour		Changes tracked differently in v2

Customer benefit

• Single source of truth for patient flags through the patient scope dimension table
• Reduced risk of data inconsistencies and synchronization issues
• Improved data lineage tracking with centralized reference tables

Customer action

• Review and update any processes or integrations that relied on the removed columns
• Use dedicated MKB reference tables for code mappings instead of inline columns
• Query the patient scope dimension table for patient opt flags and status information
• Ensure that any necessary mappings or transformations are adjusted accordingly

2. NULL value columns

Several columns in the iPCV2 table now consistently return NULL values and have been retained for backward compatibility. These include:

Why?

These columns are retained to maintain backward compatibility while returning NULL values to ensure consistent behavior in downstream processes.

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
registration_ods_code		Always NULL as it is deprecated in v2
fhir_category		Always NULL as it is deprecated in v2
fhir_episodicity		Always NULL as it is deprecated in v2
fhir_status		Always NULL as it is deprecated in v2
status		Always NULL as it is deprecated in v2
processing_id		Always NULL as it is deprecated in v2

Customer benefit

• User in role information is now standardized through entered_by_user_in_role_id and authorising_user_in_role_id foreign keys joined with user_in_role_v2
• Reduced risk of unexpected behavior in downstream processes
• Clear distinction between deprecated columns and new standardized relationships

Customer action

• Identify any queries or reports that use the deprecated columns
• Update processes to use entered_by_user_in_role_id and authorising_user_in_role_id, joining with user_in_role_v2 to retrieve user in role details
• For organization-related queries, use observation_organisation_id instead of registration_ods_code

3. Addition of new columns

The v2 allergy model introduces additional columns to enhance auditability, relationships, and clinical context. These include:

User tracking columns:

• entered_by_user_in_role_id
• authorising_user_in_role_id

Relationship and context columns:

• observation_id
• parent_observation_id
• parent_observation_guid
• parent_observation_uuid
• consultation_id
• problem_observation_id
• problem_observation_guid
• problem_observation_uuid
• observation_organisation_id
• consultation_source_original_term
• code_category
• associated_text

Numeric measurement columns:

• numeric_operator
• numeric_value
• numeric_unit
• ucum_code
• unit_of_measure

Qualifier and range columns:

• range_minimum_operator
• range_minimum
• range_minimum_text
• range_maximum_operator
• range_maximum
• range_maximum_text
• range_units
• range_qualifier_description
• qualifiers
• is_abnormal
• is_parent
• qualifier_mapping

Why?

These columns have been added to enhance the data model:

• User tracking columns: Enable auditing and lineage tracking by providing standardized foreign keys to the user_in_role_v2 table
• Relationship and context columns: Support hierarchical allergy structures, link allergies to consultations and problems, and provide organizational context
• Numeric measurement columns: Standardize representation of numeric values with operators and units of measure
• Qualifier and range columns: Provide comprehensive support for reference ranges and contextual information

Customer benefit

• Improved data consistency through standardized foreign key relationships
• Enhanced ability to track allergy hierarchies and relationships
• Better support for temporal analysis and consultation-based reporting
• Standardized representation of numeric values with operators and units

Customer action

• Join entered_by_user_in_role_id and authorising_user_in_role_id with user_in_role_v2 for audit and authorization tracking
• Use parent_observation_id to build hierarchical allergy structures
• Use consultation_id to link allergies to specific consultations
• Use problem_observation_id to identify allergies linked to specific problems

Examples

Get all allergies for a patient

SELECT *
FROM hive.explorer_ipcv_vanilla.allergy_v2
WHERE patient_id = 123456789
LIMIT 100

Find sensitive or confidential allergies

SELECT *
FROM hive.explorer_ipcv_vanilla.allergy_v2
WHERE is_sensitive = TRUE
  OR is_confidential = TRUE
LIMIT 100

Get allergies with SNOMED mapping for interoperability

SELECT
  a.observation_id,
  a.code_id,
  m.snomed_concept_id,
  m.snomed_description_id
FROM hive.explorer_ipcv_vanilla.allergy_v2 a
LEFT JOIN hive.explorer_ipcv_vanilla.codeable_concept_v2 m
  ON a.code_id = m.emis_code_id
  AND a.organisation = m.organisation
WHERE m.snomed_concept_id IS NOT NULL
LIMIT 100