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

Question

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

CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO officer_ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);

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

CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');
    
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);

(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?

Answer 1

Example shown using PostgreSQL, but other RDBMS's have similar syntax

That's incorrect. It is not an ISO/IEC/ANSI SQL requirement, so the commercial databases do not provide it (you are supposed to provide Lookup tables). The small end of town implement various "extras", but do not implement the stricter requirements, or the grunt, of the big end of town.

We do not have ENUMs as part of a DataType either, that is absurd.

The first disadvantage of ENUMs is that is it non-standard and therefore not portable.

The second big disadvantage of ENUMs is, that the database is Closed. The hundreds of Report Tools that can be used on a database (independent of the app), cannot find them, and therefore cannot project the names/meanings. If you had a normal Standard SQL Lookup table, that problem is eliminated.

The third is, when you change the values, you have to change DDL. In a Normal Standard SQL database, you simply Insert/Update/Delete a row in the Lookup table.

Last, you cannot easily get a list of the content of the ENUM; you can with a Lookup table. More important, you have a vector to perform any Dimension-Fact queries with, eliminating the need for selecting from the large Fact table and GROUP BY.

Answer 2

I don't see any advantage in using ENUMS.

They are harder to maintain and don't offer anything that a regular lookup table with proper foreign keys wouldn't allow you to do.

Answer 3

ENUMS are very-very-very useful! You just have to know how to use them:

1. An ENUM uses only 4 Bytes of storage.
2. No need for additional constraint (as replacement for FK).
3. Cheaper changes of Values compared to natural values in FKs.
4. No need for additional JOIN
5. ENUMs are ordered, ex you can compare if Monday < Friday, or January is < June or Project Initiation is < Payroll.

Thus if you have a fixed list of string values, which you want to use, an ENUM is a better solution compared to a lookup table. Let's say you need to List Amino-Acids in your products, with their respective weight. Today there are ~20 Amino Acids. If you would store their full names, you'd need much more space each time then 2 Bytes. The other option is to use artificial keys and to link to a foreign table. But how would the foreign Table look like? Would it have 2 columns: ID and Amino Acid Name? And you would join that table every time? What if your main table has >40 such fields? Querying that table would involve >40 Joins.

If your database hosts 1600 Tables, 400 of which are lookup tables which just replace ENUMs, your devs will waste lots of time navigating through them (in addition to the JOINs). Yes, you can work with prefixes, schemas and such.... but why not just kick those tables out?

ENUMS are Enumerated lists / ordered. That means that if you have values which are ordered, you are actually saving the hassle of maintaining a 3 columns lookup table.

The question is rather: why do I need lookup tables then? Well, the answer is easy:

1. When your values are changing often
2. When you need to store more additional attributes --> The lookup table corresponds to a full fledged data object, and not a lookup list.
3. When you need it quick and dirty

And now the funny thing: Lookup Tables and ENUMS are not complete replacements for each other!!!! If you have a list, where the PK is single-column natural key. The list can grow or the values can change their names (for some reason), then you could define an ENUM and use it for both: PK in lookup and FK in main tables!

Example benefit: you have to change the name of a lookup key. Without using the ENUM the DBMS will have to cascade the changes to all tables, where you use this value and not just your lookup table. If you are using ENUM, then you just change the value of ENUM, and there are no changes to the data.

Answer 4

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:

CREATE DOMAIN yes_no_dom
  AS character(1)
  DEFAULT 'N'::bpchar
  NOT NULL
   CONSTRAINT yes_no_dom_check
     CHECK ((VALUE = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])));

Share and enjoy.

Answer 5

A small advantage may lie in the fact, that you have a sort of UDT when creating an ENUM. A user defined type can be reused formally in many other database objects, e.g. in views, other tables, other types, stored procedures (in other RDBMS), etc.

Another advantage is for documentation of the allowed values of a field. Examples:

• A yes/no field
• A male/female field
• A mr/mrs/ms/dr field

Probably a matter of taste. I prefer ENUMs for these kinds of fields, rather than foreign keys to lookup tables for such simple concepts.

Yet another advantage may be that when you use code generation or ORMs like jOOQ in Java, you can use that ENUM to generate a Java enum class from it, instead of joining the lookup table, or working with the ENUM literal's ID

It's a fact, though, that only few RDBMS support a formal ENUM type. I only know of Postgres and MySQL. Oracle or DB2 don't have it.

Answer 6

Advantages:

• Type safety for stored procedures: will raise a type error if argument can not be coerced into the type. Like: select court_martial('3LT') would raise a type error automatically.

• Custom coalition order: In your example, officers could be sorted without a ranking id.

Answer 7

Generally speaking, enum is better for things that don't change much, and it uses slightly fewer resources, since there's no FK checks or anything like to execute on insert etc.

Using a lookup table is more elegant and or traditional and it's much easier to add and remove options than an enum. It's also easier to mass change the values than an enum.

Answer 8

Well, you don't see, because usually developers are using enums in programming languages such as Java, and the don't have their counterparts in database design.

In database such enums are usually text or integer fields, with no constraints. Database enums will not be translated into Java/C#/etc. enums, so the developers see no gain in this.

There are very many very good database features which are rarely used because most ORM tools are too primitive to support them.

Answer 9

Another benefit of enums over a lookup table is that when you write SQL functions you get type checking.