Re: surrogate key or not?

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