Definition

The medication drug record model provides a comprehensive view of the patient’s prescription records.

The model represents an individual prescription medication item for a patient (for example, a repeat prescription item, an acute item, or a historical drug record). It captures the high‑level context such as the drug, dose, route, instructions, dates, status and key participants as well as summary information of the issuing record of that prescription.

Information

The drug record model should be treated as the source of medication records that can be issued one or more times.

This model contains the following key information for drug records:

• Record and patient identifiers
• Links to issue record and registration organisation
• Clinical coding and terms
• Prescription effective and system dates
• Prescription duration and frequency information
• Prescription status, dosing and quantity details
• Authorisation and GP (user) details
• Current status summary of prescription issues.
• Indicators for private prescriptions
• Record confidentiality

Grain and Scope

Each row is a unique prescription record and each record is uniquely identified by either combining drug_record_id and organisation or the drug_record_uuid column.

This model includes the following key identifiers:

• 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.

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

• is_deleted: Indicates whether a drug 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.

Overview

flowchart TD
    patient["Patient"]
    drug_record[["Drug Record Model"]]
    issue_record["Issue Record"]

    patient --> drug_record
    drug_record --> issue_record

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

    linkStyle default stroke:#117abf,fill:none

Examples

Find active drug records for a patient

SELECT
  emis_drug_guid,
  emis_patient_id,
  emis_code_id,
  dose,
  quantity,
  uom,
  emis_medication_status
FROM
  hive.explorer_ipcv_vanilla.medication_drug_record_v2
WHERE
  emis_patient_id = 123456
  AND organisation = 'CDB-12345'
  AND emis_medication_status = 1
  AND is_deleted = FALSE;

Identify privately prescribed items

SELECT
  emis_drug_guid,
  privately_prescribed_flag,
  cancellation_date,
  end_date
FROM
  hive.explorer_ipcv_vanilla.medication_drug_record_v2
WHERE
  organisation = 'CDB-12345'
  AND privately_prescribed_flag = TRUE
  AND is_deleted = FALSE;

Identify ODS codes for patients’ registered organisations

SELECT
  dr.drug_record_id,
  dr.emis_patient_id,
  p.registration_organisation_id,
  o.ods_code
FROM
  hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr
  JOIN hive.explorer_ipcv_vanilla.patient_v2 AS p ON dr.emis_patient_id = p.emis_patient_id
  AND dr.organisation = p.organisation
  JOIN hive.explorer_ipcv_vanilla.organisation_v2 AS o ON p.registration_organisation_id = o.emis_organisation_id
  AND p.organisation = o.organisation;

Join drug records to their issue records

SELECT
  dr.emis_drug_guid,
  dr.emis_code_id,
  dr.dose,
  ir.emis_issue_guid,
  ir.quantity,
  ir.effective_date
FROM
  hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr
  JOIN hive.explorer_ipcv_vanilla.medication_issue_record_v2 AS ir ON dr.emis_drug_guid = ir.emis_drug_guid
  AND dr.organisation = ir.organisation
WHERE
  dr.organisation = 'CDB-12345'
  AND dr.is_deleted = FALSE
  AND ir.is_deleted = FALSE;