Re: [SQL] ENUM like data type

On 6/28/05, Martín Marqués <martin@bugs.unl.edu.ar> wrote:
> El Mar 28 Jun 2005 13:58, PFC escribió:
> >       Personnally I use one table which has columns (domain, name) and which
> > stores all enum values for all different enums.
> >       I have then CHECK( is_in_domain( column, 'domain_name' )) which is a
> > simple function which checks existence of the value in this domain (SELECT
> > 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.
> >       You can also use integers.
>
> I personally think that the ENUM data type is for databases that are not well
> designed. So, if you see the need for ENUM, that means you need to re-think
> your data design.

I seem to remember some discussion here, half a year ago perhaps
which was about something similar (while not exactly).  I mean it

I think it someone said that DB2 (I am not sure about that one)
has a feature that enables it to normalize the table behind the
scenes.

As I remember it, it works somewhere along the lines of:
-- you create table
CREATE TABLE foo (   when timestamptz,   useragent some_data_type_perhaps
);

...and RDBMS will create a lookup table for useragents for you,
with serial key, etc, etc.  And in our foo table useragent will be
kept as a reference to that lookup table.  When you do a select,
lookup table will be consulted behind the scenes, etc, etc.

All this is doable with RULEs and VIEWs (and triggers for populating).

Well, what MRB had in mind was more like a special subcase
of such approach (lookup table with "read-only" keys), but I think
such a lookup table would be benefitial for many users, especially
when dealing with large tables.

Incidentally, does it qualify for todo?  Or maybe its already there?
 Regards,    Dawid