When to use an enum or a small table in a relational database?

Hate to answer a question with a question, but it depends. How often do you expect the values to change, and how often do you release code?

Enum types will require a code change. A pure database table will be much easier to change. Enum types are more convenient for coding.

For infrequent releases, or if you often have new/deleted/changed values, use a database table. For static sets of values, or if you release code all the time, use an enum.

Enum is good when:

• You want to limit the choice of possible values (e.g. days of the week, sex (male/female))
• You want to reduce storage space (tuples are stored in just 1 byte each)
• Reduce the number of disc accesses for joins

Enum is bad when:

• You are not sure what to put as the possible values
• Possible values are liable to change (this requires admin rights to ALTER tables, which may require you to go offline)

Enums can be good for performance reasons because strings contain more data than uints. In general as long as you are SURE that you wont be changing the enum mappings it is a good way to go, especially from the coders point of view. It does get a bit hairier when you wan to run raw sql in the database itself though because you need to reference the code to find what all the integers mean.