CODEWITHSUNDEEP
mysqlarchitecture
artemave
artemave

Reputation: 6956

Is using mysql ENUM a bad architectural solution?

For me it is like using hardcoded values instead of constant variables in application code. But there are different opinions out there. So I can't really decide for sure.

P.S. For the scope of this question let us assume that performance is not an issue.

Upvotes: 17

Views: 8700

Answers (4)

stevebot
stevebot

Reputation: 24035

ENUM is great for data that you know will fall within a static set.

If you are using Mysql 5+, storage is almost always better with an ENUM type for data in a static set, as the official MySQL reference shows. Not to mention that the data is readable, and you have an extra layer of validation.

If you want to know if using ENUM will be an optimization I recommend using PROCEDURE ANALYZE. This will recommend the proper data type for your columns.

Upvotes: 6

soulmerge
soulmerge

Reputation: 75784

Not in any way! They have several advantages over a numeric field:

  • They are much more readable: UPDATE Person SET state=2 -- What does 2 stand for?
  • They have a limited range: If you have only 10 states for a person, why allow the numeric values 11+?
  • They can be used just like their numeric counter part: UPDATE person SET state=state + 1

In fact, using numeric values instead of enums is like putting constants into the source code.

Upvotes: 2

molf
molf

Reputation: 75045

This depends very much on the actual situation, but the point of column types in the first place is to define precisely which values are allowed and which are not. If, in your problem domain, the attribute that you are considering to store as an ENUM value, is fixed in the sense that it can't possible have other values, then ENUM is a great choice. An example of that would be gender: ENUM('male', 'female') is great, because the chance of a third gender to be added would be very low indeed.

If you are storing values that are more likely to change, then you might consider normalising your data model to a many-to-one relationship instead.

Upvotes: 2

James B
James B

Reputation: 8353

It depends on what you're trying to achieve, really. If performance, as you say, is not an issue, then it's largely down to your philosophy, and the inherent changeability of the data. If you're using an ENUM to store values for days of the week, to aid human readability, and 'queryability' of the data, then it's a perfectly valid use (and far superior, in some cases, to using numbers, or other representations). If, however, you're using it to store things like the category a product is in (for which the set of available categories could easily change), then it's a very poor solution.

Upvotes: 16

Related Questions