Re: [Gnumed-devel] import patient data - basic procedure

[Karsten Hilbert]

Hello Jerzy,

thanks for reporting your experience !

> I had a simple list of patients ...
...
> (firstname, lastname, start_date, due_date, description, ...)
...
> I decided to carry out the procedure manually.

I think that was the correct decision.

Do you want to contribute the code ?

> It meant:
> 1. create demographic patient data
> 2. create encounter with some narratives describing patient's problem
> 
> Unfortunately, neither inserting basic patient's demographic data nor
> creating encounter is an easy one line SQL statement (why?).

1) basic patient demographic data

You would have to define what you mean by "basic" if you
want a useful answer as to why it is not a one-liner in SQL.
The answer is likely going to be normalization.

The better answer is that the database is not designed to
make it easy for programmers but rather to best (to my
knowledge) support real-world affairs without taking undue
shortcuts (like one name per patient, one address per
patient, etc).

2) creating an encounter

This IS a one-liner in SQL:

        INSERT INTO clin.encounter (fk_patient) values ('pk of the patient you 
are interested in');

Well, yes, it lacks the administrative code

        set default_transaction_read_only to off;

needed up front but that doesn't really count.

> To make things harder, Postgres in version 9.1, which is still default
> for current Ubuntu distribution, does not support updatable views yet.

Waving a magic wand over things typically makes things
prettier but not necessarily safer.

> I think many people wanting to work with Gnumed face the same problem
> of importing existing data, which are not necessary HL7 compliant.

I don't know (but sure hope so) about "many" but
conceptually, that's correct. Would you like to contribute
your code ?

> Providing simple guide for minimal data import may save lots of time
> required to figure out internal details of database schema, eg. which
> fields are mandatory and which constraints must be preserved. So I
> wrote this guide,

Thanks !

> which is directed rather to some IT professional
> helping with migration, than to end user. Maybe you will consider it
> worth posting in wiki pages for developers?

Feel free to go ahead !

> Background details:
> 
> 1. Creating usable patient's data requires inserting records to both
> dem.identity and dem.names tables.

Correct.

> 2. Creating an encounter which is properly placed in EMR tree requires
> adding an episode.

That is correct. It requires 2 (or three) SQL statements:

        set default_transaction_read_only to off;
        insert into dem.identity (gender, dob) values ('m/f/tf/tm/h', now() - 
age);
        insert into dem.names (id_identity, lastnames, firstnames) values 
(pk_of_identity, 'Last Name', 'First Name');

> But you cannot add episode until you have encounter.

True. That does not mean a catch-22, however:

1) create encounter
2) create episode linked to encounter

Done. Yes, it requires 3 SQL statements.

        set default_transaction_read_only to off;
        INSERT INTO clin.encounter (fk_patient) values ('pk of the patient you 
are interested in');
        INSERT INTO clin.episode (fk_encounter, description) values 
(pk_of_encounter, 'new episode');

> Moreover text data for encounter are mainly stored as narratives in
> clin_narrative table (well, you might use assessment_of_encounter field,
> but it is not meant for long, multi-line text).

Correct.

> And encounter is not linked directly to episode, only
> through narratives.

Sure, that's quite required. Encounters are multi-problem in
most if not all cases (although many specialities choose to
ignore all but one problem most of the time).

> So, you have to
> touch three tables to import one encounter. (I know it could be
> simplified and still valid in the database schema, but it would not
> create proper EMR tree and made further management difficult)

That's right. Good analysis !

> Here is my solution:
> 1. created a function to add a minimal person's data dem.add_person and
> a function to add episode with encounter clin.insert_full_episode (both
> included as attachments). They use named parameters (supported by
> Postgres v. 9.1 does anyone use older version?).

It's not (only) a matter of who uses it. As long as Debian
ist the reference platform we need to support whatever is in
Stable. That will, gladly, change very soon. At which point
I do intend to move forward with PostgreSQL.

> One may enhance them,
> but they were meant to accept NULL values wherever possible and insert
> only minimal data. I hope you will find them useful. Comments welcome.

Will take a look.

> 2. imported external data to temporary table for further processing.

Just FYI: The "staging." schema is meant to be used for
storing such tables. Even if something happens to be left
behind in "staging." it won't break the client using the
database because "staging." is not considered in hashing the
database schema.

> The important fact is that only one record for a patient existed.
> 3. added column fk_identity referencing dem.identity(pk) to this table
> for further reference.
> 4. imported demographic data by calling dem.add_person storing
> dem.identity.pk in temporary_import_table.fk_identity
> like this:
> UPDATE temporary_import_table set fk_identity = dem.add_person(
>   temporary_import_table.lastname,
>   temporary_import_table.firstname,
>   temporary_import_table.gender  );
> 
> 5. created unattributed episode with one encounter with one narrative
> containing "description" from temporary file.
> SELECT clin.insert_full_episode(
>   fk_identity,                --  _pk_identity integer, 
>   start_date,                 --  _date  timestamp with time zone
>   NULL,               --  _health_issue integer
>   'data import',      --  _episode_d text
>   'data import',      --  _encounter_RFE text
>   NULL,               --  _encounter_AOE text
>   (SELECT pk FROM clin.encounter_type 
>     WHERE description = 'administrative encounter'),
>                       --  _encounter_type integer
>   Coalesce(description, 'no information'),    --  _s text
>   NULL,               --  _o text
>   NULL,               --  _a text
>   NULL                --  _p text
> )
> FROM temporary_import_table ;

Well done !

> Based on additional data available, I created some health_issues and
> attached episodes to them, but this is specific to my data.

I understand.

Thanks for your report !

Feel free to post to the wiki and/or contribute code to git
(you can send it to me if you don't want to make a "formal"
merge request).

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346