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

From:

Jim Busser

Subject:

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

Date:

Mon, 26 Sep 2011 17:47:49 -0700

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