On Sat, 2009-02-28 at 16:08 -0800, Waynn Lue wrote: > Plus, last time I checked, adding an enum required a full rebuild of > the table, while having an auxiliary table allows it to happen much > more quickly. > > Waynn > > On 2/28/09, Andrew Ballard <aballard@xxxxxxxxx> wrote: > > On Sat, Feb 28, 2009 at 5:13 AM, Ashley Sheridan > > <ash@xxxxxxxxxxxxxxxxxxxx> wrote: > >> On Sat, 2009-02-28 at 01:04 -0500, Andrew Ballard wrote: > >>> On Fri, Feb 27, 2009 at 7:32 PM, Ashley Sheridan > >>> <ash@xxxxxxxxxxxxxxxxxxxx> 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. > > > > > > Andrew > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > I'm not saying it is a replacement for auxiliary tables, but in a lot of cases, an enum works far better. Consider a column called display_status for a set of content pages. Having an enum type for 'live' and 'draft' is perfect, and likely not to change in the future. Using an external table is overkill, and reducing it to a number value that you then have to remember throughout your code doesn't make sense when the enum type makes it more logical. At the end of the day, it is down to personal taste, but I find a lot of good uses for enum, especially in CMS development. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
