0

In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as:

Or, is there a choice to be made specific to the use case in hand? If there is, what decision framework should I use, i.e. when to choose one of the two options?

For instance:

  • Currency: It's better to use a table join, since currency codes to be supported could change with time (since modification has been emphasized in many posts as anti-enum, but then what could not change with time?).
  • PaymentStatus: It's better to use enum type with values like, SUCCESS, FAILED, CANCELLED.
  • TierPlans: It's better to use a table join, as it would give the flexibility to add columns like monthlyPricing, annualPricing, discountRate, etc. in the future. (I am currently using enum instead, with values like FREE, PRO, MASTER; hadn't thought of pricing yet -_- ).

I am finding that if I stress enough, I could find a table join being a better choice always, mutation safe route. Is increasing number of tables or table joins a symptom of bad design? How should I approach design in general, and design in such use cases specifically?


Note: I had asked this question on SO here. I am posting it here since I see that there is a closing request there, and I desperately need an answer to this confusing roadblock of mine. Understandably this might not be a good question to ask here too. I'd rather edit, if you may guide!

2

(Yes, there have been many heated discussions of ENUM vs TINYINT vs VARCHAR, both here and on stackoverflow.com. I have put my 2-cents in on some of them. I am addressing this Question to discuss nuances of the examples brought up.)

Currency is a tricky one. When, for example, a devaluation occurs, does the currency_code change?

If so, then is the question is about how often the list of values is likely to change.

If it does not change, then you may have a mess for the data -- Is "XYZ", the old xyz currency, or the 'new'?

With things like country_code (with well-established 2-letter codes) and currency_code (3 letter standard), etc, I recommend using things like

country_code  CHAR(2) CHARACTER SET ascii
currency_code CHAR(3) CHARACTER SET ascii

Rationale:

  • Only 2 or 3 bytes, which is not much bigger than a 1-byte ENUM or TINYINT.
  • Avoids the extra bulk and overhead of utf8mb4, which might be the default for the table.
  • You can still build a secondary table to, for example, spell out the full name of the country or currency.
  • You won't need a JOIN except when you want the extra info. (JOINing is most costly when you need to start from multiple Dimension tables in an attempt to look up rows in the Fact table. A JOIN is reasonably cheap going the other direction.)
  • Hi @Rick, thanks for your answer. But I don't really take away: "when should I not use a table join, and use enum instead?". Also, in your elaborate example, why do I need secondary table for keep description, can't it be just another column in our currency table, with country code as primary key? – devautor Dec 10 '18 at 4:38
  • @devautor - Oops, I did not think about currency simply living on the Country code row. Another example: If you already 'normalize' city, don't bother normalizing state or country; simply spell them out (or use abbrev) in the city table. – Rick James Dec 10 '18 at 6:25
  • Hi @Rick, got this last point on normalization, thanks :) I feel bad to press, but do you feel this answers already: "Should I never use enum, or when should I not?" – devautor Dec 10 '18 at 6:40
  • I like ENUMs in some situations. Some people insist that ENUMs should never be used. The two camps are too rational and too vocal for this to have a simple answer. Another "rule" for ENUMs: Don't use if there are more than 2 options. (I would stretch that to more like 5 options.) Would you dare have a column gender ENUM('male', 'female') when registering people at an LGBTIQA+ convention? Either avoid Enum, or be ready to add yet another option. – Rick James Dec 10 '18 at 6:55
  • Makes sense! I think the general rule of thumb could be then that locally related and immutable list of up to 5 values is for enum. – devautor Dec 10 '18 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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