Re: [Gnumed-devel] schema changes requested

[Karsten Hilbert]

On Wed, Nov 30, 2005 at 04:11:42PM +1100, Ian Haywood wrote:

> I would like to request some schema changes
OK, let's think them through.

> - forms_data seems to double up with the fields of 'real' tables that hold 
> this data
> such as referral, lab_request, medication, etc.,
yes

> I would get rid of it.
No. Here's why:

Form fields content are values extracted from the live
database, possibly slightly modified, for a given purpose.
They are not supposed to ever be changed after the fact. Now
a PDF would do for non-changeable form field items. But then
they would not be re-usable for filling in a, say, follow-up
form or re-creating the form with a slight variation
(because of mis-spelling, whatnot).

I think you already figured this out.

> - remove constituents completely: IMHO we're not going to use it.
Done.

> - put ... clin.referrals in separate files, which can
> be loaded via the australia  config files for the time being.
Done.

> ... clin.medications and  ...
I think clin.clin_medication is quite OK as it is. Feel free
to add and muck around with au.medications any way you
want/need, however.

> - lab_request needs a foreign key for the form generated
>    fk_form integer references clin.form_instances (pk),
> can be NULL for electronic results where no form exists.
I wonder whether a link table would serve the purpose
better. Can we positively assume that there'll only ever
(within reasonable limits) be ONE form associated with a
given lab request ? Should we rather

create table lnk_form2lab_req (); ?

> - clin.reviewed_root should have a flag
>       unread boolean,
> 
> false means the clinician referred to in fk_reviewer hasn't seen it yet.
You are making an assumption that I think is overly
specific. My fk_reviewer means "this is the person who HAS
reviewed the item". Your's is "this is the person who is
INTENDED to review the item and has possibly already done
so". What we actually want is probably both !

We already talked about the fact that we want to be able to
record who (as in plural) has reviewed the item (and signed
it off as seen - that's my difference between "view" and
"re-view" - the latter involves "consciously taking into
account"). That is why we introduced review_root -> review_*
such that we can attach several reviewers to an item.

Now you suggest we also want a (nullable)
fk_intended_reviewer, eg a conceptually targetted foreign
key. While I would argue that this is a process/workflow
type thing (IOW the importer decides who is to review a
particular item and notifies that person by their inbox or
whatever means) I don't have too many gripes about adding
that. I would, however, NOT add it to review_root since we
want the query

 select not exists (
        select 1 from reviewed_documents where fk_document=XXX
 );

to return True and have that mean "no one has reviewed that
particular document".

IOW, we'd need to add the field to doc_obj and test_result
etc etc. which is fine with me.

Also I would surely call it fk_*intended*_reviewer.

What do you think ? Do we really want/need that ?

> Let me re-iterate why I keep on banging on about this: all information
> that comes into an EHR must be in two categories: either information
> entered by a clinician, or information that has been "signed off" by
> a clinician to take responsiblity for it
I think you have an excellent point here. Let me slightly paraphrase:

... information either *generated* by the entering clinician
(or directly on behalf of such by immediate staff) or
imported and signed off ...

Reason being that anything imported under my account (say,
lab data) will appear to be entered and thus generated by
me. Hence we should cater for signing off items that are
typically entered by staff who legally can't take
responsibility for it. Which we already do.

> Here is AU a doc would be roasted alive for running and EHR without this 
> feature,
> it's difficult to see how you could get away without it elsewhere too.
Even if I could get away without being roasted alive (not
much sense in that since there's probably hardly any tender
meat on me) I think we should heed your advice here and
support that. It makes immediate medical sense.

> So, we need a way of presenting a list of documents/results which haven't 
> been signed
> yet.
This is already possible today:

select * from blobs.doc_obj do where do.id not in (
        select rdo.fk_reviewed_row from blobs.reviewed_doc_obj
);

(or any number of equivalent and likely faster queries)

Same for results.

What we cannot currently do is blame the person who was
*intended* to review it.

> As this stands this means search for test_result and doc_obj rows with no
> matching reviewed_root row: far too slow,
I challenge that assumption. Apart from being untested (and
likely untrue given proper indices) it violates the First
Principle of Optimization: Don't.

> the alternative is to duplicate the review_root and descendant tables but 
> this seems a
> bit pointless.
I agree.

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