5

Imagine we have orders table, and an order has a status. Which of these three options is the best?

  1. Use varchar for status column
  2. Use enum for status column
  3. Use separate status table, which has status_id int and name varchar, and in orders table keep status_id as a foreign key.

Which is the best approach? I suspect that using dictionary table is also better because it'd be faster to search statuses by int, instead of varchar.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Victor
  • 173
  • 1
  • 9

3 Answers3

5

Well, I am an advocate of ENUM -- at least in limited use.

I would use it for status with a small, reasonably static, list of possible values. I would start with unknown to catch things that are typos. ALTER TABLE has long been optimized to add a new option on the end of an ENUM list.

I would not use ENUM for continents. If there are standard abbreviations, I would use a short VARCHAR for such. For countries, I advocate

country CHAR(2) CHARACTER SET ascii

Using a lookup table can be very bad for efficiency. A "star" schema in Data Warehousing can be terribly inefficient. This happens when multiple JOINs are needed; ENUM avoids the inefficiency. VARCHAR is bulky, which is a big issue in DW applications.

An ENUM acts like a string in many situations: WHERE status = 'OK' is more readable than either of the alternatives.

Comparing two ints versus two strings: Ints is not enough faster to matter.

  • VARCHAR -- Use when bulkiness is not a problem
  • ENUM -- Use when bulkiness is a problem, and the number of choices is quite small and stable.
  • Dictionary lookup -- Use when multiple tables need to reference the same set of stuff, and/or when there is extra info in the Dictionary.
Rick James
  • 80,479
  • 5
  • 52
  • 119
1

MySQL doesn't have an ENUM type. It has something called ENUM(), but it's not an ENUM. It's a translation and a column constraint. Because of this the maintenance on using the pseudo-type goes up. If it's used in two places (ie, tables),

If you go with the "dictionary table" and establish a normalized relationship you minimize maintenance and complexity. You would have to only maintain this one place in the relational paradigm at a minor cost of an additional join, which will be marginally slower.

Using varchar is just plain stupid and should be avoided imho, it adds room for error and provides nothing. Eliminating a single join is almost never worth it. It also adds table bloat.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
0

By far and away the best approach is to use a lookup (or reference) table!

There are 8 excellent reasons not to use enums here! Any of them on their own would be bad, 2 would be a showstopper! Don't use enums!

The problem with free text (VARCHARs) is that your users will put all sorts of total and utter rubbish in them - you'll have status attributes of "Mary, ring Jimmy in 5 mins at 01 234 5678". Joe will have written that as a stopgap, meaning to get back and change it later - meanwhile, his wife calls, she's expecting! Joe runs back to machine, saves everything and sprints to the pub!

Lookups, taking advantage of FOREIGN KEYs, are what databases were invented for! Your data can never be inconsistent and will at least have a fighting chance of reflecting reality!

Vérace
  • 30,923
  • 9
  • 73
  • 85