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.

Starting with Docker and ASP.NET Core

I love the .NET world. But for me, it always felt strange that you can run your code only inside Windows ecosystem. But now, with .NET Core, you can develop and run .NET application on Linux. Now you can use all the benefits of the Linux platform. One of such benefits is the ability to use Docker natively. Now all major cloud services offer direct Docker containers deploy. It means you shouldn't worry about dependencies and software versions - everything is inside your container . That's why I decided to learn about Docker and share it with you here. In this tutorial, you will learn what are ASP.NET Core and Docker, and how to use them together. ASP.NET Core What is ASP.NET Core? Microsoft defines ASP.NET Core as: ASP.NET Core is a cross-platform, high-performance, open-source framework for building modern, cloud-based, Internet-connected applications. What about .NET Core?  NET  Core is a framework that supports ASP.NET. It is a cross-platform, open-sour...