Re: [Gnumed-devel] basic test types

[Karsten Hilbert]

> In the table test_type I may define the random and fasting glucose 
> levels from 2 different labs
> (suppose these are test_org_pk 6 and 7)
> 
> ID     / TEST_ORG_PK / CODE   / CODING_SYSTEM / NAME
> 1      / 6          / 14769-4 / LOINC         / GLUCOSE^PRE 12H CFST 
> (SER/PLAS FASTING)
> 2      / 7          / 14769-4 / LOINC         / GLUCOSE^PRE 12H CFST 
> (SER/PLAS FASTING)
> 3      / 6          / 14749-6 / LOINC         / GLUCOSE (SER/PLAS, RANDOM)
> 4      / 7          / 14749-6 / LOINC         / GLUCOSE (SER/PLAS, RANDOM)
> 
> The above meets the schema requirement unique (fk_test_org, code).
Yes.

> Since some other coding_system could happen to use the same code for 
> a different purpose, we do not require "code" by itself to be unique.
Correct.

> However a combination of CODE with CODING SYSTEM should probably only 
> be associated with a single, consistent name so we should also have:
> unique (code, coding_system, name)?
No. Two different test_orgs can use differing NAMEs for
(code, coding_system) combinations. That's precisely the point
of a code, to allow for different labels that mean the same
thing.

> In table_type can we change "id" to "pk" or would it complicate 
> matters for you in your parents' practice if they are already using 
> PostgreSQL tables for lab data storage?
We can. Changing it currently.

> So now, moving to the table test_type_local, I can have
> 
> FK_TEST_TYPE / LOCAL_CODE     / LOCAL_NAME
> 1            / GLUC [F]       /  Glucose, fasting
> 2            / GLUC [F]       /  Glucose, fasting
> 1            / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
> 2            / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
> 3            / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
> 4            / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
> 
> This would permit me to select for display or analysis:
> - any patient's fasting glucoses (irrespective whether done at lab 6 or 7)
> - any patients' blood or serum or plasma glucoses (but not urine or 
> CSF glucoses)
Correct.

> If there are 100 tests that I commonly use, serviced by 5 local 
> test_orgs then, even if the presence of branch labs is already taken 
> care of, I would have to have at least 100 x 5 = 500 codes that I 
> have to create myself in test_type_local.
This sucks :-)

However, you don't *have* to create anything in
test_type_local. If there isn't anything in there for a
test_type the views will happily create virtual rows where the
unified name is the same as the original name...

You think it makes more sense to remove fk_test_type from
test_type_local and have a table lnk_test_type2local_type ?

> So maybe if all test_orgs 
> in my district were to use the *same* CODING_SYSTEM I would not have 
> to bother with test_type_local because could just pick LOINC 14769-4.
A widget written to business logic that I would consider
obvious would auto-aggregate those test anyways. And it's a
big IF.

> The special advantage of going to all that extra trouble with 
> test_type_local would be:
> 
> 1) when one or more test_orgs use a DIFFERENT CODING_SYSTEM and
Yes.

> 2) when I want to aggregate different tests for viewing purposes for example
> COAGS = INR, PTT and PLATELETS
> LIVER = AST ALT GGT ALK BILI INR HEPA HEPB HEPC
> CARDIO = CK TROP ANP GLUC CHOL
This is explicitely NOT the purpose of test_type_local. I
improved the comment to that effect. This area is what Liz is
eventually working on and which will show up under "profiles"
(no tables there yet).

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346