Re: [Gnumed-devel] Languages and drug interactions was (FreeDIams) Re: Interactions engine available with Canadian drugs database

[Karsten Hilbert]

> >     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) 

> 1) no drugs database available (at least, not natively inside FreeDIAMS)

...

> 2) drugs database available, but the interactions did not (yet) get
> translated

...

> 3) drugs database available in non-native language and interactions
> available in native language.

That is what I meant.

> If there would be agreement on the above, a separate step would remain to
> consider the schema options. The suggestion above maybe (?) implies
> directionality, that the interactions table is the parent table based on
> ATC and

Which makes sense because the interaction itself exists inside nature
conceptually. It doesn't care into which languages a textual
representation thereof is translated let alone whether any textual
representation even *exists*.

This is very similar in nature to that in GNUmed a person first of
all exists as an entity in dem.identity whereas names get attached
from a secondary table.

> that it is from this table that the knowledge is linked, always depending on
> an unchanging UID for the interaction.

Exactly. Interactions are simply enumerated. In fact, this table *seems*
to suggest a natural key: (ATC1 + ATC2). However, there can be several
wanted-to-be-kept-distinct interactions for the same combination of ATCs.
Therefor a surrogate, artificial primary key is useful.

> The potential problem with this is
> that the primary data source AFSSAPS is keyed only to french drug names
> which is maybe why the existing interactions table maintains a foreign
> key to the knowledge.

I am not sure I yet fully appreciate why this would be. The linkage
would still be there albeit the other way round (as is customary
for master <-> detail tables in RDBMS). Doing so is known to prevent
mounting problems down the road of expansion (which I cannot, however,
enumerate off the top of my head).

> The second issue is whether to normalize the language strings out of the
> knowledge table, in which case it would no longer be a knowledge table but
> rather a link table in which case I maybe do not see how a language code
> value in each record of Interaction_knowledge would work (?)  

No, the interaction_knowledge table would keep all the language strings.
It would only gain more rows, namely one per language per interaction.

Karsten
-- 
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01