When using ENUM to have a range of values, you have to properly plan what values you will use. Once those values are put in place, it can be rather challenging to perform any kind of simple maintenance should you want to redefine the ENUM range.
While there are bait-and-switch tricks you can perform to extend a range for an ENUM, those same tricks become very cumbersome (maybe even impossible) if you want to do other things such as reducing or altering an ENUM range.
Using foreign keys makes a great deal of sense when you know that the types you are establishing can be extended, reduced, or altered. Just by adding new entries into a table with the types needed, keys can be passed around to other tables without worrying about the underlying value being properly represented in the foreign tables. In addition, you would not be worried about those values matching up exactly with the base table definition of the type. Such concerrns would definitely apply if using ENUM.
Foreign Key Constraints would server to protect a row from absorbing invalid types. Hence, this would not be suitable for ENUMs because the constraint checking would have to be done in your application rather than in the database.
Here is what should be considered about ENUM usage:
ENUMs work best when
- the type you are representing is local to the table only
- it must represent a type that is never changed
- it will never likely experience redefinition
- its cardinality needs to be low
- its portability must always be based on logcial dumps of the table, never physical. Otherwise, bait-and-switch methods of maintenance would have to accompany the use of the ENUM.
- you can allow for NULL (Example :
game_rating ENUM('EC','E','E10+','T','M','A') NULL This allows a new game to be entered into a table without a default rating)
OK game_rating may be a bad example since ESRB could create more game ratings. In that case, a FK would be better but the base table would have to have 'unknown' as a default type and define game_rating as