From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 23 Jul 2004 08:57:21 +0200
Message-ID: <20040723085721.C728@hermes.hilbert.loc> (raw)
In-Reply-To: <200407221005.59482.josh@agliodbs.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
<200407211025.33615.josh@agliodbs.com>
<20040722142040.H3720@hermes.hilbert.loc>
<200407221005.59482.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Karsten.Hilbert@gmx.net
Subject: Re: surrogate key or not?
In-Reply-To: <20040723085721.C728@hermes.hilbert.loc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox