Re: ENUM type - Mailing list pgsql-advocacy

On Tue, Jul 26, 2005 at 02:05:17PM -0700, Jeff Davis 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)
> >
> >
>
> Yeah, you're right. But this is only in the case where someone cares
> about using an int rather than a string type for some performance
> reason. If they don't mind wasting a few bytes (and it's really only a
> few bytes per record), then why not just use a check constraint when
> defining the table (like Chris explains)?

Normalization is about a lot more than just saving space in your base
tables. But since that's the example you used, you a) can't assume it's
only a few bytes and b) can't assume that those few bytes won't start to
seriously add up over the span of a few hundred million rows.

Remember: while disk space might be cheap, disk I/O bandwidth costs a
fortune.

> > Hopefully someone on -hackers can shed light on what's required to clean
> > up the parsing. One thing worth noting though, is that table definition
> > is a relatively small part of doing a migration. Generally, it's
> > application code that causes the most issues. Because of this, I think
> > there would still be a lot of benefit to an enum type that didn't
> > strictly follow the mysql naming/definition convention. In this case, it
> > might be much easier to have an enum that doesn't allow you to define
> > what can go into it at creation time; ie:
> >
> > CREATE TABLE ...
> >     blah ENUM NOT NULL ...
> > ...
> >
> > ALTER TABLE SET ENUM blah ALLOWED VALUES(1, 2, 4);
>
> Interesting. I'm not really sure exactly what syntax we want to use, but
> as long as it gets the job done and is reasonable to implement.

Yeah, like I said the real key is just making sure it works the same
from an application's viewpoint (which generally doesn't involve any
DDL).
--
Jim C. Nasby, Database Consultant               decibel@decibel.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?"