Using The NDF-RT

The key connections of NDFRT and HL7-RIM/SPL today are through

  • UNII, and
  • SNOMED CT

NDF_KIND is a mixed bag, but some of them are clearly mappable to active moieties UNIIs. For those that are (e.g., METOPROLOL) the has_Ingredient relationship to INGREDIENT_KIND is a simple identity relationship of an NDF_KIND and an INGREDIENT_KIND. That way we can find ingredient combination terms in NDF_KIND terms that have multiple has_Ingredient links. This can simplify the odd NDFRT structure into a more logic structure. Most domain knowledge (may_*, CI_*, etc.) hangs off these simple ingredient-like NDF_KIND terms that can be mapped to UNII. Diseases are already mapped to SNOMED. So we can now enrich SPLs with this data all based on active moieties. We simply summarize and merge the moiety knowledge on the product level. There they also need to be reconciliated (if two ingredients contribute similar indications).

In our application, we may be content with taking these domain knowledge relationships from the active moieties only. This may be more manageable. Might be more manageable for FDA also.

UNII to NDF-RT

See new separate page UNII to NDF-RT.

SPL Active Moieties and their coverage in NDFRT

Let's make an overview of what roles we get from just these mapped active moieties: 

SELECT name, scount, ncount, ROUND(scount/ncount*100,2) as spct, ucount, 
       ROUND(ucount/(SELECT COUNT(1) FROM UNII_NDFRT_MAP)*100,2) AS upct
  FROM (
SELECT d.name, count(1) AS ncount
  FROM umls.ndfrt_concept s 
    INNER JOIN umls.ndfrt_role r ON(s.id = r.source_id)
    INNER JOIN umls.ndfrt_role_def d ON(d.id = r.role_def_id)
  WHERE r.target_id IS NOT NULL
    AND s.kind = 9
  GROUP BY d.name
) a INNER JOIN (
SELECT name, count(1) as ucount, sum(scount) as scount FROM (
SELECT d.name, count(1) as scount
  FROM UMLS.UNII_NDFRT_MAP u
    INNER JOIN UMLS.NDFRT_ROLE r ON(r.source_id = u.id)
    INNER JOIN UMLS.NDFRT_ROLE_DEF d ON(d.id = r.role_def_id)
  WHERE r.target_id IS NOT NULL
  GROUP BY d.name, u.unii_code
) GROUP BY name
) s USING(name) 
  ORDER BY name ASC, scount DESC
NAMESCOUNTNCOUNTSPCTUCOUNTUPCT
CI_ChemClass19528368.9725.62
CI_MoA508161.73493.83
CI_PE336154.1312.42
CI_with2331434653.6472656.67
VA_Product_Component130757010.08
definingConcept8421379360.6184165.65
effect_may_be_inhibited_by1110010.08
has_Ingredient727471915.4172756.75
has_MoA936195147.9874658.24
has_PE1540307950.0272156.28
has_PK485194.12251.95
has_active_metabolites347530.23
induces3613227.27292.26
may_diagnose3113522.96211.64
may_prevent28972140.0818314.29
may_treat3201559057.2670354.88
metabolized_by_recip_structure566487.5201.56
site_of_metabolism222588211.64

Obviously we are missing something here. Let's just focus on may_treat (R35) to find out:

  • 57.26 of their indications apply to our active moieties
  • 54.88 of our active moieties have such indication

So we loose about >40% of content in both ways. Why is that?

  1. it is possible that SPL moieties only cover ~70% of NDFRT moieties
  2. it is possible that NDFRT roles are associated with somehow related medicines (e.g. precise ingredients rather than moieties) and we loose those by only looking at moieties.

To be sure, there are often multiple may_treat roles per NDFRT concept: 

SELECT count(1) count, min(rcount), median(rcount), avg(rcount), max(rcount)
  FROM (
SELECT source_id, count(1) rcount
  FROM UMLS.NDFRT_ROLE
  WHERE ROLE_DEF_ID = 'R35'
  GROUP BY source_id
)
  • 1473 have any may_treat
  • minimum 1
  • median 3
  • average 3.79
  • maximum 30

This may help us to explore the mismatches: 

SELECT source_id, unii_code, c.name, n.name, min(r.rcount), max(r.rcount)
  FROM (SELECT source_id, count(DISTINCT target_id) as rcount 
          FROM UMLS.NDFRT_ROLE WHERE ROLE_DEF_ID IN ('R35', 'R43', 'R39', 'R41')
          GROUP BY source_id) r
    FULL OUTER JOIN UMLS.UNII_NDFRT_MAP u ON(r.source_id = u.id)
    LEFT OUTER JOIN UMLS.NDFRT_CONCEPT c ON(c.id = r.source_id)
    LEFT OUTER JOIN (SELECT code, min(displayName) as name FROM UNII GROUP BY code) n ON(n.code = u.unii_code)
  GROUP BY source_id, unii_code, c.name, n.name
  ORDER BY coalesce(c.name, n.name)

From inspection we do not see a specific pattern, i.e., no large scale systematic misses which seem to be preventable. There are known issues with electrolytes and with insulins, but those appear to be of minor value to fix. There is a larger set of NDFRT concepts not yet reflected in UNII and a small set of UNIIs not yet in NDFRT, but none of this we can do anything about.

So, let's conclude with numbers: How many of our active moieties have any indications? 

SELECT unii_code, count(source_id) count, min(rcount), median(rcount), avg(rcount), max(rcount)
  FROM (
SELECT source_id, unii_code /*, c.name, n.name*/, avg(r.rcount) rcount
  FROM (SELECT source_id, count(DISTINCT target_id) as rcount 
          FROM UMLS.NDFRT_ROLE WHERE ROLE_DEF_ID IN ('R35', 'R43', 'R39', 'R41')
          GROUP BY source_id) r
    FULL OUTER JOIN UMLS.UNII_NDFRT_MAP u ON(r.source_id = u.id)
   -- LEFT OUTER JOIN UMLS.NDFRT_CONCEPT c ON(c.id = r.source_id)
   -- LEFT OUTER JOIN (SELECT code, min(displayName) as name FROM UNII GROUP BY code) n ON(n.code = u.unii_code)
  GROUP BY unii_code, source_id --, c.name, n.name
) GROUP BY unii_code
  • 118 UNIIs have no indication.
  • 841 UNIIs exist
  • 843 NDFRT indications have no UNII

Now let's move on.

DISEASE_KIND to SNOMED mapping

There are SNOMED mappings in NDFRT, so this might be trivial. 

SELECT name, term_has_mapping, mapping_is_finding, count(1)
  FROM (
SELECT d.name, 
       CASE WHEN r.target_value IS NULL THEN 0 ELSE 1 END term_has_mapping,
       CASE WHEN s.code IS NULL THEN 0 ELSE 1 END mapping_is_finding
  FROM umls.ndfrt_role c
    INNER JOIN umls.ndfrt_role_def d ON(d.id = c.role_def_id)
    LEFT OUTER JOIN umls.ndfrt_role r ON(c.target_id = r.source_id AND r.role_def_id = 'P262795')
    LEFT OUTER JOIN umls.snomed_finding s ON(s.code = r.target_value)
  WHERE c.role_def_id IN ('R37', 'R41', 'R43', 'R39', 'R35')
) GROUP BY ROLLUP(name, term_has_mapping, mapping_is_finding)

And of course we have losses everywhere:

ROLETERM_HAS_MAPPINGMAPPING_IS_FINDINGCOUNT
CI_with0085
CI_with0*85
CI_with10559
CI_with1110815
CI_with1*11374
CI_with**11459
induces007
induces0*7
induces108
induces11262
induces1*270
induces**277
may_treat00510
may_treat0*510
may_treat10270
may_treat1110244
may_treat1*10514
may_treat**11024
may_prevent0072
may_prevent0*72
may_prevent1044
may_prevent111371
may_prevent1*1415
may_prevent**1487
may_diagnose0012
may_diagnose0*12
may_diagnose102
may_diagnose11312
may_diagnose1*314
may_diagnose**326
***24573

So we need to check which terms are not mapped properly? And indeed there are issues. 

SELECT d.name, c.target_id, cc.name, r.target_value, m.STR, m.tty
  FROM umls.ndfrt_role c
    INNER JOIN umls.ndfrt_role_def d ON(d.id = c.role_def_id)
    INNER JOIN umls.ndfrt_concept cc ON(cc.id = c.target_id)
    INNER JOIN umls.ndfrt_role r ON(c.target_id = r.source_id AND r.role_def_id = 'P262795')
    LEFT OUTER JOIN umls.snomed_finding s ON(s.code = r.target_value)
    LEFT OUTER JOIN umls.mrconso m ON(m.code = r.target_value AND m.sab = 'SNOMEDCT' AND m.tty IN ('FN', 'OF'))
  WHERE c.role_def_id IN ('R37', 'R41', 'R43', 'R39', 'R35')
    AND r.target_value IS NOT NULL
    AND s.code IS NULL
GROUP BY d.name, c.target_id, cc.name, r.target_value, m.STR, m.tty
ROLEIDNAMESNOMEDSNOMED NAMETTY
may_treat791Adenocarcinoma [Disease/Finding]35917007Adenocarcinoma, no subtype (morphologic abnormality)FN
may_treat799Adenoma [Disease/Finding]32048006Adenoma, no subtype (morphologic abnormality)FN
CI_with811Adhesions [Disease/Finding]42685002Adhesion (morphologic abnormality)FN
CI_with31787Adolescent [Disease/Finding]133937008Adolescent (person)FN
may_treat1293Bites, Human [Disease/Finding]43028005Human bite (morphologic abnormality)FN
may_prevent1473Calculi [Disease/Finding]56381008Calculus (morphologic abnormality)FN
may_prevent1473Calculi [Disease/Finding]125243009Single calculus (morphologic abnormality)FN
may_treat7659Carcinoma, Medullary [Disease/Finding]32913002Medullary carcinoma (morphologic abnormality)FN
CI_with1507Carcinoma [Disease/Finding]68453008Carcinoma, no subtype (morphologic abnormality)FN
may_treat1507Carcinoma [Disease/Finding]68453008Carcinoma, no subtype (morphologic abnormality)FN
CI_with31769Child [Disease/Finding]410601007Childhood age person (person)FN
induces1985Death [Disease/Finding]419620001Death (event)FN
may_prevent1985Death [Disease/Finding]419620001Death (event)FN
CI_with7177Diabetic Ketoacidosis [Disease/Finding]24927004Diabetes with ketoacidosis (disorder)OF
may_diagnose7177Diabetic Ketoacidosis [Disease/Finding]24927004Diabetes with ketoacidosis (disorder)OF
may_treat7177Diabetic Ketoacidosis [Disease/Finding]24927004Diabetes with ketoacidosis (disorder)OF
CI_with2093Diabetic Nephropathies [Disease/Finding]21858001Diabetes with renal manifestations (disorder)OF
may_prevent2093Diabetic Nephropathies [Disease/Finding]21858001Diabetes with renal manifestations (disorder)OF
may_treat2093Diabetic Nephropathies [Disease/Finding]21858001Diabetes with renal manifestations (disorder)OF
may_treat2095Diabetic Neuropathies [Disease/Finding]267382005Diabetes mellitus with neurological manifestation (disorder)OF
may_treat2203Dwarfism [Disease/Finding]190584003Constitutional short stature (disorder)OF
CI_with2293Edema [Disease/Finding]20741006Hydrops (morphologic abnormality)FN
CI_with2293Edema [Disease/Finding]79654002Edema (morphologic abnormality)FN
may_treat2293Edema [Disease/Finding]79654002Edema (morphologic abnormality)FN
may_treat2293Edema [Disease/Finding]20741006Hydrops (morphologic abnormality)FN
CI_with31785Elderly adult [Disease/Finding]207528003[D]Senescence (situation)FN
CI_with31785Elderly adult [Disease/Finding]207528003[D]Senescence (context-dependent category)OF
CI_with31785Elderly adult [Disease/Finding]271872005Old age (qualifier value)FN
CI_with31785Elderly adult [Disease/Finding]207527008[D]Old age (situation)FN
CI_with31785Elderly adult [Disease/Finding]207527008[D]Old age (context-dependent category)OF
may_treat2413Ependymoma [Disease/Finding]57706008Ependymoma, no ICD-O subtype (morphologic abnormality)FN
may_treat2675Fibrosarcoma [Disease/Finding]53654007Fibrosarcoma (morphologic abnormality)FN
may_treat2677Fibrosis [Disease/Finding]112674009Fibrosis (morphologic abnormality)FN
induces2701Flatulence [Disease/Finding]249505007Farting (finding)OF
induces2701Flatulence [Disease/Finding]275298000Wind symptom (context-dependent category)OF
induces2701Flatulence [Disease/Finding]275298000Wind symptom (finding)OF
may_prevent2701Flatulence [Disease/Finding]275298000Wind symptom (context-dependent category)OF
may_prevent2701Flatulence [Disease/Finding]275298000Wind symptom (finding)OF
may_prevent2701Flatulence [Disease/Finding]249505007Farting (finding)OF
may_treat2701Flatulence [Disease/Finding]275298000Wind symptom (finding)OF
may_treat2701Flatulence [Disease/Finding]275298000Wind symptom (context-dependent category)OF
may_treat2701Flatulence [Disease/Finding]249505007Farting (finding)OF
may_treat2873Glioblastoma [Disease/Finding]63634009Glioblastoma (morphologic abnormality)FN
may_treat2875Glioma [Disease/Finding]115240006Glioma (morphologic abnormality)FN
may_treat2949Granuloma [Disease/Finding]45647009Granuloma (morphologic abnormality)FN
CI_with3211Hernia [Disease/Finding]414403008Herniated structure (morphologic abnormality)FN
may_treat3345Hyperemia [Disease/Finding]30098003Hyperemia (morphologic abnormality)FN
may_treat7419Hypereosinophilic Syndrome [Disease/Finding]128835008Hypereosinophilic syndrome/chronic eosinophilic leukemia (morphologic abnormality)FN
may_treat7419Hypereosinophilic Syndrome [Disease/Finding]128835008Hypereosinophilic syndrome (morphologic abnormality)OF
CI_with3399Hypersensitivity [Disease/Finding]127072000Allergic disorder (disorder)OF
may_treat3399Hypersensitivity [Disease/Finding]127072000Allergic disorder (disorder)OF
may_diagnose3515Immune System Diseases [Disease/Finding]41266007Disorder of immune system (disorder)OF
may_diagnose3515Immune System Diseases [Disease/Finding]41266007Disease of immune system (disorder)OF
may_diagnose3515Immune System Diseases [Disease/Finding]41266007Disorder of immune system (navigational concept)FN
may_treat3515Immune System Diseases [Disease/Finding]41266007Disorder of immune system (disorder)OF
may_treat3515Immune System Diseases [Disease/Finding]41266007Disease of immune system (disorder)OF
may_treat3515Immune System Diseases [Disease/Finding]41266007Disorder of immune system (navigational concept)FN
CI_with31765Infant [Disease/Finding]133931009Infant (person)FN
may_treat3539Infarction [Disease/Finding]55641003Infarct (morphologic abnormality)FN
induces3553Inflammation [Disease/Finding]23583003Inflammation (morphologic abnormality)FN
may_treat3553Inflammation [Disease/Finding]23583003Inflammation (morphologic abnormality)FN
may_treat3783Leiomyoma [Disease/Finding]189793008[M]Leiomyomatous neoplasm NOS (morphologic abnormality)FN
may_treat3783Leiomyoma [Disease/Finding]189787000[M]Leiomyomatous neoplasms (morphologic abnormality)FN
may_treat3783Leiomyoma [Disease/Finding]189788005[M]Leiomyoma NOS (morphologic abnormality)FN
may_treat6715Leukemia, Basophilic, Acute [Disease/Finding]69077002Acute basophilic leukemia (morphologic abnormality)FN
may_treat6713Leukemia, Nonlymphocytic, Acute [Disease/Finding]413443009Acute myeloid leukemia - category (morphologic abnormality)FN
may_treat6713Leukemia, Nonlymphocytic, Acute [Disease/Finding]17788007Acute myeloid leukemia, no ICD-O subtype (morphologic abnormality)FN
may_treat8423Lithiasis [Disease/Finding]66058000Lithiasis (morphologic abnormality)FN
may_treat4015Macular Degeneration [Disease/Finding]302891003Macular degeneration (disorder)OF
may_treat4107Medulloblastoma [Disease/Finding]83217000Medulloblastoma (morphologic abnormality)FN
may_treat7757Melanoma, Amelanotic [Disease/Finding]70594002Amelanotic melanoma (morphologic abnormality)FN
may_treat31749Menopause [Disease/Finding]161712005Menopause, function (observable entity)FN
may_treat31749Menopause [Disease/Finding]161712005Menopause (qualifier value)OF
may_treat7637Mesothelioma, Cystic [Disease/Finding]86562005Cystic mesothelioma (morphologic abnormality)FN
may_treat4185Mesothelioma [Disease/Finding]62064005Mesothelioma, malignant (morphologic abnormality)FN
may_treat4325Mycoplasma Infections [Disease/Finding]186462007Eaton's agent infection (disorder)OF
may_treat4341Myeloid Metaplasia [Disease/Finding]82513007Myeloid metaplasia (morphologic abnormality)FN
CI_with4343Myeloproliferative Disorders [Disease/Finding]414794006Myeloproliferative disorder (morphologic abnormality)FN
CI_with4343Myeloproliferative Disorders [Disease/Finding]128925001Myeloproliferative disease, no ICD-O subtype (morphologic abnormality)FN
CI_with4343Myeloproliferative Disorders [Disease/Finding]414792005Myeloid neoplasm (morphologic abnormality)FN
CI_with4343Myeloproliferative Disorders [Disease/Finding]128925001Myeloproliferative disease (morphologic abnormality)OF
may_treat4343Myeloproliferative Disorders [Disease/Finding]414794006Myeloproliferative disorder (morphologic abnormality)FN
may_treat4343Myeloproliferative Disorders [Disease/Finding]414792005Myeloid neoplasm (morphologic abnormality)FN
may_treat4343Myeloproliferative Disorders [Disease/Finding]128925001Myeloproliferative disease (morphologic abnormality)OF
may_treat4343Myeloproliferative Disorders [Disease/Finding]128925001Myeloproliferative disease, no ICD-O subtype (morphologic abnormality)FN
may_treat4413Necrosis [Disease/Finding]6574001Necrosis (morphologic abnormality)FN
may_treat4513Neuroblastoma [Disease/Finding]87364003Neuroblastoma (morphologic abnormality)FN
CI_with4545Nevus [Disease/Finding]51697005Cutaneous hamartoma (morphologic abnormality)FN
may_treat5535Osteosarcoma [Disease/Finding]21708004Osteosarcoma, no ICD-O subtype (morphologic abnormality)FN
may_treat4733Paget's Disease, Extramammary [Disease/Finding]71447003Paget's disease, extramammary (except Paget's disease of bone) (morphologic abnormality)FN
may_treat4769Papilloma [Disease/Finding]23730008Papilloma (except papilloma of bladder M-81201) (morphologic abnormality)OF
may_treat4769Papilloma [Disease/Finding]23730008Papilloma, no ICD-O subtype (except papilloma of bladder M-81201) (morphologic abnormality)FN
may_treat4983Pigmentation Disorders [Disease/Finding]201273003Dyschromia (disorder)OF
CI_with31747Pregnancy [Disease/Finding]289908002Pregnancy (qualifier value)OF
CI_with31747Pregnancy [Disease/Finding]289908002Pregnancy, function (observable entity)FN
induces31747Pregnancy [Disease/Finding]289908002Pregnancy, function (observable entity)FN
induces31747Pregnancy [Disease/Finding]289908002Pregnancy (qualifier value)OF
may_prevent31747Pregnancy [Disease/Finding]289908002Pregnancy, function (observable entity)FN
may_prevent31747Pregnancy [Disease/Finding]289908002Pregnancy (qualifier value)OF
CI_with31845Pregnancy First Trimester [Disease/Finding]255246003First trimester (qualifier value)FN
CI_with31847Pregnancy Second Trimester [Disease/Finding]255247007Second trimester (qualifier value)FN
CI_with31849Pregnancy Third Trimester [Disease/Finding]255248002Third trimester (qualifier value)FN
may_treat5527Sarcoma, Ewing's [Disease/Finding]76909002Ewing's sarcoma (morphologic abnormality)FN
induces5583Sclerosis [Disease/Finding]43846000Sclerosis (morphologic abnormality)FN
may_treat5733Snake Bites [Disease/Finding]125199004Snake bite (morphologic abnormality)FN
may_treat5963Tachycardia, Paroxysmal [Disease/Finding]195078007Bouveret-Hoffmann syndrome (disorder)OF
may_treat6225Trophoblastic Neoplasms [Disease/Finding]115234004Trophoblastic neoplasm (morphologic abnormality)FN
may_prevent6277Tuberculosis, Pulmonary [Disease/Finding]397190009Respiratory tuberculosis (disorder)OF
may_treat6277Tuberculosis, Pulmonary [Disease/Finding]397190009Respiratory tuberculosis (disorder)OF
CI_with6303Ulcer [Disease/Finding]56208002Ulcer (morphologic abnormality)FN
may_treat6303Ulcer [Disease/Finding]56208002Ulcer (morphologic abnormality)FN
CI_with8327Venous Thrombosis [Disease/Finding]18266009Phlebothrombosis (disorder)OF
may_prevent8327Venous Thrombosis [Disease/Finding]18266009Phlebothrombosis (disorder)OF
may_treat8327Venous Thrombosis [Disease/Finding]18266009Phlebothrombosis (disorder)OF
may_treat7917Ventricular Premature Complexes [Disease/Finding]195093006Ventricular ectopic beats (finding)OF
may_treat7917Ventricular Premature Complexes [Disease/Finding]195093006Ventricular ectopic beats (disorder)OF
may_treat6521Wounds, Nonpenetrating [Disease/Finding]3821009Blunt injury (morphologic abnormality)FN
CI_with6517Wounds and Injuries [Disease/Finding]19130008Traumatic abnormality (morphologic abnormality)FN
may_treat6517Wounds and Injuries [Disease/Finding]19130008Traumatic abnormality (morphologic abnormality)FN
CI_with31773Young child [Disease/Finding]74489007Younger child (person)FN

Even if we use that "SPL_Problem_List_Mapping_ID" instead of the SNOMED id we still have losses. 

SELECT name, term_has_mapping, mapping_is_finding, role_def_id, count(1)
  FROM (
SELECT r.role_def_id, 
       d.name, 
       CASE WHEN r.target_value IS NULL THEN 0 ELSE 1 END term_has_mapping,
       CASE WHEN s.code IS NULL THEN 0 ELSE 1 END mapping_is_finding
  FROM umls.ndfrt_role c
    INNER JOIN umls.ndfrt_role_def d ON(d.id = c.role_def_id)
    LEFT OUTER JOIN umls.ndfrt_role r ON(c.target_id = r.source_id AND r.role_def_id IN ('P262795', 'P262796'))
    LEFT OUTER JOIN umls.snomed_finding s ON(s.code = r.target_value)
  WHERE c.role_def_id IN ('R37', 'R41', 'R43', 'R39', 'R35')
) GROUP BY ROLLUP(name, term_has_mapping, mapping_is_finding, role_def_id)

But now that we know what we're missing, we can move right ahead and complete the task before coming back and filling those gaps.

Extracting and Simplifying Useful Relationships

We want

  • indication (may_treat, may_prevent, may_diagnose, and induces)
  • classifications has_MoA, has_PE
    • the ChemClass? through has_Ingredient and then definingConcept+
  • interactions CI_MoA, CI_PE, CI_ChemClass
  • contraindications CI_with

most of these we will attach directly. Exept the ChemClass?, which we get from the definingConcept.

  • The INGREDIENT_KIND and NDF_KIND with single ingredient should be merged into a single ingredient.
  • The NDF_KIND with multiple ingredients can link directly to the NDF_KIND with single ingredients.

We shall reformulate the NDFRT using the SPL conceptual model.

Ingredient Substances (Moieties)

Those are the single ingredient ones: 

WITH ingr AS (
  SELECT source_id, target_id
    FROM umls.ndfrt_role 
    WHERE role_def_id = 'R25'
), single AS (
  SELECT i.source_id as id
    FROM ingr i
      LEFT OUTER JOIN ingr j
        ON(i.source_id = j.source_id AND i.target_id <> j.target_id)
      WHERE j.source_id IS NULL
) SELECT * 
    FROM single
      INNER JOIN umls.ndfrt_concept USING(id)

And double check if any of the remaining have no '/' in their name 

WITH ingr AS (
  ...
), multi AS (
  SELECT source_id AS id FROM ingr
    MINUS
  SELECT id FROM single
) SELECT * 
    FROM multi
      INNER JOIN umls.ndfrt_concept USING(id)
    WHERE name not like '%/%'

No, they don't. But aren't there some of the single ingredient items that have them? 

WITH ingr AS (
  ...
) SELECT * 
    FROM single
      INNER JOIN umls.ndfrt_concept USING(id)
      INNER JOIN ingr ON(source_id = id)
    WHERE name like '%/%'
    ORDER BY id

yes there are. A lot. But what ingredient? 

WITH ingr AS (
  ...
) SELECT * 
    FROM single
      INNER JOIN umls.ndfrt_concept c USING(id)
      INNER JOIN ingr i ON(source_id = id)
      INNER JOIN umls.ndfrt_concept d ON(d.id = i.target_id)
    WHERE c.name like '%/%'

those are mostly incomplete. Now do they carry any indications? 

WITH ingr AS (
  ...
) SELECT * 
    FROM single
      INNER JOIN umls.ndfrt_concept c USING(id)
      INNER JOIN umls.ndfrt_role r ON(r.source_id = id AND r.role_def_id IN('R35', 'R43', 'R39', 'R41'))
    WHERE c.name like '%/%'

no, nothing. So, never mind those.

Now we care for these single ingredient NDF_KIND terms because they carry the interesting knowledge. But we get those also from our UNII_NDFRT_MAP. We don't really care for the the mixtures, except for their carring extra information which the single ingredients don't have. 

WITH ingr AS (
  ...
) SELECT * 
    FROM multi
      INNER JOIN umls.ndfrt_concept c USING(id)
      INNER JOIN umls.ndfrt_role r ON(r.source_id = id AND r.role_def_id IN('R35', 'R43', 'R39', 'R41'))
    ORDER BY id

but there is only one of very low interest, so, never mind again.

Ready to make our interaction tables: