0

I have a field that should contain the place of a seminar. A seminar can be held:

  • In-house
  • In another city
  • In another country

At first, I had only one table, so I have used an enum. But now I have three tables which can't be merged and they all should have this information and customer wants this field to be customizable to add or remove options in the future. But the number of options will be limited they say, probably 5 or so.

Now, my question is, should I use an enum or a table for this field? More importantly, what would be the proper way to decide between an enum or a table?

PS: enum fields are dynamically retrieved from the database, they are not embedded in the code.

oguzkonya
  • 53
  • 6
  • Some your code for the types. Hopefully you haven't nested your `enum` in the first class that used it. – Yuck Apr 24 '12 at 14:49
  • Yes, MySQL, but would it make any difference if it were Oracle or Postgre? – oguzkonya Apr 24 '12 at 14:52
  • This is a duplicate of another more complete question: http://stackoverflow.com/questions/4293476/sql-advantages-of-an-enum-vs-a-one-to-many-relationship – PPC Aug 08 '12 at 18:53

3 Answers3

1

As a general rule of thumb, in an application, use an enum if:

  1. Your values change infrequently, and
  2. There are only a few values.

Use a lookup table if:

  1. Values changes frequently, or
  2. Your customer expects to be able to add, change, or delete values in realtime, or
  3. There are a lot of values.

Use both if the prior criteria for an enum are met and:

  1. You need to be able to use a tool external to your application to report on the data, or
  2. You believe you may need to eliminate the enum in favor of a pure lookup table approach later.

You'll need to pick what you feel is a reasonable cut of for the number of values for your cutoff point, I often hear somewhere between 10 and 15 suggested.

If you are using an enum construct provided by your database engine, the first 2 groups of rules still apply.

In your specific case I'd go with the lookup table.

JamieSee
  • 12,696
  • 2
  • 31
  • 47
  • I'd said that enums should be used for internal logic (be it database or application level routines) and lookup tables should be used when customers has overview of the schema. – vyegorov Apr 24 '12 at 15:16
  • According to your answer, all the criteria for the enum is met in my case, but you say you would choose the lookup table. Is it because they appear in three tables? – oguzkonya Apr 24 '12 at 15:50
  • It's because your customer desires the ability to change it. My experience has been that for things like this, they don't usually want to wait for a programmer to change it for them. If your customer is ok with that then you could go with an enum. – JamieSee Apr 24 '12 at 15:52
0

If the elements are fixed in that case enum should be used if in case of variable data then enum should not be used. In those cases table is more preferable.

I think in your case table should be ok.

Ketan Bhavsar
  • 5,338
  • 9
  • 38
  • 69
0

I agree with the top answers about enums being lean, slighty faster and reduce complexity of your database when used reasonably (that is without a lot of and fastchanging values).

BUT: There are some enormous caveats to consider:

  • The ENUM data type isn't standard SQL, and beyond MySQL not many other DBMS's have native support for it. PostgreSQL, MariaDB, and Drizzle (the latter two are forks of MySQL anyway), are the only three that I know of. Should you or someone else want to move your database to another system, someone is going to have to add more steps to the migration procedure to deal with all of your clever ENUMs. (Source and even more points on http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/)

  • ENUMS aren't supported by most ORMs such as Doctrine exactly because they're not SQL Standard. So if you ever consider using an ORM in your project or even use something as the Doctrine Migrations Bundle you'll probably end up writing a complex extension bundle (which I've tried) or using an existing like this one which in Postgresql for example cannot even add more tha a pseudosupport for enums: by treating enums as string type with a check constraint. An example:

    CREATE TABLE test (id SERIAL NOT NULL, pseudo_enum_type VARCHAR(255) CHECK(pseudo_enum_type IN ('value1', 'value2', 'value3')) , ...

So the gain of using Enums in a bit more complex setups really is below zero.

Seriously: If I don't absolutely have to (and I don't) I'd always avoid enums in a database.

luba
  • 104
  • 4