Re: [Gnumed-devel] External id type attributes, data consistency and validation

[Karsten Hilbert]

On Sat, Sep 24, 2011 at 09:43:22AM -0700, Jim Busser wrote:

> Other EMRs accept in what they call a "date of birth" field the value 00 00 
> 0000.

Depending on what they *do* with this it may be complete
bulls or not. If they store 00 00 0000 in the database they
are crazy (or else the database is crazy if that is required
by the database).

> Why not allow, for gender / sex
> 
>       m
>       M
>       male
>       Male
>       Man

Sure, in the GUI (and we do), but most definitely not in the database.

> But where an external code is locally defined, I see
> nothing wrong with assisting a praxis to make a choice of
> whether or not to trigger improvements that assist local
> consistency.

But NOT within the database.

> I see nothing wrong with allowing my praxis to
> determine that for the identifier type
> 
>       PHN Personal Health Number (issued by BC_CA.MSP)
> 
> it needs to be 10 digits, beginning with a 9. I see
> nothing wrong in principle with allowing a locally definable
> data value
> 
>       MRN Medical Record Number (issued by BC_CA.VCHA)
> 
> when it was entered as 12345 to be auto-assisted to be 00012345 *if and only 
> if* a praxis has determined that this is reasonable.

Sure, but not in the database.

> I can totally understand whether you do not see sufficient
> value in it to be interest to code the change. However this
> is orthogonal to whether there is anything flawed in the
> proposal, justifying to deny its support.

The proposal to support it by adding columns to the database
each of which representing accidental properties of ranges
of types of identifiers is, indeed, flawed in that it does
not scale.

One sane approach would be to define a (number of) regular
expression(s) with each external ID type:

        dem.enum_ext_id_types.regexen text[] default NULL

and have INSERT/UPDATE triggers on dem.lnk_identity2ext_id
check that.

But growing other columns as suggested is not an approach
good database design would regard as valid.

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