Definition

Purpose

The allergy model extracts allergy-related data for patients included in recruit studies from the anon extract. The view is scoped to fully anonymised patients via the included patients view.

Information

The Allergy data model provides a comprehensive record of patient allergy information derived from observation data. It captures coded allergy entries recorded by clinicians in EMIS Web, including associated clinical codes and consultation context. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

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

• emis_observation_guid (PK): The unique identifier for the allergy observation record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient
• pseudo_patient_id (FK): Hashed patient identifier - links to patient
• emis_registration_organisation_guid (FK): Links to organisation
• emis_code_id (FK): Links to clinical_code
• emis_consultation_guid (FK): Links to consultation where allergy was recorded
• study_id (FK): Links to user_studies

Examples

Get all allergies for a patient (patient_id is hashed)

SELECT *
FROM hive.explorer_recruit_anon.allergy
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find confidential or sensitive allergies

SELECT *
FROM hive.explorer_recruit_anon.allergy
WHERE confidential_flag = TRUE
   OR fhir_status = 'sensitive'
LIMIT 100;

Get allergies with SNOMED mapping for interoperability

SELECT
  a.emis_observation_guid AS observation_id,
  a.emis_code_id AS code_id,
  cc.snomed_concept_id,
  cc.snomed_description_id
FROM hive.explorer_recruit_anon.allergy a
LEFT JOIN hive.explorer_recruit_anon.codeable_concept cc
  ON a.emis_code_id = cc.emis_code_id
WHERE cc.snomed_concept_id IS NOT NULL
LIMIT 100;