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.
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/.
ReplyDeleteThank you! Never thought about this way of using enums. Interesting read!
DeleteBitwise 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.
DeleteAgree. 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 :)
DeleteReally 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.
ReplyDeleteYou are doing a great job. Keep it up!
Thanks for sharing a useful info.
ReplyDeleteOf all the articles I read on the topic, this was the one that best answered my question about how to store enums. Thank you!
ReplyDeletebaşakşehir
ReplyDeletebayrampaşa
beşiktaş
beykoz
beylikdüzü
32GB
Explore the ultimate voice communication platform with our Mumble Server. Experience crystal-clear audio and seamless collaboration today!
ReplyDelete