Re: surrogate key or not?

> > One thing we ARE looking for in our records is the ability to
> > find groups of patients by arbitrary criteria since one day
> > I'll have to find all my patients whose father took a statine,
> > whose second-born child suffered a bout of neutropenia 2 weeks
> > after birth and who started being on the pill at age 14.
> > Because they'll have a 3fold increased risk of lung embolus.
> > Unless monitored for clotting factors every 6 months. Which I
> > will have to do from now on. Get my point ? :-)
>
> couldnt you do something like, let them write the 'long flaky text', and
at
> the same time mark a certain number of key words or key phrases which
could
> be stored and retrieved?

I was thinking along similar lines. On one hand, you need the "long flaky
text" (love that expression), on the other, you want to ensure that you can
locate appropriate data, and that the required details are available. By
available, I mean that it was entered in the first place, and that it is
retrievable. I imagine a system whereby you define keywords and attributes
for them (attributes would be an episode date, or dosage, etc). The memo, is
checked for keywords and the doctor prompted to supply the attributes for
them. If your parsing was smart, and the memo formated a little, you could
conceivably pull a lot of this out of the memo as defaults. The processing
could also be done retrospectively by an intern or researcher, but I imagine
it would be best to have the doctor do it at the time.

Just some vague ideas anyway. This may of course be much more work than
anyone wants to get into... I don't have much experience with text searching
systems, but something reasonably sophisticated would probably get you
there.

Regards
Iain