Definition

The drug code model provides a standardized reference catalog of pharmaceutical products based on the Dictionary of Medicines and Devices (dm+d) along with essential exception codes for historical data compatibility.

Information

The drug code model provides a standardized reference catalog of pharmaceutical products used within the healthcare system. It focuses specifically on medications defined in the Dictionary of Medicines and Devices (dm+d), along with select exception codes needed for historical data compatibility. The model includes drug identifiers, terms, and reference codes to enable consistent medication analysis across all participating organizations.

Schema updates for data maintenance

• The is_deleted column has been added to indicate whether a clinical code has been marked as deleted

Benefit

• The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes

Examples

Find codes based on DMD product code

SELECT
  *
from hive.explorer_open_safely.drug_code
where dmd_product_code_id = 1234567890123456

Find medications with a specific ingredient

SELECT
  code_id,
  term,
  dmd_product_code_id
FROM hive.explorer_open_safely.drug_code
WHERE
  term LIKE '%Paracetamol%'
  AND is_deleted = FALSE
ORDER BY
  term

Find medications migrated or degraded during system transfers

SELECT
  code_id,
  term,
  old_code,
  CASE
    WHEN code_id = 1572871000006117 THEN 'Awaiting migration'
    WHEN code_id = 294711000000118 THEN 'Transfer-degraded'
  END AS exception_type
FROM hive.explorer_open_safely.drug_code
WHERE
  code_id IN (1572871000006117, 294711000000118)
ORDER BY
  exception_type, term;