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

[Jim Busser]

On 2011-09-24, at 3:44 PM, Karsten Hilbert wrote:

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

I can concede that the entity itself has no inherent or 'natural' restriction 
on what can be valid. Rather, validity and invalidity are a function of

1. a value that resides in some other column. In the case of postal code, the 
other column is the applicable *country* 

combined with

2. arbitrary governmental policy rules which define what is 'allowed'


so it is presumably more fitting that any such rules are tied into the 
*relationship* between the value

        ext_id

and the individual to whom it pertains

        identity

taking into account the requirements of the ext_id_type

        dem.enum_ext_id_types

which is where you are seeming to agree we should store the constraints, and 
what you have clarified is that in place of multiple columns (one per specific 
constraint) we would better gather up one-to-many possible constraints per 
external ID type, and store these in a single column

        dem.enum_ext_id_types.regexen

BTW as far as a column name, I gather

        .regexen

is an inventive plural for pseudo-latin regex despite that latin -ix and -ex 
are normally pluralized with -ices (appendix --> appendices, index --> indices 
suggesting therefore regex --> regices) but we could maybe instead do

        .valid_regex

and would need to clarify how multiple strings would need to be formatted / 
stored to best allow their re-use.

??

-- Jim