On 17 Oct 2002 at 13:28, paul butler wrote:
> Purely for discussion:
>
>
> On Thu, 2002-10-17 at 22:23, Jules Alberts wrote:
> >
> > I have considered this. As a matter of fact, that is the way it is in
> > our current db but I'm not really happy with it. Theoretically
> CODE
> > should never change and is therefore safe to use as primary key.
> But
> > having an "extra" serial primary key will make the db more
> flexible
> > regarding to unforeseen complications.
>
> Could you not make NAME not unique? Then you could have a
> new code for the same name, not affecting previous records. If a
> code changes, then its a new code, or the old code with a new
> name
Both CODE and NAME are unique. And they will _never_ change. Until they
do :-). These are mostly tables which are not our own, stuff like
country codes, medical diagnosises etc. They comply to ISO standards.
Someone may decide to change them. Of course we could tackle this by
using an UPDATE CASCADE, but beside that I want a truly unique and
unchangeable column in the table. Hence the ID field, which is
redundant as long as nothing unchangeable changes, merely a safeguard.
> >Yeah, this happens. Later people want to expire particular codes,
> >or
> >change their meaning, but not for the existing records that refer to
> >them...
>
> If all attributes are 'unique' I don't see how you could change a
> codes 'meaning' without (effectively not mechanically) cascading
> these changes to existing records
>
>
> >From my own experience, I would also say that there is value in
> >being
> >able to sequence the codes in a non-alphabetic order. I add
> >another
> "seq" column to such tables, to allow their ordering to be arbitrarily
> adjusted as well.
>
> Just wondering aloud
>
> Cheers
>
> Paul Butler
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
