Definition

The drug code model provides a standardized reference catalog of pharmaceutical products based on the NHS Dictionary of Medicines and Devices (dm+d) product code ID (which is also a SNOMED CT concept ID). Similar to the mkb dmd preparation table but includes essential exception codes for historical data compatibility.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.

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;