Definition

In clinical practice, medications are often prescribed to treat, manage, or monitor a specific patient problem or condition. However, the relationship between a patient’s clinical problem and the medication prescribed for that problem is not directly available within the core medication or problem models.

The Problem–Medication bridge models provide the many-to-many relationship between Problem records and Medication records in EMIS-sourced data. They support a key clinical workflow in which practitioners link medications to problems to document which treatments manage specific conditions.

There are two models representing this relationship in iPCVs:

Problem Drug Record
Problem Issue Record

Information

Problem Drug Record

The Problem Drug Record model links patient problems to prescriptions (Drug Record).

This model contains the following key information for problems linked to drug record:

Problem, drug record and patient identifiers
Links to organisation
Clinical coding for problem and prescription
Confidentiality and sensitivity information

Problem Issue Record

The Problem Issue Record model links patient problems to prescription issues (Issue Record).

This model contains the following key information for problems linked to issue record:

Problem, issue record and patient identifiers
Links to organisation
Clinical coding for problem and prescription
Confidentiality and sensitivity information

Grain and Scope

Each row is a unique problem linked to medication record (prescription or issue).

For Problem Drug record, each record is uniquely identified by either combining problem_observation_id, drug_record_id and organisation or combining problem_observation_uuid and drug_record_uuid.

This model includes the following key identifiers:

problem_observation_id: The unique internal identifier for the problem observation record within an organisation.
problem_observation_guid: The GUID for the problem record within an organisation.
problem_observation_uuid: The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.
drug_record_id: The unique internal identifier for the drug record within an organisation.
drug_record_guid: The GUID for the drug record within an organisation.
drug_record_uuid: The UUID derived from drug_record_id and organisation, providing a stable unique identifier.

For Problem Issue record, each record is uniquely identified by either combining problem_observation_id, issue_record_id and organisation or combining problem_observation_uuid and issue_record_uuid.

This model includes the following key identifiers:

problem_observation_id: The unique internal identifier for the problem observation record within an organisation.
problem_observation_guid: The GUID for the problem record within an organisation.
problem_observation_uuid: The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.
issue_record_id: The unique internal identifier for the issue record within an organisation.
issue_record_guid: The GUID for the issue record within an organisation.
issue_record_uuid: The UUID derived from issue_record_id and organisation, providing a stable unique identifier.

For both models the following fields are important for tracking data lineage and freshness:

is_deleted: Indicates whether an issue record has been deleted at source.
is_confidential: Indicates whether the record is confidential.
transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

many-to-many relationship: A single medication can be prescribed for multiple problems (e.g. aspirin for both cardiac and pain management), and a single problem can have multiple medications prescribed to treat it (e.g. multiple drugs for diabetes management).
Linking requires deliberate action: In EMIS source system, clinicians can link various clinical events to problems including medications. However, creating these links requires deliberate action by the clinician during consultations, and usage patterns vary significantly between practices.

Overview

flowchart TD
    problem["Problem"]
    drug_link[["Problem Drug Record Link"]]
    issue_link[["Problem Issue Record Link"]]
    drug_record["Drug Record"]
    issue_record["Issue Record"]
    patient["Patient"]

    patient --> problem
    problem --> drug_link
    problem --> issue_link
    drug_link --> drug_record
    issue_link --> issue_record

    classDef nodeStyle stroke:#9961a4;
    class problem,drug_link,issue_link,drug_record,issue_record,patient nodeStyle;

    linkStyle default stroke:#117abf,fill:none

Examples

Find all drug records linked to a specific problem

SELECT
  problem_emis_observation_guid,
  emis_drug_guid,
  exa_drug_guid,
  recorded_date,
  emis_patient_id
FROM
  hive.explorer_ipcv_vanilla.medication_drugrecord_problem_link_v2
WHERE
  organisation = 'CDB-12345'
  AND problem_observation_id = 123456
  AND is_deleted = FALSE;

Find all problems linked to a specific medication issue

SELECT
  problem_emis_observation_guid,
  emis_issue_guid,
  exa_issue_guid,
  recorded_date,
  emis_patient_id
FROM
  hive.explorer_ipcv_vanilla.medication_issuerecord_problem_link_v2
WHERE
  organisation = 'CDB-12345'
  AND issue_record_id = 789012
  AND is_deleted = FALSE;

Join problem links to problem and drug record details

SELECT
  pl.problem_emis_observation_guid,
  p.emis_original_term AS problem_term,
  pl.emis_drug_guid,
  dr.emis_original_term AS drug_name,
  pl.recorded_date
FROM
  hive.explorer_ipcv_vanilla.medication_drugrecord_problem_link_v2 AS pl
  JOIN hive.explorer_ipcv_vanilla.problem_v2 AS p ON pl.problem_observation_id = p.observation_id
  AND pl.organisation = p.organisation
  JOIN hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr ON pl.drug_record_id = dr.drug_record_id
  AND pl.organisation = dr.organisation
WHERE
  pl.organisation = 'CDB-12345'
  AND pl.is_deleted = FALSE
  AND p.is_deleted = FALSE
  AND dr.is_deleted = FALSE;