Date: Thu, 22 Jul 2004 14:20:40 +0200
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Message-ID: <20040722142040.H3720@hermes.hilbert.loc>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<200407210001.00102.josh@agliodbs.com>
	<200407211144.01001.sad@bankir.ru>
	<200407211025.33615.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200407211025.33615.josh@agliodbs.com>
User-Agent: Mutt/1.3.22.1i
Re-X-turn-receipt-to: Karsten.Hilbert@gmx.net
Di-X-sposition-Notification-To: Karsten.Hilbert@gmx.net

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