Skip to content
Advertisement

SQL: Advantages of an ENUM vs. a one-to-many relationship?

I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:

But the same thing can also be shown using a user-defined type / ENUM:

(Example shown using PostgreSQL, but other RDBMS’s have similar syntax)

The biggest disadvantage I see to using an ENUM is that it’s more difficult to update from within an application. And it might also confuse an inexperienced developer who’s used to using a SQL DB simply as a bit bucket.

Assuming that the information is mostly static (weekday names, month names, US Army ranks, etc) is there any advantage to using a ENUM?

Advertisement

Answer

A disadvantage of using something like an ENUM is that you can’t get a list of all the available values if they don’t happen to exist in your data table, unless you hard-code the list of available values somewhere. For example, if in your OFFICERS table you don’t happen to have an MG on post there’s no way to know the rank exists. Thus, when BG Blowhard is relieved by MG Marjorie-Banks you’ll have no way to enter the new officer’s rank – which is a shame, as he is the very model of a modern Major General. 🙂 And what happens when a General of the Army (five-star general) shows up?

For simple types which will not change I’ve used domains successfully. For example, in one of my databases I’ve got a yes_no_domain defined as follows:

Share and enjoy.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement