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 integernot nullreferences patient(pk)on update cascadeon
deletecascade,   narrative textnot 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