I want to add some notes
> 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)
1) a sequence generates INT8 values (in general) and you have INT4 field to
refer to a serial field.
2) narrative TEXT is very bad to check uniquness becauce it is non-formal
human-generated native-language text. so it contains missprints, it may be
rephrased many ways with the same meaning.
3) afaik a diagnosis doesn't belong to a patient,
it belongs to a History, and History is marked with a date and status and
belongs to a patient.
do not treat my words as The Truth.
In response to
pgsql-sql by date
|Next:||From: Karsten Hilbert||Date: 2004-07-23 06:57:21|
|Subject: Re: surrogate key or not?|
|Previous:||From: Chris Cox||Date: 2004-07-22 23:46:27|
|Subject: Re: LIKE on index not working|