From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Thu, 22 Jul 2004 14:20:40 +0200
Message-ID: <20040722142040.H3720@hermes.hilbert.loc> (raw)
In-Reply-To: <200407211025.33615.josh@agliodbs.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
<200407210001.00102.josh@agliodbs.com>
<200407211144.01001.sad@bankir.ru>
<200407211025.33615.josh@agliodbs.com>
Josh,
I reckon you are the one in the know so I'll take advantage of
that and ascertain myself of your advice.
I am the primary designer for the database schema of GnuMed
(www.gnumed.org) - a practice management application intended
to store medical data. Obviously we wouldn't want ambigous
data.
I have until now used surrogate primary keys on all table like
so:
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)
);
Note that fk_patient would not do for a primary key since you
can have several diagnoses for a patient. However, the
combination of fk_patient and narrative would, as is implied
by the unique() constraint. For fear of having the real
primary key change due to business logic changes I have
resorted to the surrogate key.
Short question: Is this OK re your concerns for using
surrogates, eg. using a surrogate but making sure that at any
one time there *would* be a real primary key candidate ?
This would amount to:
> Streets
> ID Street Name Location
> 345 Green Street West Side of City
> 2019 Green Street In Front of Consulate
> 5781 Green Street Shortest in Town
Key: ID
UNIQUE: Key, Location
Is that OK ?
Karsten
--
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: <20040722142040.H3720@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