Re: ENUM type

On Tue, Jul 26, 2005 at 05:30:49PM -0400, Merlin Moncure wrote:
> Jim C Nasby:
> > 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
> create table color (color text);
> insert into color values ('red'); -- etc
>
> > - no need to define RI
> references color
>
> > - no need to manually map between ID and real values (though of course
> if you use color as p-key this is unnecessary
>
> > we should make it easy to get the ID too)
> again, in this case the color should be the id and using a surrogate key
> is poor design. If that's too much typing you have the option of a
> check constraint which is superior to enum in just about every way,
> particularly if encapsulated into a domain (although inferior to RI
> solution, IMO).

Please go back through the original thread. The original comment was
regarding adding an enum type that actually made sense; ie: it stored an
int in the base table that referenced a parent table. The original
poster was arguing that enum didn't really buy anything over doing that
the manual method (create seperate table; fill that table with values;
create base table with RI, and finally, make sure that everything that
touches the base table can do mapping, or add a bunch of other stuff to
do the mapping automatically). My argument is that simply specifying

fieldname ENUM(...)

is a heck of a lot easier on developers than doing all those manual
steps, yet it still does things in the correct, normalized manner
(unlike MySQL).
--
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?"