Page 1 of 1

When to use ENUM field?

Posted: Sun Nov 05, 2006 10:30 am
by matthijs
I have the following mysql db table:

Code: Select all

CREATE TABLE `climbs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `route_id` INT UNSIGNED NOT NULL,
  `climbdate` DATE NOT NULL,
  `name` VARCHAR (255) NOT NULL,
  `grade` ENUM('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+',
       '7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b') NOT NULL,
PRIMARY KEY (`id`)
);
There are 2 issues:
1) Whether or not it's good to use the + sign in the enum values. I read in a db book that it's best to only use alphanumeric, spaces and underscores. I tried the above code and it seems to work fine (mysql 5). Does anybody know of any possible problems with using these values?

2) I need to be able to filter in my queries on the 'grade'. I know it's possible to normalize the table by removing the grades to their own table and replacing them with for example an INT. That would make the sorting and filtering easier. However, filtering on the current set of ENUM values seems to work fine. For example:

Code: Select all

$sql = "SELECT *
FROM `climbs`
WHERE grade > '7a'
ORDER BY grade
LIMIT 0 , 30";
works fine.

Also, I didn't want to seperate the grades to another table as that would mean one more join in every query. Is anybody aware of any problems I might encounter with filtering and sorting on these ENUM values?

Thanks.

Posted: Sun Nov 05, 2006 11:14 am
by feyd
  1. I haven't seen anything that would suggest non-alphanumerics wouldn't work.
  2. ENUMs and SETs are both internally stored as numerics with their named component being translated during calls. You can still use the numeric values however. For example:

    Code: Select all

    INSERT INTO foo (grade) VALUES(6)

Posted: Sun Nov 05, 2006 1:04 pm
by matthijs
Thanks for your answers Feyd, appreciated. The use of the numeric values can be useful, good to hear that's possible.

Posted: Sat Nov 11, 2006 5:07 pm
by califdon
I agree with feyd but would add that my preference would be to use the ENUM for the reason that if you are working with raw table values (for maintenance or upgrading, for example), it makes it easier to see the actual values instead of the linking numerics.

Don