Best practices for using and persisting enums

Question

I've seen several questions/discussions here about the best way to handle and persist enum-like values (e.g. Persisting data suited for enums , How to persist an enum using NHibernate ), and I'd like to ask what the general consenus is.

In particular:

• How should these values be handled in the code?
• How should they be persisted to a database (as text/as a number)?
• What are the tradeoffs of different solutions?

Note: I moved the explanations originally included in this question to an answer.

Solution 1

I've tried to summarize my understanding. Feel free to edit this if you have any corrections. So here it goes:

In the code

In the code, enums should be handled using either the language's native enum type (at least in Java and C#), or using something like the "typesafe enum pattern". Using plain constants (Integer or similar) is discouraged, as you lose type safety (and make it hard to understand which values are legal input for e.g. a method).

The choice between these two depends on how much additional functionality is to be attached to the enum:

• If you want to put loads of functionality into the enum (which is good, because you avoid switch()ing on it all the time), a class is usually more appropriate.
• On the other hand, for simple enum-like values, the language's enum is usually clearer.

In particular, at least in Java an enum cannot inherit from another class, so if you have several enums with similar behavior which you'd like to put into a superclass, you cannot use Java's enums.

Persisting enums

To persist enums, each enum value should be assigned a unique ID. This can be either an integer, or a short string. A short string is preferred, since it can be mnemonic (makes it easier for DBAs etc. to understand the raw data in the db).

• In the software, every enum should then have mapping functions to convert between the enum (for use inside the software) and the ID value (for persisting). Some frameworks (e.g. (N)Hibernate) have limited suppport for doing this automatically. Otherwise, you have to put it into the enum type/class.
• The database should (ideally) contain a table for each enum listing the legal values. One column would be the ID(see above), which is the PK. Additional columns might make sense for e.g. a description. All table columns that will contain values from that enum can then use this "enum table" as a FK. This guarantees that incorrect enum values can never be persisted, and allows the DB to "stand on its own".

One problem with this approach is that the list of legal enum values exists in two places (code and database). This is hard to avoid and therefore often considered acceptable, but there are two alternatives:

• Only keep the list of values in the DB, generate the enum type at build time. Elegant, but means that a DB connection is required for a build to run, which seems problematic.
• Define the list of values in the code to be authoritative. Check against the values in the DB at runtime (usually at startup), complain/abort on mismatch.

Solution 2

I agree with much of what you say. One thing I'd like to append, though, about the persistence of enums: I don't believe the generation of the enums at build time from the DB values is acceptable, but I also think that the runtime check is not a good solution. I'd define a third means: have a unit test which will check the values of the enum against the database. This prevents "casual" divergence, and avoids the overhead of checking the enums against the database every time the code is run.

Solution 3

The initial article looks fine to me. Still, based on the comments, it seems some comments concerning Java enums might clarify few things.

Enum type in Java is a class by definition, but many programmers tend to forget this, because they rather relate it to "a list of allowed values" as in some other languages. It's more than that.

So, to avoid those switch statements it might be reasonable to put some code and additional methods in the enum class. There's almost never a need to create a separate "enum-like real class".

Consider also the point of documentation - do you want to document the actual meaning of your enum in the database? In the source code reflecting the values (your enum type) or in some external documentation? I personally prefer the source code.

If you want to present enum values as integers in the database due to speed or whatever reason, that mapping should also reside in the Java enum. You'll get string-name mapping by default, and I've been content with that. There's an ordinal number associated with each enum value, but using that directly as a mapping between code and database is not very bright, because that ordinal number will change if someone reorders the values in the source code. Or adds additional enum values in between existing values. Or removes some value.

(Of course, if someone changes the name of the enum in the source code, the default string-mapping goes sour too, but that's less likely to happen accidentally. And you can more easily protect against that if necessary by putting some runtime-checking and check constraints in the database as suggested here already. )

Solution 4

In the code handling for C# you've missed out defining delcaring the 0 value. I almost without fail always declare my first value as:

public enum SomeEnum
{
    None = 0,
}

So as to serve as a null value. Because the backing type is an integer and an integer defaults to 0 so it is massively useful in a lot of places to know if an enum has actually been programatically set or not.

Solution 5

Java or C# should always use enums in code. Disclaimer: My background is C#.

If the value is to be persisted to a database, the integral values of each enumeration member should be explicitly defined so that a later change in code does not accidentally alter translated enum values and thus application behavior.

Values should always be persisted to a database as integral values, to protect against enum name refactoring. Keep documentation on each enumeration in a wiki and add a comment to the database field pointing to the wiki page documenting the type. Also add XML documentation to the enum type containing a link to the wiki entry so that it is available through Intellisense.

If you use a tool to generate CRUD code it should be capable of defining an enumeration type to use for a column so that generated code objects always use enumerated members.

If custom logic needs to be applied for an enumeration member, you have some options:

• If you have an enum MyEnum, create a static class MyEnumInfo which offers utility methods to discover additional information about the enum member, by switch statements or whatever means necessary. Appending "Info" to the end of the enum name in the class name ensures that they will be next to each other in IntelliSense.
• Decorate the enumeration members with attributes to specify additional parameters. For example we have developed an EnumDropDown control which creates an ASP.NET dropdown filled with enumeration values, and an EnumDisplayAttribute specifies the nicely formatted display text to use for each member.

I have not tried this, but with SQL Server 2005 or later, you could theoretically register C# code with the database that would contain enum information and the ability to convert values to enums for use in views or other constructs, making a method of translating the data in a manner easier for DBAs to use.

View more solutions

Comments

[Miguel Ping]

The enum's int value is not guaranteed to be the same over time.

[sleske]

Also, if you use a short string, performance should be the same. A char(2) takes 2 bytes, an int usually also takes 2 or 4.

[sleske]

Agree in principle, however at least in Java "enum" is limited in that it cannot have a superclass (as noted above), so sometimes a "typesafe enum" class is probably better.

[sleske]

@Miguel Ping: The idea is to explicitly assing an ID (int or char) to each enum. Using the internally generated int of the enum is indeed very dangerous.

[sleske]

I disagree. This would only make sense if you sometimes leave variables uninitialized, which I would consider seriously bad practice. I have often seen this idea of having a "none" value, but I believe it only hides the real problem (the uninitialized variable).

[Quibblesome]

How does it hide the problem? It makes it explicit like a nullable int. I leave values uninitialised in code because I know what the CLR will set them to by default. They're still initialised its just implicit.

[sleske]

Well, it's probably a matter of style. I strongly believe in fully initializing all variables on declaration (or at most in an if-else directly after declaration). Otherwise you might forget to initialize them, especially if the code flow is complicated. See also c2.com/cgi/wiki?SingleStepConstructor.

[Justin]

There are two scenarios to support: someone reordering the enums in my file OR someone doing some refactoring (to clarify poor initial name choices) and breaking persisted data. I think the latter is more important, and ordinal is the way to go for data persistence.

[Dave Cousineau]

if meaning beyond integer values is desired in the DB, a table mapping the integer values to human readable strings is what I would use. also, yes, the integer values must not change later. shouldn't be a problem though; the only relevance that underlying enum integer values should have is that they are different from the other members of the enum. (ie: they should have no reason to ever change). if the integer values have meaning beyond unique identification, a different data structure should probably be used.

[ashes999]

+1 explicitly assigning values is the only way to avoid "corruption" when you change the enum