Database Enums

So is it better to store enumerated types in a database as a string/varchar or integer?  Well it depends, but in general as a string is your best bet.  In this post I explore the pros and cons of each.

Lists verse Enums

Before we get to that let’s first be clear that I’m talking about enums here, not lists.  Let me explain the difference.

For example let’s say to have a list of a available weight units: pounds, kilograms, grams, short tons, metric tons, long tons, stones, ounces, and etcetera.  You might be able to design your database so that the list of possible weight units is in a table.

Your application should not have any advanced knowledge of the weight units that are defined in this table.  The application reads this dynamic list at run time.  Any information needed about these weight units must come from the database.  This includes conversion factors, display names with multilingual support, flags to indicate in which situations it is appropriate to offer these units as a choice, mapping rules to external systems, and anything else the application may need.  There should be no requirements that pounds must exist as a row or that kilograms must always be ID #1.

If you can soft code every behaviour of the weight unit required by applications in the database then the best design is to soft code this in a table.  Installers may add or remove rows from this table as needed.  This is a “List” of available values, which is defined in a database table.

If the available weight units is hard coded into the application as an enum or strongly typed enum then this is an “enum” not a “list”.  The available values cannot change without changes to the programs.  Therefore the available values should not be a database table.  Adding support for new weight units requires code changes, so it is to the code that you must go to make this change.  Having a database table falsely implies that the values are easy to change.

Lookup Times

The main argument for storing enum values as strings instead of integers is for human readability.  People looking at the database can easily see what the values mean.  Which of these two tables is easier to read?

OrderID Weight WeightUnits
1 14 kg
2 23 lb
3 25 kg
4 11 lb
5 18 kg
OrderID Weight WeightUnitID
1 14 1
2 23 2
3 25 1
4 11 2
5 18 1

Storing the values as a string makes it immediately obvious to anyone looking at the database what the units are.  There is no need have external lookup tables either as database tables, database functions, or external documentation.  It simply makes the system faster and easier to use.

The principle is similar to how applications should be developed so that users can get around with a minimum number of clicks.  Adding just a few hundred milliseconds to the load time of a web page can cost thousands of dollars of lost sales.  Even if the only people looking at the database are your own employees (i.e. a “captive audience”) the lookup time is still burdensome.  Even if users have the IDs memorized, there is still a non-zero amount of time that it takes for the mental jumping jacks to make this conversion.  There is a real cost to this lookup.

Systems should be designed for human readability, not computer readability.

Validation

String enum columns usually offer better validation and data quality.  If someone enters a “jf” instead of “kg” for the units it is very obvious that a mistake has been made.  Whereas fat fingers on the number pad entering pressing 2 instead of 1 is much harder to detect.

Having a 1 digit password is not a good idea.  Making your password at least 2 or 3 characters gives much better reassurance that a monkey banging on the keyboard isn’t going to happen upon a valid, yet totally random and incorrect value.

Regardless on whether you choose to have enum values stored as integers or strings, it is a good idea to add a check constraint on the column to limit the values.  In SQL Server, the T-SQL code to add a check constraint is as follows:

Alter Table Orders
Add Constraint CHK_Orders_WeightUnits Check(WeightUnits In ('kg', 'lb'))

There are also pros and cons to adding the check constraint to the database.  The downside is that if you want to enhance your program to support a new enum value you must modify both the program and the database constraint. 

You need to time the upgrades to the application and database.  In general, update the programs to support the new values if the are read from the database, then update the database constraint to allow the new values, then update the applications (or feature toggle) to allow the new values to be written to the database.

Performance

I do acknowledge that using integers over strings gives better performance.  Databases are smaller, less data is transferred over the network, and in theory faster.  Exactly how much faster?  I’m not sure, I haven’t run the benchmarks.  Even with storing enums and strings I aim to have all my queries run in a few milliseconds.  In most cases, I would categorize using integers over strings to be a micro-optimization.

However, when creating the enum string values do try to use as little space as possible.  You don’t want to make your enum values dozens of characters long.  Always try to keep these values less than 10 characters.  Try to use abbreviations to reduce the values further down to 2 or 3 characters.  Always use varchar instead of nvarchar.

For normal table columns I don’t think there is a significant performance hit in using strings.  However, one place I might consider using an integer is if the column was used as part of an index.  Database indexes are meant to make databases go fast, therefore every effort should be made to optimize these.

However, it is rare that enum (or Boolean) columns are used in indexes.  In general those types of columns don’t make for good indexes.  The goal of a database index is to take a table with a million rows and narrow that row count down to just the few rows you are searching for.  Dates and free text (e.g. a person’s last name) are good candidates for indexes because they drill down from millions of rows to just a few - quickly.  With an enum with 5 possible values, including the enum in the index is only going to reduce the millions of rows by a fifth (still hundreds of thousands).

If there is an enum value that does significantly reduce the row count, then consider using a filtered index.  Put the enum value in the filter and not directly in the index.  In that case the string enum value isn’t stored in the index; it is just used to decide which rows to put in the index.

Create Index IX_Orders_StatusUnderReview
On dbo.Orders(OrderID)
Where (Status = 'Review')

With this query the “Select * From Orders Where (Status = ‘Review’)” does use this index even though the Status column is not an indexed column.

Print | posted on Wednesday, September 20, 2017 11:38 PM

Feedback

# re: Database Enums

Left by Robinjack at 3/1/2018 10:15 AM
Gravatar Superb and extremely energizing site. Adoration to watch. Continue Rocking. Casino dunyasi pro

# re: Database Enums

Left by Robinjack at 3/1/2018 11:47 PM
Gravatar I might want to say this site truly persuaded me to do it! Much obliged, great post. bitcoin bahis

# Neflix

Left by Netflix Password at 3/5/2018 11:32 PM
Gravatar The Netflix login Free streaming site is the first choice for the people who prefer to watch live streaming of TV shows, music, dramas and movies on the internet. Although has a simple hard and fast rule of having a premium Netflix login free free for availing its online services of media streaming

# re: Database Enums

Left by bnntgamess at 3/10/2018 12:31 AM
Gravatar I might want to say this site truly persuaded me to do it! Much obliged, great post
Play Now Free Girl Online at gamesbanat

# re: Database Enums

Left by Robinjack at 3/13/2018 10:44 AM
Gravatar I am trusting the same best exertion from you later on too. Truth be told your experimental writing aptitudes has roused me. pizzagalata.web.tr

# re: Database Enums

Left by Robinjack at 3/13/2018 10:44 AM
Gravatar Every one of the substance you specified in post is too great and can be extremely valuable. I will remember it, a debt of gratitude is in order for sharing the data continue redesigning, looking forward for more posts.Thanks rada hukuk

# re: Database Enums

Left by Robinjack at 3/16/2018 3:40 AM
Gravatar I discovered your this post while hunting down data about web journal related exploration ... It's a decent post .. continue posting and upgrading data. Joshlevine.online

# re: Database Enums

Left by Robinjack at 3/16/2018 3:40 AM
Gravatar I've perused some well done here. Certainly worth bookmarking for returning to. I amaze the amount of exertion you put to make such an awesome useful site. pokernerde.com

# re: Database Enums

Left by Robinjack at 3/17/2018 2:03 AM
Gravatar I discovered this is an educational and intriguing post so i suspect as much it is extremely helpful and proficient. I might want to thank you for the endeavors you have made in composing this article. https://bestcloudminingreviews.com/redeem-hashflare-promo-coupon-code/

# re: Database Enums

Left by Robinjack at 3/17/2018 2:04 AM
Gravatar A debt of gratitude is in order for sharing the post.. folks are universes best individual in every existences of individual..they need or must succeed to manage needs of the crew. default gateway

# re: Database Enums

Left by Robinjack at 3/19/2018 4:41 AM
Gravatar Simply a grinning visitant here to share the affection (:, btw remarkable Video Games

# re: Database Enums

Left by Robinjack at 3/20/2018 1:35 AM
Gravatar Really nice pattern and excellent content, very little else we want . price change refunds

# re: Database Enums

Left by Robinjack at 3/22/2018 11:51 AM
Gravatar "Satta Matka is the best site where you can get fastest Satta Matka results, Matka Boss, Matka Charts, free Kalyan Matka tips and Mumbai Matka tips with 100% fix Matka number from Satta Matka. Recover every kind of loss from Kalyan Matka, Milan Day, Milan Night, Rajdhani Day, Rajdhani Night Matka by SattaMatka .Buzz, All Satta Market's Live results are available with us and we provide fix Satta Matka Numbers. We are the most applauded website for offering the Satta Matka Live updates at the earliest.
For updates on live and the fastest Satta Matka results, upfates for Time Bazar, Madhur Matka. Milan Matka, Rajdhani Matka, our platform will give you that.
We are the most popular online Satta Matka website that provides the fastest Satta Matka results and Satta Matka number. Stay in touch with our website for the fastest Kalyan Matka or Satta Matka Tips. This is the worlds most famous Matka website loved by people engaged in Satta Bazar in Mumbai Matka. We SattaMatka.Buzz Provide Faster results than any other matka site with free fix satta matka lucky numbers. Please Bookmark Our Link https://sattamatka.buzz to get our daily updates visit us" satta matka

# re: Database Enums

Left by HadleyG at 3/24/2018 10:13 AM
Gravatar Thanks for the website !
nourriture bio

# re: Database Enums

Left by Robinjack at 3/26/2018 12:52 AM
Gravatar Your work is great and I welcome you and jumping for some more enlightening posts. Much obliged to you for sharing extraordinary data to us. gelinlik kizlar web

# re: Database Enums

Left by Robinjack at 3/26/2018 12:52 AM
Gravatar I have been looking at a couple of your stories and i can state really well done. I will bookmark your website twitch nedir

# re: Database Enums

Left by gapozodom at 4/9/2018 3:24 PM
Gravatar And last but not least, the durability. No other footwear provides you as much durability as boots. Instead of buying several pairs of footwear every year, you can get one of best winter boots and get done with it. Hope you found the reasons to get one of these. Go ahead!

# re: Database Enums

Left by Robinjack at 4/27/2018 8:50 AM
Gravatar there are many good family resorts that you can find both online and offline, some are very cheap too“ wormax.io

# Nice article

Left by Misty at 5/2/2018 9:46 PM
Gravatar there are many online loot deals are available in online shopping market you can find here loot deals

# re: Database Enums

Left by onedollar webhostings at 5/4/2018 4:40 AM
Gravatar your post is nice and useful. I hope you post more updates. visit my site for discount for best web hosting services
godaddy 12 dollar Hosting Thanks for visiting.

# re: Database Enums

Left by Raushan at 5/18/2018 8:46 AM
Gravatar Hii Friends If You are Finding Satta Matka Best Site Then Your Search Is Over Here Then Your Search is Over Here.

# re: Database Enums

Left by Gyan at 5/18/2018 10:48 AM
Gravatar Indias Best Satta Matka Sites Where You Never Get Any kinds Of Loss
Satta Matka

Your comment:





 

Copyright © Timothy Klenke

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski