[Gnumed-devel] Re: GNUmed substance schema was Re: GNUmed and FreeDiams

[Jim Busser]

Whoa! Where did the v15 drug-related tables go to…

        ref.table.lnk-substance2brand
        clin.table.active-substance
        ref.table.consumable-substance
        ref.table.branded-drug
        
In the meantime, I had been trying to figure out how to populate the above 
tables in their last known form…

- a source file could usefully (?) contain, where brand 2 is a combo drug, 
something like

brand1name, +- brand1atc, external_code, external_code_type, substance1Name, 
substanceStrength, substanceUnits, +- substanceATC
brand2name, +- brand1atc, external_code, external_code_type,  substance1Name, 
substanceStrength, substanceUnits, +- substanceATC
brand2name, +- brand1atc, external_code, external_code_type,  substance2Name, 
substanceStrength, substanceUnits, +- substanceATC

--> from this, we could populate the table

        ref.consumable.substances

with

        SELECT DISTINCT substanceName, strength, units

and then update (where substanceATC was available in source)

        UPDATE ref.consumable.substances
        SET atc…

--> then populate the table branded-drug

        SELECT DISTINCT brandName, brandATC, external_code, external_code_type

--> then create links between

        branded-drug
        consumable.substances

        where branded-drug keyed to source table links to
                consumable.substances keyed to source table

?