Re: [Gnumed-devel] Re: Info and to-do tracking schema

[Karsten Hilbert]

> >A generic enter-clinical-todo widget would have the user pick a
> >type (eg. refill request) and have him enter some info, eg.
> >"Pharmacy Wilson at K-Mart called for refill on sildenafil".
> >Such requests would always relate to a given patient (defined
> >via clin_root_item). The context_link field would serve to
> >store a non-checked foreign key to a clinical table, say, a
>             ^^^^^^^^^^^^^^^^^^^^^
> >previous prescription. The widget needs to know what to put
> >there depending on fk_type. It might also be kept as NULL.
> 
> How is a "non-checked" foreign key different from other (default 
> "checked"?) foreign keys? Is it a matter of storing the "value" of 
> what would be the foreign key, without defining or requiring the 
> field to be treated "as" a foreign key, in case for some reason it is 
> desirable to modify table content independently?
Quite exactly. There can be various reasons for the "key" not
being checked:

1) It points to somewhere outside the current database for which
PostgreSQL does not offer a standard mechanism. One could use
dblink for that but it's fairly cumbersome (and contrib/).
What we do is channel all *those* FKs through an intermediate
xlnk_* table in the local db (to which all other tables have
hard FKs) and leave only that one "dangling". And then we use
our cross-DB referential integrity check demon to check (not
ensure) consistency of that one table across databases.

2) It would need to point to several target tables. This may
or may not be a sign of bad relational design. This is the
"opaque" key that I was referring to - we also use it in the
allergy table and some code-using tables.

> If a foreign key that is "non-checked" does not function truly or 
> properly *as* a foreign key, should we name it something other than 
> fk_? like ncfk_ or ek_ for external key?
Good idea. How about ufk_ for unchecked foreign key ? External
to me conveys too much of "external to this schema/db" and
ncfk "feels" cumbersome to use. "Unchecked foreign key" seems
to suggest that it really should be a FK but is unchecked for
any variety of reasons. An ufk_ sits well, too (for me, at
least). How 'bout ?

Oh, we also already use things like xfk_identity as eXternal
Foreign Key (on those that *are* external, that is) ...

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