MySQL ENUM type vs join tables

by

  • Changing the set of values in an ENUM requires an ALTER TABLE which might cause a table restructure — an incredibly expensive operation (the table restructure doesn’t happen if you simply add one new value to the end of the ENUM definition, but if you delete one, or change the order, it does a table restructure). Whereas Changing the set of values in a lookup table is as simple as INSERT or DELETE.

  • There’s no way to associate other attributes with the values in an ENUM, like which ones are retired, and which ones are eligible to be put in a drop-down list in your user interface. However, a lookup table can include additional columns for such attributes.

  • It’s very difficult to query an ENUM to get a list of distinct values, basically requiring you to query the data type definition from INFORMATION_SCHEMA, and parsing the list out of the BLOB returned. You could try SELECT DISTINCT status from your table, but that only gets status values currently in use, which might not be all values in the ENUM. However, if you keep values in a lookup table, it’s easy to query, sort, etc.

I’m not a big fan of ENUM, as you can tell. 🙂

The same applies to CHECK constraints that simply compare a column to a fixed set of values. Though MySQL doesn’t support CHECK constraints anyway.

Update: MySQL 8.0.16 now implements CHECK constraints.

More Related Contents:

  1. Cakephp Career Database Design
  2. How to store multiple options in a single table?
  3. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  4. What does character set and collation mean exactly?
  5. How to pivot a MySQL entity-attribute-value schema
  6. MySQL: multiple tables or one table with many columns? [closed]
  7. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  8. MySQL – Conditional Foreign Key Constraints
  9. Auto Generate Database Diagram MySQL [closed]
  10. MySQL stored procedures use them or not to use them
  11. Unique constraint that allows empty values in MySQL
  12. Entity-Attribute-Value Table Design
  13. In SQL, is it OK for two tables to refer to each other?
  14. Remove Primary Key in MySQL
  15. Relational database design multiple user types
  16. Modeling Product Variants
  17. Facebook like notifications tracking (DB Design)
  18. How to design a multi tenant mysql database
  19. MySQL Enum performance advantage?
  20. MySQL – Supertype/Subtype design
  21. Difference between two table structure
  22. Proper database model for a user feedback system (an interesting case)
  23. Strings as Primary Keys in MYSQL Database [closed]
  24. When to use a left outer join?
  25. List of Constraints from MySQL Database
  26. Should I always prefer MySQL InnoDB over MyISAM?
  27. Database Historization
  28. MySQL Relationships
  29. Inventory management with stock options
  30. How do I add more members to my ENUM-type column in MySQL?

Leave a Comment