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 en

Solution 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.

Solution 2:

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.

Solution 3:

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.