Re: [Gnumed-devel] [Fwd: Re: [GPCG] Embedded Flags]

[Karsten Hilbert]

> mysql can automatically create an fulltext index across a number of 
> fields.

> I could not find this functionality in postgresql  :  (
> but it may well  be there.
Yes, that's one of the few built-in advantages of MySQL over
PostgreSQL. Notice the "built-in" as you can get this from
PostgreSQL with some code from contrib/.

> This has worked much faster and more efficiently for me than the 
> previous way of creating a special table for index words (less stop 
> words). (which was prone to corruption, as every time a text entry was 
> changed an enormous number of old entries had to be deleted and new 
> ones added
Sure. I wasn't suggesting that. I was thinking of moving ALL
clinical narrative into clin_narrative like this:

create table clin_narrative (
    id serial primary key,
    id_patient,
    id_type,                -- sth. like SOAP
    src_table varchar,      -- name of table where this belongs
    value text
) ;

create table allergy (
    id serial primary key,
    id_patient,
    drug,
    [...]                   -- allergy-related fields
    id_comment integer references clin_narrative(id),
) ;

such that I can

select
    drug, value as comment
from
    allergy a, clin_narrative cn
where
    a.id_comment = cn.id
;

for normal operations and

select
    value
from
    clin_narrative
where
    value ~* $search_term
        and
    <other conditions as to type of entry>
;

for full-text (non-indexed) search.

A few well-chosen indices on, say, clin_narrative.src_table,
clin_narrative.text and allergy.id_comment are helpful in
various situations.

This will not do an traditionally indexed full-text search but
will allow for searching across the full clinical narrative
tucked away in those useful little comments. Still, it will
also allow for associating said comments with their respective
typed data. Of course, there's a performance strain on behalf
of the server. And, of course, there's some trouble for the
query writer (which can, in severe cases, be alleviated with
some views). I am aware of the fact that this might result in
a clin_narrative table of extreme proportions.

If no one objects I'll comb through our SQL to see where this
would make sense and implement it.

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