Re: [Gnumed-devel] basic test types

[J Busser]

At 6:38 PM +0200 9/26/04, Karsten Hilbert wrote:

> > > > > > >possible to
> aggregate various types of BP measurements
> > >> >> >[or] aggregate
> test super types even with overlapping contributing
> > >> >> >types... So one might define "all
> BP", "in-house BP",
> > > > >>
> >"right-arm in-house BP", "external BP" etc
> etc.
> No, the constraints are
>
> unique(local_code, local_name)
> unique(fk_test_type, local_code)
>
> eg:
> a) each local_code can only ever be associated with one local_name
>    (eg. long-short name combinations are unique)
> b) each test_type can only once be associated with a
>    given local_code (and thus
> local_name)
> Does that make sense ?

Maybe. Is the following correct?

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).

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. 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)?

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?

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)

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. 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.

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

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