Skip to main content

Pros and cons of different ways of storing Enum values in the database

Lately, I was experimenting with Dapper for the first time. During these experiments, I've found one interesting and unexpected behavior of Dapper for me. I've created a regular model with string and int fields, nothing special. But then I needed to add an enum field in the model. Nothing special here, right?
Long story short, after editing my model and saving it to the database what did I found out? By default Dapper stores enums as integer values in the database (MySql in my case, can be different for other databases)! What? It was a surprise for me! (I was using ServiceStack OrmLite for years and this ORM by default set's enums to strings in database)
Before I've always stored enum values as a string in my databases!
After this story, I decided to analyze all pros and cons I can imagine of these two different ways of storing enums. Let's see if I will be able to find the best option here.

Pros of storing enum value as an integer

1. Size

The first pros I can imagine is, of course, size. Integer values will store much less space on a hard drive than any string (4 bytes against 8 bytes per symbol for varchar or 16 bytes per symbol for nvarchar).

2. Errorproof

When you are using integers for enum chances that you will make a typo are much lower than with strings. Usually, enums are not having big lists of keys. The biggest enum I've ever seen was 300 key in it. which is only 3 symbols. I have rarely met enum names with 3 or fewer symbols.

3. Filtering speed

Not so much, but comparing integers is a little bit faster. Plus you will never use LIKE operation here.

Pros of storing enum value as a string

1. High readability

If you have an enum with 300 keys in it, how fast will you find out what does the key 174 means? Or key 123? I think you will spend a couple of minutes to open the file and find out this value there. But what if I will ask you what does the value "Brazil" means? Or "France"? From the value, you see that it's a country's names.

2. Better support

This point goes out directly from the first point. If you can read and understand values faster - you can better support your data without any extra tool that will translate integer to value for you.

3. Freedom to reorder enums in code

When you are storing enums as a string you are not depend on the order of enums in code, so you can easily reorder them without any side affects.
To avoid this you can implicitly specify integer values for enums:
public enum MyEnum
{
    Value1 = 0,
    Value2 = 1,
    Value3 = 2
}

But still be careful with changing enums!

Summary

So what's in the end? You should decide for yourself. As you can see all the pros of the one method are cons of another at the same time. So you should decide for you what is better for your specific case.
But if you will access your database through an interface (like the dashboard) - better you an integer. It will help you save some space on a hard drive and make filtering operations faster.
If you often look into the database and check something in there - better store enums as a string. In this case, you won't be wasting time every time you don't remember the meaning of the number.
Rule of thumb is:
If people will see it - try to make it human readable, if only computers will access it - make it machine readable.


In my cases, I was an often database visitor, so I chose better support than speed and database size. But you should decide for yourself.

Thank you for reading! I hope you enjoyed it.

Comments

  1. One reason to store values as an integer is bit-wise operations, a use case is described in this article: https://www.alanzucconi.com/2015/07/26/enum-flags-and-bitwise-operators/.

    ReplyDelete
    Replies
    1. Thank you! Never thought about this way of using enums. Interesting read!

      Delete
    2. Bitwise is only for flags. By that I mean when a value can be a combination of enums. It does not make a whole lot of sense to use a database that way instead of just using a one to many relationship.

      Delete
    3. Agree. In database it will be for sure one-to-many connection. But I've never thought about using enums for bitwise operation. One more trick in collection :)

      Delete
  2. Really Very very happy to say, your blog is very intersting to read. Business need online platforms and mobile applications because they impact how customers reach you for your products and services. Software development is much more than just a website or an application. There are many benifits you can get by improving your software.
    You are doing a great job. Keep it up!

    ReplyDelete
  3. Of all the articles I read on the topic, this was the one that best answered my question about how to store enums. Thank you!

    ReplyDelete
  4. Explore the ultimate voice communication platform with our Mumble Server. Experience crystal-clear audio and seamless collaboration today!

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Build TypeScript App and Deploy it on GitHub Pages

Quick Summary In this post, I will show you how to easily build and deploy a simple TicksToDate time web app like this: https://zubialevich.github.io/ticks-to-datetime .

Caching strategies

One of the easiest and most popular ways to increase system performance is to use caching. When we introduce caching, we automatically duplicate our data. It's very important to keep your cache and data source in sync (more or less, depends on the requirements of your system) whenever changes occur in the system. In this article, we will go through the most common cache synchronization strategies, their advantages, and disadvantages, and also popular use cases.

How to maintain Rest API backward compatibility?

All minor changes in Rest API should be backward compatible. A service that is exposing its interface to internal or/and external clients should always be backward compatible between major releases. A release of a new API version is a very rare thing. Usually, a release of a new API version means some global breaking changes with a solid refactoring or change of business logic, models, classes and requests. In most of the cases, changes are not so drastic and should still work for existing clients that haven't yet implemented a new contract. So how to ensure that a Rest API doesn't break backward compatibility?