[Gnumed-devel] Re: FreeDiams Database Schema

[Jim Busser]

On 2010-02-22, at 6:40 AM, Eric MAEKER wrote:

> Updated (sql schema of INFORMATIONS) :
> 
>       http://ericmaeker.fr/FreeMedForms/di-manual/drugsdatabaseschema.html

I could still fix a few field name and other information english misspellings 
(like MARKETTED --> MARKETED and AUTORIZATION --> AUTHORIZATION) but I don't 
know if you reconsidered credentialing a Google account you asked me for or 
maybe you personally prefer a 'francophonization" of english AUTHORIZATION --> 
AUTORIZATION


> 
> Need to add the RULES and a COMMENT. Anything more ?

Still thinking...


> Still the link table between MOLECULE_CODE and IAM_ID is missing.
> ATC database must be created.

I am still thinking that the ATC will be a useful link and am just wondering 
how to pull it together. There do exist about 26 ATCs for combination drugs (I 
got this information from 
http://apps.who.int/medicinedocs/en/d/Js4952e/5.1.html ) and the others do seem 
handled under a "main drug" naming convention for example a Children's Cold 
remedy sold in Canada is ATC: N02BE51 (ACETAMINOPHEN, COMB EXCL PSYCHOLEPTICS)

But this does not matter because nearly every molecule sold in a country would 
only rarely if ever be sold *only* in combination. The only unusual cases I 
could think of are enzyme inhibitors. Anything that could not be populated from 
Canada's ~ 1400 unique single-drug ATCs might only leave such molecules as 
tazobactam (which has an ATC J01CG02 if we care to input it) and things like 
carbidopa, cilastatin (that don't have their own ATCs) but we probably don't 
even need to worry since the French IAC table contains no tazobactam or 
carbidopa or cilastatin!

But we just (still) need to understand how to map to (and maybe re-express) the 
French IAM table as an example solution. I do notice that in IAM_IMPORT its 
columns ID1 and ID2 really just represent a shared column "DENOMINATION" (which 
I gather may simply be a French INN drug molecule name) from the table 
IAM_DENOMINATION.

I am wondering whether what exists in the FreeDiams IAM_DENOMINATION table
        fluoroquinolones
        hormones thyroïdiennes

could be replaced by the first 5 characters of the ATC
        J01MA
        H03AA

And the link from COMPOSITION to IAM could just be the first 5 characters of a 
molecule ATC?

Can we
- obtain, from the French source, the ATC-per-French-UIC
- achieve some mapping between what seems to be the French INN (which we 
suspect is what resides currently in the FreeDiams tables) and the English 
often-INN contained in the Canadian database or which (English-INN) can be 
looked-up from WHO lookups?

-- Jim