JPA Enum ORDINAL vs STRING

Question

It's possible to define enumerations in JPA using either

@Enumerated(EnumType.ORDINAL)

or

@Enumerated(EnumType.STRING)

I wonder what are advantages and disadvantages of those two definitions?

I heard that ORDINAL performs better (is faster) than STRING with EclipseLink.
Is that true?

Solution 1

I always go STRING.

Speed is rarely the most important issue - readability and maintainability are more important.

I use STRING because it's a lot easier to manually inspect rows from the database, but more importantly, I can do two things, without touching the database, the ORDINAL can't handle:

1. I can change the order of my enums
2. I can insert new enums in the middle of the enum list

Both of these changes will alter the ordinal values of the enums already in use in the database, thus breaking existing data if you are using ORDINAL.

If you change an enum value (not that common), handling it is simple:

UPDATE table SET enum_column = 'NEW_ENUM_NAME' where enum_column = 'OLD_ENUM_NAME';

Solution 2

It's likely that ORDINAL is more efficient, but that's minor. There are a few downsides to ORDINAL:

• it is less readable in the database
• if you reorder your enum definitions the database will not be consistent.

With STRING you can't rename your enums.

Pick one of them and use it throughout the whole application - be consistent.

If your database is going to be used by other clients/languages - use STRING, it's more readable.

Solution 3

I prefer the use of Ordinal but this really depends on the use.

By example:

You have a enum, to save all your user states, in this case the order doesn't matter, and you can add more states in the future (Best use is @Enumerated(EnumType.ORDINAL)):

public enum UserStates { ACTIVE, DELETED, PENDING }

But now, you have an enum, to save the Plantes in the Solar System (Best use @Enumerated(EnumType.STRING)):

public enum Planets {MERCURY,VENUS,EARTH,MARS,JUPITER,SATURN,URANUS,NEPTUNE,PLUTO,NINE}

Now think that you want reorder your planets, with @Enumerated(EnumType.ORDINAL) you can't, because your database can't know the new order in your Java file.

You can reorder your Plantes using @Enumerated(EnumType.STRING) because your Planet is linked to the enum name, not the enum order.

Anyway, you can modify your @Enumerated(EnumType.ORDINAL)enums because they are linked to the order, but you can't change your @Enumerated(EnumType.STRING)enums because they will use like new enums.

String types are more readable in the Database, but will occupy more size than an ordinal data. Maybe are useful if the database is used by more clients, but it's better have a good documentation of the software than save 1000 times "EARTH" than "4"

USERSTATE
------------
ID | STATE |
------------
1 | 1
2 | 2
3 | 1

Planets
------------
ID | Name |
------------
1 | EARTH
2 | EARTH
3 | MARS
4 | EARTH

Solution 4

This is a good question. In the past I used String but today my preference is to use Ordinal.

The main disadvantage for the String is for the DBAs. With the String they have no idea what are the possible values of the column, because this information is in the application code. The DBA only can have some idea about the possible values grouping the existent information on the table, but he will never be sure about the other possible values until the application insert them on the table.

In the Ordinal you have the same problem above. But my preference for Ordinal came to a solution to the DBA problem that seems natural to the database. You can create a new table to show the possible values of the Enumerator on database, with a foreign key between the column (ordinal enum value) and this new table. This strategy is described and implemented here.

About the problem that someone could reorder the Enumerator and break the system, a simple unit test can deal with this problem and guarantee that no one will reorder them without a very good error. The same idea is valid on renaming the Enumerator. So, renaming (on String) or reorder (on Ordinal) accidentally it is not really a strong argument against String or Ordinal approach.

By the way, for my perspective the developers have more necessity to rename than reorder an Enumerator, so I count this as one more positive point to use Ordinal.

So, with this extra table approach, you solve the main problem of the Ordinal (now, is readable) and the information will occupy less space on the database (and your DBA will be happy).

View more solutions

Comments

[Bozho]

on the other hand you can't rename your enums. ;) I wouldn't say there is a preferred one.

[Bohemian]

sure you can: `UPDATE table SET enum_column = 'NEW_ENUM' where enum_column = 'OLD_ENUM'

[Admin]

renaming Enums would be just as catastrophic as renaming columns in a table or a table for that matter, it has long and far reaching non-trivial effects, I don't think it is a concern, because big changes like that will be thought through carefully. You are much more likely to be adding new Enums and not removing or renaming them, and not having to worry about changing the ordinal positions is much more important to external code, that can't be checked at compile time, renaming can. In the end the client code should not have to care about which you use, Names are easier to maintain.

[Bozho]

not if someone is unaware that the enum name is used in the db. I would say I rename enums more often than I reorder them. And you can the this UPDATE query with ordinals as well

[Bohemian]

"catastrophic" is too emotive a term to describe renaming an enum - see answer for simple handling.

[earcam]

(sarcastic comment warning) Yes so much better to have to look through code to find what that ordinal value actually means.... Oh and don't forget ordering - great when somebody inserts a new value in the middle/top of the enum. Don't let the implementation dictate design. @Enumerated(ORDINAL) considered harmful

[Sanghyun Lee]

How to update the enum name in the DB and in the code at the same time?

[Bohemian]

@Sangdol that would be a release task: after renaming an enum, as you release the code run an sql script to update the enum name used: update mytable set myenumcol = 'newname' where myenumcol = 'oldname'

[1in9ui5t]

Ordinal has the advantage of always working with @OrderBy, whereas with String, ordering is alphabetical (I'm speaking of db SELECT statement here).

[Bohemian]

@1in9ui5t interesting point, although most databases have ways of ordering by an enum. In particular, if you implement the column as a mysql enum too, ordering is by enum order and if a plain string you can order by find_in_set(mycolumn, 'ENUM_NAME_1', 'ENUM_NAME_2', ...) (a little ugly, but simple enough)

[1in9ui5t]

@Bohemian I wasn't aware of the ENUM type in databases. That solves the ordering issue. Just have to include the Enum values in the column definition.

[Ville Myrskyneva]

Maintainability & readability is far more important than what you said. Such things (space) have virtually no cost at all in means of the service speed and memory consumption. When you end up solving an issue because of such things, this immediately costs you (more than the space).

[dwilda]

@earcam (sarcastic response) you can always add comment on column and read it every time you need. Is this that difficult to read? IMHO this ticket does not respond to the question, both cases have their pros and cons

[earcam]

@dwilda haha, fair enough. But surely cardinality + (compound) indexing mitigates any performance concerns... mismatch is bad enough already 😉