Re: surrogate key or not?

Josh, sad,

> create table diagnosis (
> pk serial primary key,
> fk_patient integer
> not null
> references patient(pk)
> on update cascade
> on delete cascade,
> narrative text
> not null,
> unique(fk_patient, narrative)
> );
>
> This was obviously created so that a patient could have multiple diagnoses.
> However, there is no information in the table to indicate *why* there are
> multiple diagnoses.
Because there is no information to be had on this fact. The
patient IS afflicted by such or she is not. There is no why.

> And you are using a real key based on a long text
> field;
Yes, but for various concerns I am not using it as the primary
key, just making sure it is unique. I was just trying to
ascertain myself that this is OK to do from a database insider
point of view.

> always hazardous, as there are many ways to phrase the same
> information and duplication is likely.
But that is at the discreetion of the user/doctor and nothing
that can be enforced at the DB level (no, don't start thinking
about coding systems/classifications).

> To do it in english, your postulates look like:
>
> PATIENT 67 was given a diagnosis of WATER ON THE KNEE.
> PATIENT 456 was given a diagnosis of ACUTE HYPOCHONDRIA.
Hm, I don't see anything wrong with that (I'm a doctor). The
plain information that Patient 456 is known to have suffered
bouts of ACUTE HYPOCHONDRIA is invaluable when dealing with an
agitated, psychically decompensated, hyperventilating patient
456.

> But this is a bit sketchy. Who made these diagnoses?
I may or may not care. Our actual schema does, of course,
carry that information.

> When did they make them?
We'd be happy if we always knew.

>Why?
That's of marginal concern, actually, and the answer just
flows from the narrative of the medical record. But even if
there's no narrative there the "fact" alone helps.

> create table diagnosis (
> pk serial primary key,
> fk_patient integer references patient(pk),
> fk_visit integer references visits(pk),
> fk_complaint integer references complaints(pk)
Nope, this doesn't belong here AT ALL from a medical point of
view. Diagnoses and complaints don't have any rational
relationship. This is life.

> fk_staff integer references medical_staff(pk)
> narrative text,
> unique(fk_patient, fk_visit, fk_complaint, fk_staff)
> );
And in fact our real tables ARE pretty much like that :-)

> PATIENT 67 was given a diagnosis by STAFF MEMBER 12 on his VISIT #3
> in response to NOT BEING ABLE TO WALK of WATER ON THE KNEE
> PATIENT 456 was given a diagnosis by STAFF MEMBER 19 on his VISIT #192
> in response to THE CREEPY-CRAWLIES of ACUTE HYPOCHONDRIA
That'd by a psychosis ;-)

> It also allows you to establish a much more useful key; it's reasonable to
> expect that a single staff member on one visit in response to one complaint
> would only give one diagnosis.
Entirely false and a possible sign of inappropriate care.

> Otherwise, you have more than database
> problems. And it prevents you from having to rely on a flaky long text key.
Flaky long text is what kept people reasonably well in health
for the last, what, five thousand years ? I rely on it
countless times every single day.

BTW, our full schema is here:

http://www.hherb.com/gnumed/schema/

Lot's of it isn't in the state yet where we want it but we are
getting there - or so I think.

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