Re: surrogate key or not? - Mailing list pgsql-sql

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