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 <aball...@gmail.com> wrote: > 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. > > > Andrew > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
