[ACCEPTED]-How to handle enumerations without enum fields in a database?-enums

Using a foreign key to a lookup table is the approach 34 I use. In fact, I use this even when I 33 do use a database that supports ENUM (e.g. MySQL).

For 32 simplicity, I may skip the ever-present 31 "id" for the lookup table, and just use the 30 actual value I need in my main table as 29 the primary key of the lookup table. That 28 way you don't need to do a join to get the 27 value.

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW'), ('OPEN'), ('FIXED');

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  summary           VARCHAR(80),
  ...
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

Admittedly, storing strings takes 26 more space than MySQL's implementation of 25 ENUM, but unless the table in question has millions 24 of rows, it hardly matters.

Other advantages 23 of the lookup table are that you can add 22 or remove a value from the list with a simple 21 INSERT or DELETE, whereas with ENUM you have to use ALTER TABLE to 20 redefine the list.

Also try querying the 19 current list of permitted values in an ENUM, for 18 instance to populate a pick-list in your 17 user interface. It's a major annoyance! With 16 a lookup table, it's easy: SELECT status from BugStatus.

Also you can 15 add other attribute columns to the lookup 14 table if you need to (e.g. to mark choices 13 available only to administrators). In an 12 ENUM, you can't annotate the entries; they're 11 just simple values.

Another option besides 10 a lookup table would be to use CHECK constraints (provided 9 the database supports them -- MySQL doesn't 8 support CHECK until version 8.0.16):

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  summary           VARCHAR(80),
  ...
  status            VARCHAR(20) NOT NULL
    CHECK (status IN ('NEW', 'OPEN', 'FIXED'))
);

But 7 this use of a CHECK constraint suffers from the 6 same disadvantages as the ENUM: hard to change 5 the list of values without ALTER TABLE, hard to query 4 the list of permitted values, hard to annotate 3 values.

PS: the equality comparison operator 2 in SQL is a single =. The double == has no 1 meaning in SQL.

To restrict the possible values I would 11 use a foreign key to a table that holds 10 the enumeration items.

If you don't want 9 to JOIN to do your searches then make the 8 key a varchar if JOINS are not a problem 7 then make the key an INT and don't join 6 unless you need to search on that field.

Note 5 that putting your enumerations in the DB 4 precludes compile time checking of the values 3 in your code (unless you duplicate the enumeration 2 in code.) I have found this to be a large 1 down side.

You basically have two options :

• use an integer 6 field

• use a varchar field

I would personally 5 advocate the use of varchars, because you 4 won't break anything if you change your 3 enum + the fields are human-readable, but 2 ints have some pro aswell, namely performance 1 (the size of the data is an obvious example)

This is what I did recently

In my hibernate 11 mapped POJO- I kept the type of the member 10 as String and it is VARCHAR in the database.

The 9 setter for this takes an enum There is another 8 setter which takes String- but this is private 7 (or you can map the field directly- if that's 6 what you prefer.)

Now the fact I am using 5 String is encapsulated from all. For the 4 rest of the application- my domain objects 3 use enum. And as far as the database is 2 concerned- I am using String.

If I missed 1 your question- I apologize.

If you use Spring JPA 2.1 or later with 5 hibernate you can implement your own AttributeConverter 4 and define how your enum maps to column 3 values

@Converter(autoApply = true)
public class CategoryConverter implements AttributeConverter<Category, String> {
 
    @Override
    public String convertToDatabaseColumn(Category category) {
        if (category == null) {
            return null;
        }
        return category.getCode();
    }

    @Override
    public Category convertToEntityAttribute(String code) {
        if (code == null) {
            return null;
        }

        return Stream.of(Category.values())
          .filter(c -> c.getCode().equals(code))
          .findFirst()
          .orElseThrow(IllegalArgumentException::new);
    }
}

Please see the 4th solution in the 2 article at Persisting Enums in JPA.The code snippet is also from 1 there.