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 may be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys are preserved so deleted linked records can be identified.

  are preserved for audit purposes.

Examples

Find codes based on DMD product code

SELECT
  *
from 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 explorer_open_safely.drug_code
WHERE
  term LIKE '%Paracetamol%'
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 explorer_open_safely.drug_code
WHERE
  code_id IN (1572871000006117, 294711000000118)
ORDER BY
  exception_type, term;