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`)
);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";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.