On Sat, Feb 28, 2009 at 5:13 AM, Ashley Sheridan
<a...@ashleysheridan.co.uk> wrote:
> On Sat, 2009-02-28 at 01:04 -0500, Andrew Ballard wrote:
>> On Fri, Feb 27, 2009 at 7:32 PM, Ashley Sheridan
>> <a...@ashleysheridan.co.uk> wrote:
> I absolutely love enum datatypes; they allow you to use string values
> but internally stores them as numbers, and prevents the wrong data from
> being inserted. Much simpler than joining extra tables of values onto
> it.

Oh, I know why programmers love them. I like them for a lot of the
same reasons, but I'm enough of a DBA that I'm still not sure they are
a very good idea in a SQL database. Granted, indexes on an ENUM column
will be more useful than on SET columns, but what do you do when you
need to add a value to the list? You have to have permission to modify
the database, and you are limited to about 64 values. In some projects
that's an acceptable constraint. I tend to like auxilliary tables
better because I can easily add an admin interface to an app to allow
users with sufficient permission to add their own values as needed
without granting them access to muck around with the actual table
structure, I'm NOT limited to 64 values, and indexes work even in 1:m
(SET) cases in addition to 1:1 (ENUM) relationships.

You can't add extra fields to an ENUM to track when a value was added
to the list, whether it is no longer a valid value for new records
(since it probably can't be deleted because of referential integrity),
or any other information that might be relevant to the value. I know
these aren't needed in every case, but I generally like to plan for
extensibility if it doesn't require very much additional effort.


PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to