Definition

The Problem-Medication Link tables are bridge tables that model the many-to-many relationships between Problem records and Medication records in the GP clinical system. These tables support a key clinical workflow where practitioners can link medications to problems to document which treatments are being used to manage specific conditions.

Information

These linking tables resolve two important many-to-many relationships:

• Problem Drug Record (carerecord_problemdrugrecord) — links Problem records to Medication Authorisations (repeat medications)
• Problem Issue Record (carerecord_problemissuerecord) — links Problem records to Medication Issues (acute/one-time prescriptions)

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). The bridge table structure correctly models this bidirectional many-to-many relationship.

Clinical context

In EMIS Web, clinicians can link various clinical events to problems including medications, referrals, observations, reviews, consultations, and documents. However, creating these links requires deliberate action by the clinician during consultations, and usage patterns vary significantly between practices. Currently, only medication links are modelled in iPCV due to these being the most consistently used and having clear clinical value.

Historical context

Legacy GP data extracts modelled this relationship incorrectly, assuming that a medication would only be linked to one problem by including a problem ID attribute as part of the medication extract. The current bridge table approach corrects this by properly supporting the many-to-many nature of the relationship.

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

What’s changed in iPCV v2

1. Removal of Patient Flags and Opt-out columns

In iPCV v2, all patient-related columns have been moved to the Patient model to reduce data duplication and provide a single source of truth. The following columns have been completely removed from both problem drug record and problem issue record link models:

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2

Why?

• To ensure a single source of truth for patient-level flags and reduce data duplication across models.

Customer benefit

• Consistent patient flag values sourced from one place.
• Smaller, leaner link tables with improved query performance.

Customer action

• Replace direct use of patient flag columns with joins to patient_v2 using patient_id and organisation.

2. Removal of metadata columns and low-value columns

Some columns that were redundant, consistently null, or better sourced from other tables have been removed from the v2 srv layers:

• _record_version — replaced by transform_datetime and _ingest_time
• intent — was a hardcoded value (plan for drug records, order for issue records) with no analytical value

Why?

• Reduce storage and remove columns with no analytical value from generic views.

Customer benefit

• A cleaner, simpler schema focused on meaningful data.

Customer action

• Remove dependencies on _record_version and intent in your SQL.
• For incremental pipelines, use transform_datetime and/or _ingest_time as your primary change-detection fields.

3. Renaming and re-mapping of key columns

Several columns have been renamed between v1 and v2 to align naming conventions across the wider iPCV v2 models and clarify meaning (e.g., GUID vs UUID vs ID):

V1 Column Name	V2 Column Name
problem_id	problem_observation_guid
problem_id_type5	problem_observation_uuid
medication_id	drug_record_guid / issue_record_guid
exa_medication_guid	drug_record_uuid / issue_record_uuid
recorded_date	availability_datetime
registration_id	patient_guid
patientid	patient_id
emis_registration_organisation_guid	patient_organisation_guid
sensitive_flag	is_sensitive
confidential_flag	is_confidential
code_id	drug_record_code_id / issue_record_code_id
organisation_id	patient_organisation_id

Customer action

• Update all queries referencing the old column names to use the new v2 names.

4. Addition of new metadata and lifecycle columns

The following columns have been added in v2:

Lifecycle and availability:

• is_deleted — supports soft-deletion tracking
• load_datetime — datetime the record was loaded into the raw layer
• transform_datetime — datetime the data was made available with a relevant change
• _execution_date — formatted transform datetime for versioning

New key identifier columns for improved joining to other referenced tables:

• problem_observation_id — numeric FK to the problem observation
• drug_record_id / issue_record_id — numeric FK to the medication record
• patient_uuid — UUID5 generated from patient_id + organisation
• patient_organisation_id — numeric organisation identifier (renamed from organisation_id, now present in both drug and issue record links)
• patient_organisation_guid — renamed from emis_registration_organisation_guid
• patient_organisation_uuid — UUID5 generated from patient_organisation_id + organisation
• drug_record_organisation_id / issue_record_organisation_id — numeric organisation identifier for the linked medication record
• drug_record_organisation_uuid / issue_record_organisation_uuid — UUID5 generated from the organisation_id + organisation
• drug_record_code_id / issue_record_code_id — renamed from code_id, FK to the clinical code for the medication record
• observation_code_id — FK to the clinical code for the problem observation

Sensitivity history:

• was_sensitive — whether the record was previously marked sensitive
• was_confidential — whether the record was previously marked confidential

Why?

• To provide a richer, auditable lifecycle for each link record.
• To support soft-deletion, history of confidentiality/sensitivity changes, and robust joining across models.

Customer benefit

• Ability to distinguish current vs historical or deleted link records.
• Consistent transform/ingestion metadata for incremental loads.

Customer action

• For incremental pipelines, use transform_datetime and/or _ingest_time as your primary change-detection fields.
• Consider explicitly excluding is_deleted = TRUE rows unless required.

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;