Content-Type: text/plain;
  charset="iso-8859-1"
From: sad <sad@bankir.ru>
Organization: IT
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 23 Jul 2004 09:16:50 +0400
User-Agent: KMail/1.4.3
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<200407211025.33615.josh@agliodbs.com>
	<20040722142040.H3720@hermes.hilbert.loc>
In-Reply-To: <20040722142040.H3720@hermes.hilbert.loc>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200407230916.50436.sad@bankir.ru>

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=
=20
refer to a serial field.

2) narrative TEXT is very bad to check uniquness becauce it is non-formal=
=20
human-generated native-language text. so it contains missprints, it may be=
=20
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=
=20
belongs to a patient.

do not treat my words as The Truth.=20