Re: [Gnumed-devel] (FreeDIams) Re: Interactions engine available with Canadian drugs database

[Karsten Hilbert]

On Fri, Jul 23, 2010 at 07:06:32PM -0700, Jim Busser wrote:

> > What exactly is meant by "can be *exported*" ?  Would this mean
> > it is "duplicated into" the other databases or would it link to them
> > and only exist once ?
> 
> As alluded to by Eric, this more correctly means "able now to be implemented 
> also for"

I understand.

> Eric, in 0.5 will the French linkage remain by INN or will the new schema 
> depend on French drugs having ATCs?

Technically it could do both as needed - attempt ATCs and
fall back to INNs if no ATCs available.

> Also, as the French interactions source may change over
> time, will we have a way to identify diffs

If I may suggest the gettext suite of tools might aid in
fostering online translation and keeping things up to date.

Other than that suitably running diff against the previous
and current French source strings will easily reveal those
that have changed.

> Here is more info for anyone interested re the FreeDIams
> schema as it relates to interactions and which maybe makes
> clearer that it will not matter which drugs will happen to
> be included in any particular "Country X" drugs db provided
> that

> 1) its ATCs are included in that db AND

Assuming the abovementioned fallback ATC -> INN were to be
implemented any new database might be able to gain some
advantage from the fact that quite a few INNs will be
identical in related languages.

> 2) the interactions information exists in a suitable
> language.

I wondered whether it would be possible to, say, have a
German drug data source but be able to use the French or
English interactions information (as per the user's desire)
-- to me having the English available would be far
preferable to not having any.

This is likely to be the case for many less widely spoken
languages where people are used to using English as the
professional language in medicine anyway.

> FreeDiams 0.2x did not support multiple drug databases
> however between 0.3 and 0.4 what I believe *had* been a
> monolithic db was split into a drugs.db and an interactions
> (iam) db and the latter consists of
>
>       ATC table
>               ATC code
>               English possibly INN (from Canadian government source)
>               French INN
>               German maybe INN
> 
>       IAM_TREE table
>               …
> 
>       Interactions table
>               ID
>               ATC_ID1
>               ATC_ID2
>               INTERACTION_KNOWLEDGE_ID
> 
>       Interaction_knowledge table
>               `ID` INTEGER PRIMARY KEY, `TYPE` INTEGER NOT NULL
>               `RISK_FR` varchar(2000) NOT NULL 
>               `MANAGEMENT_FR` varchar(2000) NOT NULL 
>               `RISK_EN` varchar(2000) NOT NULL 
>               `MANAGEMENT_EN` varchar(2000) NOT NULL 
>               `XML` varchar(10000) 

Ah, excellent, that helps a lot understanding the structure.
May I respectfully suggest considering changing the tables
to something akin to:

        Interactions table
                ID
                ATC_ID1
                ATC_ID2


        Interaction_knowledge table
                `ID` INTEGER PRIMARY KEY, `TYPE` INTEGER NOT NULL
                'INTERACTION_ID' INTEGER NOT NULL FK(Interactions.ID)
                'LANGUAGE_CODE' varchar(5) NOT NULL
                `RISK` varchar(2000) NOT NULL 
                `MANAGEMENT` varchar(2000) NOT NULL 
                `XML` varchar(10000) 

?  This would turn getting interactions into:

SELECT
        risk,
        management,
        xml
FROM
        interaction_knowledge
WHERE
        language_code = <user language>
                AND
        interaction_id IN (
                SELECT id
                FROM interactions
                WHERE atc_id1 = <xxx> AND atc_id2 = <yyy>
        )
;

The advantage would be that the interaction_knowledge
*table* would never again need to be modified to include
another language ...   Just add more data to it.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346