Definition

The mkb_drugpack model provides pharmaceutical packaging details, linking medications to their commercial forms. It combines preparation data with pack configurations including pricing, quantities, and supplier information.

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.

Examples

Detailed packaging information for a specific drug

SELECT
  drug.term AS drug_name,
  drug.dmd_product_code_id,
  pack.pack_description,
  pack.units_per_pack,
  pack.unit_of_measure,
  pack.pack_price,
  pack.price_per_unit
FROM explorer_open_safely.drug_code AS drug
JOIN explorer_open_safely.mkb_drug_pack AS pack
  ON drug.code_id = pack.preparation_code_id
WHERE
  drug.term LIKE '%metformin%'
ORDER BY
  drug.term, pack.units_per_pack

All packaging options for a specific medication

SELECT
  term AS drug_name,
  supplier_name,
  pack_description,
  units_per_pack,
  unit_of_measure,
  pack_price,
  price_per_unit
FROM explorer_open_safely.mkb_drug_pack
WHERE
  term LIKE '%Amoxicillin%'
ORDER BY
  price_per_unit ASC

Medications with the most packaging options

SELECT
  term AS drug_name,
  COUNT(DISTINCT preparation_pack_id) AS pack_options_count,
  COUNT(DISTINCT supplier_name) AS supplier_count,
  MIN(price_per_unit) AS min_price_per_unit,
  MAX(price_per_unit) AS max_price_per_unit,
  AVG(price_per_unit) AS avg_price_per_unit
FROM explorer_open_safely.mkb_drug_pack
WHERE
  price_per_unit > 0
GROUP BY
  term
HAVING
  COUNT(DISTINCT preparation_pack_id) > 3
ORDER BY
  pack_options_count DESC,
  supplier_count DESC
LIMIT 20