Ways to save enums in database

Question

Hey guys.

I am wondering what the best ways to save enums into a database is.

I know there are name() and valueOf() methods to make it into a String back. But are there any other (flexible) options to store these values?

Is there a smart way to make them into unique numbers (ordinal() is not safe to use)?

Any comments and suggestions would be helpful :)

Update:

Thanks for all awesome and fast answers! It was as I suspected.

However a note to 'toolkit'; That is one way. The problem is that I would have to add the same methods with each enum type i create. Thats a lot of duplicated code and, at the moment, Java does not support any solutions to this (You cannot let enum extend other classes).

However, thanks for all answers!

Answer 1

I would argue that the only safe mechanism here is to use the String name() value. When writing to the DB, you could use a sproc to insert the value and when reading, use a View. In this manner, if the enums change, there is a level of indirection in the sproc/view to be able to present the data as the enum value without "imposing" this on the DB.

Answer 2

What kind of database? MySQL has an enum type, but I don't think it's standard ANSI SQL.

Answer 3

We never store enumerations as numerical ordinal values anymore; it makes debugging and support way too difficult. We store the actual enumeration value converted to string:

public enum Suit { Spade, Heart, Diamond, Club }

Suit theSuit = Suit.Heart;

szQuery = 
      "INSERT INTO Customers (Name, Suit)"+Environment.NewLine+
      "VALUES ('Ian Boyd', "+QuotedStr(theSuit.ToString())+")";

and then read back with:

Suit theSuit = Enum.Parse(typeof(Suit), reader["Suit"]);

The problem was in the past staring at Enterprise Manager and trying to decipher:

Name                Suit
==================  ==========
Shelby Lake         2
Ian Boyd            1

verses

Name                Suit
==================  ==========
Shelby Lake         Diamond
Ian Boyd            Heart

the latter is much easier. The former required getting at the source code and finding the numerical values that were assigned to the enumeration members.

Yes it takes more space, but the enumeration member names are short, and hard drives are cheap, and it is much more worth it to help when you're having a problem.

Additionally, if you use numerical values, you are tied to them. You cannot nicely insert or rearrange the members without having to force the old numerical values. For example, changing the Suit enumeration to:

public enum Suit { Unknown, Heart, Club, Diamond, Spade }

would have to become :

public enum Suit { 
      Unknown = 4,
      Heart = 1,
      Club = 3,
      Diamond = 2,
      Spade = 0 }

in order to maintain the legacy numerical values stored in the database.

Answer 4

As you say, ordinal is a bit risky. Consider for example:

public enum Boolean {
    TRUE, FALSE
}

public class BooleanTest {
    @Test
    public void testEnum() {
        assertEquals(0, Boolean.TRUE.ordinal());
        assertEquals(1, Boolean.FALSE.ordinal());
    }
}

If you stored this as ordinals, you might have rows like:

> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF

"Alice is a boy"      1
"Graham is a boy"     0

But what happens if you updated Boolean?

public enum Boolean {
    TRUE, FILE_NOT_FOUND, FALSE
}

This means all your lies will become misinterpreted as 'file-not-found'

Better to just use a string representation

Answer 5

We just store the enum name itself - it's more readable.

We did mess around with storing specific values for enums where there are a limited set of values, e.g., this enum that has a limited set of statuses that we use a char to represent (more meaningful than a numeric value):

public enum EmailStatus {
    EMAIL_NEW('N'), EMAIL_SENT('S'), EMAIL_FAILED('F'), EMAIL_SKIPPED('K'), UNDEFINED('-');

    private char dbChar = '-';

    EmailStatus(char statusChar) {
        this.dbChar = statusChar;
    }

    public char statusChar() {
        return dbChar;
    }

    public static EmailStatus getFromStatusChar(char statusChar) {
        switch (statusChar) {
        case 'N':
            return EMAIL_NEW;
        case 'S':
            return EMAIL_SENT;
        case 'F':
            return EMAIL_FAILED;
        case 'K':
            return EMAIL_SKIPPED;
        default:
            return UNDEFINED;
        }
    }
}

and when you have a lot of values you need to have a Map inside your enum to keep that getFromXYZ method small.

Answer 6

For a large database, I am reluctant to lose the size and speed advantages of the numeric representation. I often end up with a database table representing the Enum.

You can enforce database consistency by declaring a foreign key -- although in some cases it might be better to not declare that as a foreign key constraint, which imposes a cost on every transaction. You can ensure consistency by periodically doing a check, at times of your choosing, with:

SELECT reftable.* FROM reftable LEFT JOIN enumtable ON reftable.enum_ref_id=enumtable.enum_id WHERE enumtable.enum_id IS NULL;

The other half of this solution is to write some test code that checks that the Java enum and the database enum table have the same contents. That's left as an exercise for the reader.

Answer 7

If saving enums as strings in the database, you can create utility methods to (de)serialize any enum:

   public static String getSerializedForm(Enum<?> enumVal) {
        String name = enumVal.name();
        // possibly quote value?
        return name;
    }

    public static <E extends Enum<E>> E deserialize(Class<E> enumType, String dbVal) {
        // possibly handle unknown values, below throws IllegalArgEx
        return Enum.valueOf(enumType, dbVal.trim());
    }

    // Sample use:
    String dbVal = getSerializedForm(Suit.SPADE);
    // save dbVal to db in larger insert/update ...
    Suit suit = deserialize(Suit.class, dbVal);

Answer 8

Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.

Suits table:

suit_id suit_name
1       Clubs
2       Hearts
3       Spades
4       Diamonds

Players table

player_name suit_id
Ian Boyd           4
Shelby Lake        2

1. If you ever refactor your enumeration to be classes with behavior (such as priority), your database already models it correctly
2. Your DBA is happy because your schema is normalized (storing a single integer per player, instead of an entire string, which may or may not have typos).
3. Your database values (suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.