Re: char() or varchar() for frequently used column - Mailing list pgsql-novice

If your really that worried about this you will just have to have an
effective date and an expiration date on the codes.  Leave the expiration
date either null or a VERY VERY large value.



On Thursday 17 October 2002 07:21 am, you wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org