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, gender, status of a ticket (opened/closed/archived)
- 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)