Re: enums

Andrew wrote:
> > Jim C. Nasby wrote:
> >Personally, I don't see why enum can't just be syntactic sugar on top
of
> >a side-table of values and a foreign key. And I guess a view to hide
the
> >internals from normal viewing. That would certainly allow the most
> >flexibility, although it probably wouldn't perform as well as what
you
> >wrote.
> The other issue is ease of use.
>
> We used lookup tables in bugzilla when it was converted to work with
> Postgres. But many users will find having to do that annoying, to say
> the least. I think there's a very good case for providing true enums.
> There is a technical part of the puzzle I can't quite see yet, though
:-)

Hm, I agree with Jim here. IMO, enum=FK syntax sugar...enum should be a
lookup table with two fields, one being enum value which is the PK, and
two being the sequencing value.

I think many people are opposed to this approach because they assume
this relationship is via ID-ID link (IIRC this is what mysql does under
the hood). In fact, the enum table's only purpose is for constraint
checking, not to lookup the value (there is no 'id').

I like the way sequences work. They are first class SQL objects
although they are normally accessed via helper functions. Enums could
be the same. Dependancy could be preserved to the creating table or not
(I prefer not).

Merlin