Medicine Classification

This is based on DrugBaseEvaluationClassification but cleaned up towards final inclusion into the mw maintenance code.

Creating The Ingredient/Moiety Classes

Active Moiety (Misrf)

CREATE TABLE mw."mc_Misrf" AS
WITH ObjectTrait AS (
  SELECT DISTINCT
         i.scoperInternalId AS objectId,
         m.playerInternalId AS traitValue
    FROM mw.Role_ i
      INNER JOIN mw.Role_ m ON(m.scoperInternalId = i.playerInternalId)
   WHERE i.classCode = 'ACTI' AND m.classCode = 'ACTM'
), ObjectTraitCount AS (
 SELECT objectId, 
        COUNT(1) AS traitCount
    FROM ObjectTrait
    GROUP BY objectId
), ObjectTraitWithCount AS (
 SELECT * FROM ObjectTrait
   INNER JOIN ObjectTraitCount
    USING(objectId)
), EquivalenceRelation AS (
 SELECT s1.objectId AS object1Id,
        s2.objectId AS object2Id
    FROM ObjectTraitWithCount s1
     INNER JOIN ObjectTraitWithCount s2
      USING(traitValue, traitCount)
   GROUP BY s1.objectId, s2.objectId
    HAVING COUNT(1) = MAX(traitCount)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId
   FROM EquivalenceRelation
    GROUP BY object1Id

15763 record(s) affected

Active Ingredient (mIsrf)

CREATE TABLE mw."mc_mIsrf" AS
WITH ObjectTrait AS (
  SELECT scoperInternalId AS objectId,
         playerInternalId AS traitValue
    FROM mw.Role_
   WHERE classCode = 'ACTI'
), ObjectTraitCount AS (
 SELECT objectId, 
        COUNT(1) AS traitCount
    FROM ObjectTrait
    GROUP BY objectId
), ObjectTraitWithCount AS (
 SELECT * FROM ObjectTrait
   INNER JOIN ObjectTraitCount
    USING(objectId)
), EquivalenceRelation AS (
 SELECT s1.objectId AS object1Id,
        s2.objectId AS object2Id
    FROM ObjectTraitWithCount s1
     INNER JOIN ObjectTraitWithCount s2
      USING(traitValue, traitCount)
   GROUP BY s1.objectId, s2.objectId
    HAVING COUNT(1) = MAX(traitCount)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId
   FROM EquivalenceRelation
    GROUP BY object1Id

15763 record(s) affected

ActiveIngredient? and Strength (mISrf)

CREATE TABLE mw."mc_mISrf" AS
WITH ObjectTrait AS (
  SELECT scoperInternalId AS objectId,
         playerInternalId AS traitValue1,
         quantity_numeratorNumber/quantity_denominatorNumber AS traitValue2, 
         CASE WHEN quantity_numeratorUnit IS NULL THEN '1' ELSE quantity_numeratorUnit END 
         ||CASE WHEN quantity_denominatorUnit <> '1' THEN '/'||quantity_denominatorUnit ELSE '' END 
         AS traitValue3
    FROM mw.Role_
   WHERE classCode = 'ACTI'
), ObjectTraitCount AS (
 SELECT objectId, 
        COUNT(1) AS traitCount
    FROM ObjectTrait
    GROUP BY objectId
), ObjectTraitWithCount AS (
 SELECT * FROM ObjectTrait
   INNER JOIN ObjectTraitCount
    USING(objectId)
), EquivalenceRelation AS (
 SELECT s1.objectId AS object1Id,
        s2.objectId AS object2Id
    FROM ObjectTraitWithCount s1
     INNER JOIN ObjectTraitWithCount s2
      USING(traitValue1, traitValue2, traitValue3, traitCount)
   GROUP BY s1.objectId, s2.objectId
    HAVING COUNT(1) = MAX(traitCount)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId
   FROM EquivalenceRelation
    GROUP BY object1Id

15654 record(s) affected

We seem to be missing a few strengths. Never mind.

Building the Refined Classes

Route

Active Moieties and Route (MisRf?)

CREATE TABLE mw."mc_MisRf" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_Misrf"
), DifferentiatingProperty AS (
  SELECT r.playerInternalId AS memberId, a.routeCode_code AS value
  FROM mw.Role_ r
    INNER JOIN mw.Participation p ON(p.roleInternalId = r.internalId)
    INNER JOIN mw.Act a ON(
           a.internalId = p.actInternalId 
       AND a.classCode='SBADM' 
       AND a.moodCode='DEF'
       AND a.routeCode_codeSystem = '2.16.840.1.113883.3.26.1.1')
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15685 record(s) affected

Wonder how this can be simplified and automated better.

Active Ingredients and Route (mIsRf)

CREATE TABLE mw."mc_mIsRf" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_mIsrf"
), DifferentiatingProperty AS (
  SELECT r.playerInternalId AS memberId, a.routeCode_code AS value
  FROM mw.Role_ r
    INNER JOIN mw.Participation p ON(p.roleInternalId = r.internalId)
    INNER JOIN mw.Act a ON(
           a.internalId = p.actInternalId 
       AND a.classCode='SBADM' 
       AND a.moodCode='DEF'
       AND a.routeCode_codeSystem = '2.16.840.1.113883.3.26.1.1')
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15685 record(s) affected

Active Ingredient with Strength and Route (mISRf)

CREATE TABLE mw."mc_mISRf" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_mISrf"
), DifferentiatingProperty AS (
  SELECT r.playerInternalId AS memberId, a.routeCode_code AS value
  FROM mw.Role_ r
    INNER JOIN mw.Participation p ON(p.roleInternalId = r.internalId)
    INNER JOIN mw.Act a ON(
           a.internalId = p.actInternalId 
       AND a.classCode='SBADM' 
       AND a.moodCode='DEF'
       AND a.routeCode_codeSystem = '2.16.840.1.113883.3.26.1.1')
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15577 record(s) affected

Form

Active Moiety, Form (MisrF)

CREATE TABLE mw."mc_MisrF" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_Misrf"
), DifferentiatingProperty AS (
  SELECT internalId as memberId, formCode_code as value
    FROM mw.Entity
    WHERE determinerCode = 'KIND'
      AND formCode_codeSystem = '2.16.840.1.113883.3.26.1.1'
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15763 record(s) affected

Active Ingredient, Form (mIsrF)

CREATE TABLE mw."mc_mIsrF" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_mIsrf"
), DifferentiatingProperty AS (
  SELECT internalId as memberId, formCode_code as value
    FROM mw.Entity
    WHERE determinerCode = 'KIND'
      AND formCode_codeSystem = '2.16.840.1.113883.3.26.1.1'
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15763 record(s) affected

Active Ingredient, Strength, Form (mISrF)

CREATE TABLE mw."mc_mISrF" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_mISrf"
), DifferentiatingProperty AS (
  SELECT internalId as memberId, formCode_code as value
    FROM mw.Entity
    WHERE determinerCode = 'KIND'
      AND formCode_codeSystem = '2.16.840.1.113883.3.26.1.1'
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        p.value AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

15654 record(s) affected

Now Combine the Classes

CREATE TABLE mw."mc_MisRF" AS
WITH BroaderClass1Member AS (
  SELECT classId, memberId FROM mw."mc_MisRf"
), BroaderClass2Member AS (
  SELECT classId, memberId FROM mw."mc_MisrF"
), CombinedClassMember AS (
  SELECT c1.classId AS class1Id,
         c2.classId AS class2Id,
         memberId
    FROM BroaderClass1Member c1
      INNER JOIN BroaderClass2Member c2 USING(memberId)
) SELECT MIN(m2.memberId) AS classId, 
       m1.memberId AS memberId,
       class1Id AS genus1Id, 
       class2Id AS genus2Id
    FROM CombinedClassMember m1 
      INNER JOIN CombinedClassMember m2 USING(class1Id, class2Id)
  GROUP BY class1Id, class2Id, m1.memberId

CREATE TABLE mw."mc_mIsRF" AS
WITH BroaderClass1Member AS (
  SELECT classId, memberId FROM mw."mc_mIsRf"
), BroaderClass2Member AS (
  SELECT classId, memberId FROM mw."mc_mIsrF"
), CombinedClassMember AS (
  SELECT c1.classId AS class1Id,
         c2.classId AS class2Id,
         memberId
    FROM BroaderClass1Member c1
      INNER JOIN BroaderClass2Member c2 USING(memberId)
)
SELECT MIN(m2.memberId) AS classId, 
       m1.memberId AS memberId,
       class1Id AS genus1Id, 
       class2Id AS genus2Id
    FROM CombinedClassMember m1 
      INNER JOIN CombinedClassMember m2 USING(class1Id, class2Id)
  GROUP BY class1Id, class2Id, m1.memberId

CREATE TABLE mw."mc_mISRF" AS
WITH BroaderClass1Member AS (
  SELECT classId, memberId FROM mw."mc_mISrF"
), BroaderClass2Member AS (
  SELECT classId, memberId FROM mw."mc_mISRf"
), CombinedClassMember AS (
  SELECT c1.classId AS class1Id,
         c2.classId AS class2Id,
         memberId
    FROM BroaderClass1Member c1
      INNER JOIN BroaderClass2Member c2 USING(memberId)
)
SELECT MIN(m2.memberId) AS classId, 
       m1.memberId AS memberId,
       class1Id AS genus1Id, 
       class2Id AS genus2Id
    FROM CombinedClassMember m1 
      INNER JOIN CombinedClassMember m2 USING(class1Id, class2Id)
  GROUP BY class1Id, class2Id, m1.memberId

Putting it all Together

Finally, and the hardest part is to keep all these names right. 

DROP TABLE mw.mc_EquivalenceClassMember
GO
CREATE TABLE mw.mc_EquivalenceClassMember AS
SELECT 'Misrf' AS category, classId, memberId, NULL AS difference FROM mw."mc_Misrf"
UNION ALL
SELECT 'mIsrf', classId, memberId, NULL FROM mw."mc_mIsrf"
UNION ALL
SELECT 'mISrf', classId, memberId, NULL FROM mw."mc_mISrf"
UNION ALL
SELECT 'MisRf', classId, memberId, difference FROM mw."mc_MisRf"
UNION ALL
SELECT 'MisrF', classId, memberId, difference FROM mw."mc_MisrF"
UNION ALL
SELECT 'mIsRf', classId, memberId, difference FROM mw."mc_mIsRf"
UNION ALL
SELECT 'mIsrF', classId, memberId, difference FROM mw."mc_mIsrF"
UNION ALL
SELECT 'mIsRf', classId, memberId, difference FROM mw."mc_mISRf"
UNION ALL
SELECT 'mIsrF', classId, memberId, difference FROM mw."mc_mISrF"
UNION ALL
SELECT 'MisRF', classId, memberId, NULL FROM mw."mc_MisRF"
UNION ALL
SELECT 'mIsRF', classId, memberId, NULL FROM mw."mc_mIsRF"
UNION ALL
SELECT 'mISRF', classId, memberId, NULL FROM mw."mc_mISRF"

188254 record(s) affected 

SELECT category, count(1) classCount, 
       sum(memberCount) totalMemberCount, 
       min(memberCount), median(memberCount), 
       round(avg(memberCount),3), round(stddev(memberCount),3), max(memberCount)
  FROM (
SELECT category, classId, count(1) memberCount
  FROM mw.mc_EquivalenceClassMember 
GROUP BY category, classId
) GROUP BY category
ORDER BY count(1) ASC
CATEGORYCLASSESMEMBERS
TOTALMINMEDAVGSTDDEVMAX
Misrf234415763126.72513.682185
mIsrf267215763125.89912.206185
MisR-f270515810125.84511.701174
MisrF-293715757125.3659.962150
MisRf?297516202125.44610.998174
MisrR-F-319915764124.9289.027150
MisrF425515763123.7056.217111
MisRF442515814123.5746.003111
mIsRF465015798123.3975.662111
mISrf715815654112.1873.11154
mIsRf739732295124.3668.866186
mIsrF837231417123.7536.315138
mISRF838915640111.8642.44449

The checks at the end of DrugBaseEvaluationCombiningClasses all pass fine. So we are good now. The only thing that's not quite right is that we missed some drugs which were not classified. But instead of fixing this we should rather exclude those cases.

One for All

Here is a single query that does all of the above. 

DROP TABLE mc_class
GO
CREATE TABLE mc_class AS
WITH ObjectTrait AS (
  SELECT DISTINCT 'Mis' AS category,
         i.scoperInternalId AS objectId,
         m.playerInternalId AS traitValue,
         0 AS traitValue2,
         ' ' AS traitValue3
    FROM mw.Role_ i
      INNER JOIN mw.Role_ m ON(m.scoperInternalId = i.playerInternalId)
   WHERE i.classCode = 'ACTI' AND m.classCode = 'ACTM'
 UNION ALL
  SELECT 'mIs' AS category,
         scoperInternalId AS objectId,
         playerInternalId AS traitValue,
         0 AS traitValue2,
         ' ' AS traitValue3
    FROM mw.Role_
   WHERE classCode = 'ACTI' 
 UNION ALL
  SELECT 'mIS' AS category,
         scoperInternalId AS objectId,
         playerInternalId AS traitValue1,
         quantity_numeratorNumber/quantity_denominatorNumber AS traitValue2, 
         CASE WHEN quantity_numeratorUnit IS NULL THEN '1' ELSE quantity_numeratorUnit END 
         ||CASE WHEN quantity_denominatorUnit <> '1' THEN '/'||quantity_denominatorUnit ELSE '' END 
         AS traitValue3
    FROM mw.Role_
   WHERE classCode = 'ACTI'
), ObjectTraitCount AS (
 SELECT category, objectId, COUNT(1) AS traitCount
   FROM ObjectTrait
   GROUP BY category, objectId
), ObjectTraitWithCount AS (
 SELECT * FROM ObjectTrait
   INNER JOIN ObjectTraitCount
    USING(category, objectId)
), EquivalenceRelation AS (
 SELECT category,
        s1.objectId AS object1Id,
        s2.objectId AS object2Id,
        COUNT(1) AS traitCount
    FROM ObjectTraitWithCount s1
     INNER JOIN ObjectTraitWithCount s2
      USING(category, traitCount, traitValue, traitValue2, traitValue3)
   GROUP BY category, s1.objectId, s2.objectId
    HAVING COUNT(1) = MAX(traitCount)
), BroaderClassMember AS (
  SELECT category,
         MIN(object2Id) AS classId,
         object1Id AS memberId,
         traitCount
   FROM EquivalenceRelation
    GROUP BY category, traitCount, object1Id
), DifferentiatingProperty AS (
  SELECT 'r' AS category1, 'f' AS category2, memberId, ' ' as value1, ' ' as value2
    FROM BroaderClassMember
    GROUP BY memberId
 UNION ALL
  SELECT 'r' AS category1, 'F' AS category2, internalId as memberId, ' ' as value1, formCode_code as value2
    FROM mw.Entity
    WHERE determinerCode = 'KIND'
      AND formCode_codeSystem = '2.16.840.1.113883.3.26.1.1'
 UNION ALL
  SELECT 'R' AS category1, 'f' AS category2, r.playerInternalId AS memberId, a.routeCode_code AS value1, ' ' as value2
  FROM mw.Role_ r
    INNER JOIN mw.Participation p ON(p.roleInternalId = r.internalId)
    INNER JOIN mw.Act a ON(
           a.internalId = p.actInternalId 
       AND a.classCode='SBADM' 
       AND a.moodCode='DEF'
       AND a.routeCode_codeSystem = '2.16.840.1.113883.3.26.1.1')
), ClassMemberDifference AS (
 SELECT category, category1, category2,
        c.classId AS genusId,
        memberId AS objectId,
        traitCount,
        p.value1 AS difference1,
        p.value2 AS difference2
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
     USING(memberId)
), EquivalenceRelation2 AS (
 SELECT category, category1, category2,
        m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, traitCount, difference1, difference2
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(category, category1, category2, genusId, traitCount, difference1, difference2)
), BroaderClassMember1 AS (
 SELECT category, category1, category2,
         MIN(object2Id) AS classId, object1Id AS memberId,
         traitCount, difference1, difference2
   FROM EquivalenceRelation2
    GROUP BY category, category1, category2, object1Id, traitCount, difference1, difference2
), CombinedClassMember AS (
  SELECT category, c1.category1, c2.category2,
         c1.classId AS class1Id,
         c2.classId AS class2Id,
         memberId, traitCount, c1.difference1, c2.difference2
    FROM BroaderClassMember1 c1
     INNER JOIN BroaderClassMember1 c2 USING(category, memberId, traitCount)
) SELECT category, category1, category2,
         MIN(m2.memberId) AS classId, 
         m1.memberId AS memberId,
         class1Id AS genus1Id, 
         class2Id AS genus2Id,
         traitCount, difference1, difference2
    FROM CombinedClassMember m1 
      INNER JOIN CombinedClassMember m2 USING(category, category1, category2, class1Id, class2Id, traitCount, difference1, difference2)
  GROUP BY category, category1, category2, class1Id, class2Id, m1.memberId, traitCount, difference1, difference2

Now here is the generalization discussed below, but with the sophistication that combination formulation will be with strength and simple drugs as moiety only. 

WITH BroaderClassMember AS (
  SELECT category, category1, category2, classId, memberId, 
         traitCount, difference1, difference2
    FROM mc_class 
   WHERE ((traitCount=1 AND category='Mis') OR (traitCount>1 AND category='mIS'))
      AND category1='R' AND category2='F'
), DifferenceGeneralization1 AS (
  SELECT source_code AS difference1,
         target_code AS difference1Class
    FROM mw.Concept_Relationship
      WHERE type_code='ISA'
        AND source_codeSystem = '2.16.840.1.113883.3.26.1.1'
        AND target_codeSystem = '1.3.6.1.4.1.12009.4.1.12'
), DifferenceGeneralization2 AS (
  SELECT source_code AS difference2,
         target_code AS difference2Class
    FROM mw.Concept_Relationship
      WHERE type_code='ISA'
        AND source_codeSystem = '2.16.840.1.113883.3.26.1.1'
        AND target_codeSystem = '1.3.6.1.4.1.12009.4.1.13'
), ClassMemberDifference AS (
 SELECT 'MIS' AS category, category1||'+' AS category1, category2||'+' AS category2,
        c.classId AS genusId,
        memberId AS objectId,
        traitCount,
        g1.difference1Class AS difference1,
        g2.difference2Class AS difference2
   FROM BroaderClassMember c
    INNER JOIN DifferenceGeneralization1 g1 USING(difference1)
    INNER JOIN DifferenceGeneralization2 g2 USING(difference2)
), EquivalenceRelation AS (
 SELECT category, category1, category2,
        m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, traitCount, difference1, difference2
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(category, category1, category2, genusId, traitCount, difference1, difference2)
) SELECT category, category1, category2,
         MIN(object2Id) AS classId, 
         object1Id AS memberId,
         traitCount, difference1, difference2
   FROM EquivalenceRelation
    GROUP BY category, category1, category2, object1Id, traitCount, difference1, difference2

So What?

The reason for this excercise is so that we can reduce clutter in the medicine name selection. We can create the drug classes as generic formulations, again in SPL format, and adding act relationships to their members. We do not need all those many classifications, only those named generic classes which we like to include on the medication selection.

For this, we need to add one more higher level class, which would combine forms into only the variances:

  • liquid
  • solid

and the route into

  • oral
  • parenteral
  • topical

Not sure what to do with rectal, vaginal, urethral, and also otic and nasal. Those can sometimes be considered topical and other times systemic. Buccal and sublingual. Sublingual definitely is systemic.

So, we define for route

  • systemic
    • oral
    • parenteral (incl. sublingual, rectal, transdermal)
      • injection (incl. infusion, anything requiring a percutanous access) - however, the injection property is derived from the dose form.
  • topical (incl. nasal, otic)
  • special (anything that requires special skills and procedures, e.g. intraspinal, intraocular, etc.)

For form we use:

  • fluid - all non-solids non-countables, including gas and powder
    • liquid - incl. solutions, suspensions, including solid susbtances reconstituted into liquids for administration
  • countable fluid - Any fluid, including liquid, which can still be dosed by counting. Includes metered sprays or tablets for solution.
  • solid - all solid forms, hard or soft, as long as they maintain integrity in handling and can therefore be counted.

With just this classification we can create the following combinations

The notion of "countable fluid" is not well understood though. Hence we probably need to translate it to better understood concepts.

  • systemic (only for NEC)
    • oral (only for NEC)
      • oral solid
      • oral liquid
      • oral countable fluid (liquid each?)
      • oral metered dose
    • parenteral (only for NEC)
      • parenteral injectable liquid
      • parenteral liquid
      • parenteral solid
      • parenteral countable fluid (liquid each?)
      • parenteral metered dose
  • topical (only for NEC)
    • topical solid
    • topical liquid
    • topical drops
  • special (all special items lumped under one category)

Before we defined this ourselves, the NCI Thesaurus was considered as a source for a rational and usable classification of dose form and route. However, the categorizations appear to have many flaws and effectively do not support our purposes for very rough classes used for prescribing. In the end we will have to continue applying our own best guess categorization.

Forming The Hierarchies Based on Property Value Abstractions

Form Code Class

DROP TABLE mw."mc_MisrF-"
GO
CREATE TABLE mw."mc_MisrF-" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_Misrf"
), DifferentiatingProperty AS (
  SELECT internalId as memberId, formCode_code as value
    FROM mw.Entity
    WHERE determinerCode = 'KIND'
      AND formCode_codeSystem = '2.16.840.1.113883.3.26.1.1'
), PropertyGeneralization AS (
  SELECT source_code AS value,
         target_code AS valueClass
    FROM mw.Concept_Relationship
      WHERE type_code='ISA'
        AND source_codeSystem = '2.16.840.1.113883.3.26.1.1'
        AND target_codeSystem = '1.3.6.1.4.1.12009.4.1.13'
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        g.valueClass AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
    INNER JOIN PropertyGeneralization g USING(value)
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

Route Code Class

DROP TABLE mw."mc_MisR-f" 
GO
CREATE TABLE mw."mc_MisR-f" AS
WITH BroaderClassMember AS (
  SELECT classId, memberId FROM mw."mc_Misrf"
), DifferentiatingProperty AS (
  SELECT r.playerInternalId AS memberId, a.routeCode_code AS value
  FROM mw.Role_ r
    INNER JOIN mw.Participation p ON(p.roleInternalId = r.internalId)
    INNER JOIN mw.Act a ON(
           a.internalId = p.actInternalId 
       AND a.classCode='SBADM' 
       AND a.moodCode='DEF'
       AND a.routeCode_codeSystem = '2.16.840.1.113883.3.26.1.1')
), PropertyGeneralization AS (
  SELECT source_code AS value,
         target_code AS valueClass
    FROM mw.Concept_Relationship
      WHERE type_code='ISA'
        AND source_codeSystem = '2.16.840.1.113883.3.26.1.1'
        AND target_codeSystem = '1.3.6.1.4.1.12009.4.1.12'
), ClassMemberDifference AS (
 SELECT c.classId AS genusId,
        memberId AS objectId,
        g.valueClass AS difference
   FROM BroaderClassMember c
    INNER JOIN DifferentiatingProperty p
    INNER JOIN PropertyGeneralization g USING(value)
     USING(memberId)
), EquivalenceRelation AS (
 SELECT m1.objectId AS object1Id,
        m2.objectId AS object2Id,
        genusId, difference
   FROM ClassMemberDifference m1
    INNER JOIN ClassMemberDifference m2
     USING(genusId, difference)
) SELECT object1Id AS memberId,
         MIN(object2Id) AS classId, difference
   FROM EquivalenceRelation
    GROUP BY object1Id, difference

And combine 

DROP TABLE mw."mc_MisR-F-"
GO
CREATE TABLE mw."mc_MisR-F-" AS
WITH BroaderClass1Member AS (
  SELECT classId, memberId, difference FROM mw."mc_MisrF-"
), BroaderClass2Member AS (
  SELECT classId, memberId, difference FROM mw."mc_MisR-f"
), CombinedClassMember AS (
  SELECT c1.classId AS class1Id, c1.difference AS difference1,
         c2.classId AS class2Id, c2.difference AS difference2,
         memberId
    FROM BroaderClass1Member c1
      INNER JOIN BroaderClass2Member c2 USING(memberId)
)
SELECT MIN(m2.memberId) AS classId, 
       m1.memberId AS memberId,
       class1Id AS genus1Id, difference1,
       class2Id AS genus2Id, difference2
    FROM CombinedClassMember m1 
      INNER JOIN CombinedClassMember m2 USING(class1Id, class2Id, difference1, difference2)
  GROUP BY class1Id, class2Id, difference1, difference2, m1.memberId

Add To Our Central Class Membership Relation

ALTER TABLE mw.mc_EquivalenceClassMember 
  MODIFY category VARCHAR2(16)
  ADD (difference2 VARCHAR2(64))
GO
DELETE FROM mw.mc_EquivalenceClassMember WHERE category IN ('MisrF-', 'MisR-f', 'MisR-F-')
GO
INSERT INTO mw.mc_EquivalenceClassMember
SELECT 'MisrF-', classId, memberId, difference, NULL FROM mw."mc_MisrF-"
UNION ALL
SELECT 'MisR-f', classId, memberId, NULL, difference FROM mw."mc_MisR-f"
UNION ALL
SELECT 'MisR-F-', classId, memberId, difference1, difference2 FROM mw."mc_MisR-F-"
ROUTEFORMCOUNT
oralsolid7422
oralliquid2664
parenteralinjectable liquid2466
topicalliquid1646
topicalsolid811
parenteralsolid315
parenteralliquid180
specialinjectable liquid102
topicalfluid57
parenteralfluid38
parenteralmetered dose24
topicalinjectable liquid21
specialliquid14
topicalmetered dose13
topicaldrops9
specialsolid7
oralfluid6
oralmetered dose3
specialfluid3
oralliquid each2
oralinjectable liquid1

The last one is rather funny, but otherwise it seems pretty nice.

Create Generic Names

Two forms:

  • "amoxicillin oral solid"
  • "tylenol (acetaminophen) oral liquid"

Strength would never be in the list. It could be found when choosing the specific formulation.

The second form cannot be made from the classes. The only reason for a brand name should be the original innovators brand name, not some generic manufacturer. It would be nice to have a source of original innovators for each drug class. That way we can also have "Tylenol III (acetaminophen and codein) oral solid", etc.

Check out NewGenericNames for an updated approach. 

DROP TABLE mw.mc_Class
GO
CREATE TABLE mw.mc_Class AS
WITH IngredientMoieties AS (
  SELECT i.scoperInternalId parentId, n.trivialname AS name
    FROM mw.Role_ i
      INNER JOIN mw.Role_ m ON(m.scoperInternalId = i.playerInternalId AND m.classCode = 'ACTM')
      INNER JOIN mw.Entity_name n ON(n.entityInternalId = m.playerInternalId)
    WHERE i.classCode='ACTI'
), GenericNameAssembly AS (
  SELECT parentId, STRINGJOIN(' and '||name) AS items
    FROM IngredientMoieties
    GROUP BY parentId
), GenericName AS (
  SELECT parentId AS objectId, SUBSTR(items,LENGTH(' and  ')) as name
    FROM GenericNameAssembly
), NDC_SCD AS (
  SELECT substr(ndc,1,5)||'-'||substr(ndc,6,4) AS ndc, scd_rxcui
    FROM rxn_ndc_cleanup
), NDC_SBD AS (
  SELECT ndc, scd_rxcui, rxcui, str
    FROM NDC_SCD
      INNER JOIN umls.RXNREL ON(rxcui2 = scd_rxcui AND RELA='has_tradename')
      INNER JOIN umls.RXNCONSO ON(rxcui = rxcui1)
    WHERE SAB='RXNORM' AND TTY='SBD' AND LAT='ENG'
), NDC_TRADENAME1 AS (
  SELECT r.*, 
         INSTR(str,'[',-1) AS tradename_start,
         INSTR(str,']',-1) AS tradename_end
    FROM NDC_SBD r
), NDC_TRADENAME2 AS (
  SELECT DISTINCT 
         ndc,
         LOWER(SUBSTR(str, tradename_start+1, tradename_end-tradename_start-1)) AS tradename
    FROM NDC_TRADENAME1 r
), NDC_TRADENAME AS (
  SELECT ndc, count(1), SUBSTR(STRINGJOIN(' or '||tradename),LENGTH(' or ')+1) AS tradenames
    FROM NDC_TRADENAME2
    GROUP BY NDC
    HAVING count(1) < 3
), RawClass AS ( 
  SELECT DISTINCT classId, difference, difference2
    FROM mw.mc_EquivalenceClassMember 
    WHERE category = 'MisR-F-'
), ClassStage AS ( 
  SELECT classId, n.name, t.tradenames, difference2 AS route, difference AS form, 
         REPLACE(c.code_code,CHR(14844051),'-') AS representative_code, 
         c.code_codeSystem AS representative_codeSystem
    FROM RawClass
      INNER JOIN mw.Entity c ON(c.internalId = classId)
      INNER JOIN GenericName n ON(n.objectId = classId)
      LEFT OUTER JOIN NDC_TRADENAME t ON(t.ndc = c.code_code)
    ORDER BY classId
) SELECT * FROM ClassStage

3223 record(s) affected

Nice and compact, this is ready to hand over to db2xml! 

DROP TABLE mw.mc_ClassIngredientMoiety
GO
CREATE TABLE mw.mc_ClassIngredientMoiety AS
SELECT i.scoperInternalId classId, n.trivialname AS name, e.code_code, e.code_codeSystem
  FROM mw.Role_ i
    INNER JOIN mw.Role_ m ON(m.scoperInternalId = i.playerInternalId AND m.classCode = 'ACTM')
    INNER JOIN mw.Entity_name n ON(n.entityInternalId = m.playerInternalId)
    INNER JOIN mw.Entity e ON(e.internalId = m.playerInternalId)
  WHERE i.classCode='ACTI'
GO
CREATE INDEX mw.mc_ClassIngredientMoiety_idx ON mw.mc_ClassIngredientMoiety(classId)

GO
DROP TABLE mw.mc_ClassMember
GO
CREATE TABLE mw.mc_ClassMember AS
SELECT classId, memberId, code_code, code_codeSystem
  FROM mw.mc_EquivalenceClassMember 
    INNER JOIN mw.Entity ON(internalId = memberId)
  WHERE category = 'MisR-F-'
GO
CREATE INDEX mw.mc_ClassMember_idx ON mw.mc_ClassMember(classId)