[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] [Fwd: Re: [GPCG] Embedded Flags]
From: |
Karsten Hilbert |
Subject: |
Re: [Gnumed-devel] [Fwd: Re: [GPCG] Embedded Flags] |
Date: |
Fri, 11 Apr 2003 14:43:17 +0200 |
User-agent: |
Mutt/1.3.22.1i |
> 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