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

[Karsten Hilbert]

> Like Jim suggest, I need to add (somewhere) the references of the datas.

Can you explain that some more - I don`t yet fully understand.

Going from substance to interaction can take two routes:

by ATC
by INN

Going by ATC is straighforward (if you know *both* ATCs). Going by
INN (which is how the French database did it until now) would need
another table:

table interaction_by_inn
   pk integer primary key
   inn1 varchar
   inn2 varchar
   (xml varchar)

(If xml really is a language independant encoding of the knowledge
 it would be duplicate to have it in both interaction_by_atc and
 interaction_by_inn in which case it should, again, go into its own
 table interaction_knowledge_xml which links to the id of the
 interaction).

Ah, wait, I start seeing the problem: now one would want both _by_inn
and _by_atc define the same primary key for a given interaction.

Thusly:

table interaction
   pk integer primary key

table lnk_atcs2interaction
   pk integer primary key
   fk_interaction foreign key(interaction.pk)
   atc1
   atc2

table lnk_inns2interaction
   pk integer primary key
   fk_interaction foreign key(interaction.pk)
   inn1
   inn2

table interaction_xml
   pk integer primary key
   fk_interaction foreign key(interaction.pk) unique
   xml varchar

table interaction_text
   pk integer primary key
   fk_interaction foreign key(interaction.pk)
   language_code varchar
   risk varchar
   management varchar
   unique(fk_interaction, language_code)

-- 
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!  
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail