Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Advantages and Disadvantages to using ENUM vs Integer types?

Considering MySQL only...

I'm curious to know what the community thinks the proper use-case for an ENUM type vs. a FK to a types table.

I've read this question: Advantages and Disadvantages to using ENUM vs Integer types?, however the use-cases aren't really covered well enough for me.

share|improve this question
2  
Perhaps then you should open a bounty on that question and specify where you would like to see more information. –  Nick Chammas Jan 12 '12 at 18:57
1  
@Randy - concerning Nick's suggestion, you can now add a reason when you add a bounty laying out what use-cases you want covered –  Jack Douglas Jan 12 '12 at 20:40
1  
IMHO this question is not a duplicate. The old question is simply pros-and-cons of ENUM. This question is more along the lines of using ENUM versus strict implementing of type tables via foreign key. The way I answered each question clearly reflects this. If there is no other objection, I'd like to vote this one to be reopened. –  RolandoMySQLDBA Jan 13 '12 at 5:48
add comment

marked as duplicate by jcolebrand Jan 12 '12 at 23:46

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

When you'd be using a column with a check constraint (limiting possible values) in other DBMS, I'd model that as an ENUM in MySQL

share|improve this answer
add comment

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)

CAVEAT

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 NOT NULL.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.