[Gnumed-devel] Re: Possible problem with schema table Clin.Test_type

[James Busser]

On 6-Feb-08, at 10:35 PM, James Busser wrote:

> Also, there is a field fk_test_org which is a foreign key to the  
> primary key clin.test_org.pk which is the organization providing  
> results. Presently, this too is defined as unique. Maybe the  
> intention was
>
>         (fk_test_org + coding_system + code) UNIQUE
>
> ... unless there is some other purpose served that I am missing?

Maybe the purpose was to allow the table

        clin.test_result

to hold, in one field

        fk_type

a foreign key to a row in Clin.Test_Type that informs *both* the  
organization (lab) that carried out the test AND the particular test.

If this schema is maintained, it would mean that where GNUmed is  
being fed by "n" number of different labs, we will:
- incur n-fold numbers of extra rows in the table Clin.Test_Type  
(maybe n x 100 tests? I am not sure how many test types one would end  
up storing)
- avoid having to have a value for fk_test_org in every row in  
clin.test_result

Would this mean that before an importer could import a result, it  
would have to test whether each instance of a lab's test code (and  
coding system) already exists in the GNUmed table and, if it did not  
already exist, to create this row in Clin.Test_Type before continuing  
to import the data

... and also that if it was a new lab that was contributing this  
result, the data could not be imported until after the lab  
organization had been created in GNUmed? In other words, in addition  
to the automatching that will need to be done to assure that received  
data can be correctly attached to a *patient*, there must be  
automatching done to assure that the lab organization that is  
providing the result is also matched to an existing value in GNUmed?