Re: [Gnumed-devel] Data pack for ICD-9 :-)

[Jim Busser]

On 2011-09-26, at 1:40 PM, Karsten Hilbert wrote:

>> Before uploading, shall I update it to understand the possibility of more 
>> than one entry where
>> 
>>      name_short = 'ICD-9'
>> 
>> for example, these same ICD-9 codes could exist in ref.data_source under 
>> some other language?
> 
> Yes, that can happen.

But ref.icd9 allows duplicates on

        fk_data_source, code, term

so what you are indicating (I think) is that we should allow in this table

        fk to icd9 where lang = 'en', '401', 'Essential hypertension'
        fk to icd9 where lang = 'fr', '401', 'Hypertension artérielle 
essentielle'

but not

        fk to icd9 where lang = 'en', '401', 'Essential hypertension'
        fk to icd9 where lang = 'en', '401', 'Essential hypertension'
        fk to icd9 where lang = 'en', '401', 'Essential hypertension'
        fk to icd9 where lang = 'en', '401', 'Essential Hypertension'
        fk to icd9 where lang = 'en', '401', 'Essential Hypertension'
        fk to icd9 where lang = 'en', '401', 'essential hypertension'
        fk to icd9 where lang = 'en', '401', 'ESSENTIAL HYPERTENSION'

so we want

        unique(fk_data_source, UPPER(code), UPPER(term))

and in fact should want

        unique(fk_data_source, UPPER(code))

because -- even if we would accept more than one set of terms in a coding 
system -- each set would better be in a distinct language, each having its own 
distinct

        fk_data_source

and maybe the indices in ref.data_source bear reviewing because they are 
currently

    "data_source_pkey" PRIMARY KEY, btree (pk)
    "data_source_version_key" UNIQUE, btree (version, name_long)
    "data_source_version_key1" UNIQUE, btree (version, name_short)
    "idx_ref_data_source_lang" btree (lang)

so maybe each of the middle two warrant the insertion, inside the 
right-terminal close parens, of

        , lang

?

-- Jim