Re: ENUM type

On Tue, Jul 26, 2005 at 01:42:42PM -0700, Chris Travers wrote:
> Jim C. Nasby wrote:
>
> >OK, but compare the amount of work you just described to the simplicity
> >of using an enum. Enum is much easier and simpler for a developer. Of
> >course in most cases the MySQL way of doing it is (as has been
> >mentioned) stupid, but done in the normal, normalized way it would
> >remove a fair amount of additional work on the part of a developer:
> >
> >- no need to manually define seperate table
> >- no need to define RI
> >- no need to manually map between ID and real values (though of course
> > we should make it easy to get the ID too)
> >
> Again, automating this process is the only way I can see this done in a
> normalized way.

Not quite shure what you mean there...

> I think that having type definitions (enum options) in
> the table definition is in general a very bad idea. A simple option

Why?

> What about the possibility of using a domain... One could alter the
> code using conversion tools....
>
> Something like:
> CREATE TABLE table_name (
> val_name ENUM(option1, option2, option3) NOT NULL,
> );
>
>
> would be rewritten to:
> CREATE DOMAIN table_name_val_name_enum AS VARCHAR DEFAULT NULL CHECK IN
> ('option1', 'option2', 'option3');
> CREATE TABLE table_name (
> val_name table_name_val_name_enum NOT NULL,
> );
>
> This could be added to the mysql2pg scripts.

That's a good idea for a stop-gap, or if no one wants to put the effort
into creating a useful enum type.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"