Re: [Gnumed-devel] clin.clin_narrative unique constraint and au specific importer

[Karsten Hilbert]

On Mon, Feb 20, 2006 at 12:07:52PM +0800, Syan Tan wrote:

> whilst developing an au specific importer for gnumed , the
> clin_narrative.unique  column constraint failed
> because narrative fields was too long and overran the index buffer.  
Ah, good, real-life bugs show up :-)

> the proposed change is to add a after update,insert for each row and a simple
> plsql trigger function to check
> if any existing rows matching the new row but use md5(narrative) instead of
> narrative in the check
I have moved the problematic

 unique (..., narrative, ...)

constraint out of gmclinical.sql::clin.clin_narrative and
have created a corresponding unique index

 clin.idx_clnarr_unique on clin.clin_narrative(..., md5(narrative)); 

in gmClinicalViews.sql instead.

This gives us *both* indexing and uniqueness :-)

> I've also got a script package for importing a commonly used emr in oz into
> gnumed. So far, it imports doctors, demographics,
> and progress notes.  It's main disadvantages is that it uses an intermediate
> postgres database, which could take up
> quite a lot of space,  and also it takes more time to do a 2 stage import . 
I don't see this as a problem at all. In fact it sounds like
a sensible approach to me.

> The users of the old system as registered as nonlogin
> users, and show up in pg_role but not in pg_user.  This isn't currently being
> used, as the author's names appear in the old progress notes anyway, but it
> sort of normalizes better if modified_by shows the original author ?
I guess it's sort of up to the policy of the site using the
importer whether the original authors should show up
textually in the data or structured in the real modified_by
fields. Some think putting the original author into
modified_by paints over the fact that the data hasn't really
been put into the GNUmed system by that author.

Personally, I tend to believe that if the data source is
really well structured it is worthwhile to keep the original
author in the proper field.

If I wanted to be anal about it I'd do a two-stage import:
first set all the modified_by fields to something like
"Syan's OZ-EMR importer" and then in a second step SQL
UPDATE all the respective rows to the original authors. That
way the fact that the rows were initially imported rather
than entered is well documented in the audit log :-)

> ok to commit this too ? ( into importers, or into testarea ? )
I think we would be fine with having this imported into a
properly named subdirectory of client/importers/.

Any chance we can get a larger anonymized sample set up as
test data ?

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