oguzkonya oguzkonya - 9 months ago 33
SQL Question

Which is reasonable to use for a field that appears in three tables: an enum or a table?

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.

Answer Source

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.