[Gnumed-devel] cross-db referential integrity again

[Karsten Hilbert]

I never much liked how in the clinical database we don't have
referential constraints on even patient id fields which is really
important. Now, there can't be much done about it lest we want
to require dblink(). The cross-database key verification demon
gets us one step closer. However, the "missing" referential
integrity links are still scattered around the schema.

I took up Horst's previous suggestion of having a table
explicitely referencing external keys. However, I dropped the
requirement for manually tracking usage on keys in that table
and used in-database referential integrity to enforce one
side of the equation. Add in the remote key verification demon
and we are pretty close, at least as close as manually
tracking the other side (the remote part of the key, that is).
Reason for this being that we didn't track WHICH SERVICE
INSTANCE a remote key belongs to, anyways, which reduces us to
either a) trust that we don't change the db-service mapping or
b) rely on *other* traits to be used in eventually needed
reidentification of separated records using, say, FEBRL (right
here one starts to understand exactly what PIDS is providing).
Anyways, to facilitate re-identification the external reference
table for patient IDs provides two more fields: PUPIC and
DATA, the former intended to hold an imagined system-independant
"globally unique" patient identifier while the latter can hold
arbitrary data intended to allow for contextual, semantic
re-identification, even manually, if required.

The drawback is that at some point someone needs to make sure
that patients referenced from clinical tables are actually
available via the xlnk_identity reference table (else the
in-database referential integrity constraints will fail - as
they should) -- which in turn requires help from the
frontend(s). The reference client, already supports this. Each
instantiation of a clinical record object did a check for
existance of it's supposed patient ID. It now also checks for
the reference table entry (and adds it if necessary).

All other code works transparently, however, as the clinical
tables *still* store the GnuMed internal identity table primary
key as their patient foreign key BUT this key is indirectly
referentially constrained via the reference table... This key
eventually really should be the PUPIC instead.

If this sounds confusing please ask for clarification.

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