One of the starting points for any web application in my career is to identify the entities that make up the entire application and how they interact with each other. More importantly, there are always business rules that dictate how these entities behave in the system. This means that validation is an important consideration to keep in mind.
There is nothing more frustrating and demoralising than having to decipher what a piece of code does, especially when you’re on a tight schedule. This is why I like using enums in some situations. Enum is short for enumerated types. In programming, it is a special variable type that defines a set of named constants.
I am currently building an ordering and subscription system for a small ice cream business and there are some business rules related to logistics limitations of delivering ice cream. As you can imagine, ice cream is a highly perishable product and once the cold chain is broken, it loses value. The business can only deliver to two cities in close proximity to each other. I decided to set the city column’s data type to an enum in the shipping addresses table, because there will only ever be two options from which to choose.
In MySQL, an ENUM is a string object, through which you can iterate one by one exactly as it is defined when the table is created. For example, you can’t have an ENUM with the values of ‘small’, ‘medium’, ‘large’, and then try to access a value of ‘x-large’, because it hasn’t been defined. Enums in MySQL also have an index automatically assigned to it.
Now that we know what an enum is in MySQL, I want to discuss when it is OK to use them from previous experience, and when it is not OK to do so.
Why use enums in MySQL
1. Database validation and type safety layer
If you’ve been building web apps for some time, you realise how important validation is and enforcing it at multiple layers of your application might be overkill, but I like to do it anyway, because you never know if a malicious user might attempt to breach your defences.
I could have used VARCHAR for my city column, and this certainly allows more flexibility in extending the business at a later stage, but what if a user manages to set their city to somewhere the business doesn’t deliver? You can probably imagine what a mess that would result in for the business owner, having to deal with an upset customer after they paid for their order, not to mention the headache the business owner now has to deal with.
This extra layer of data validation helps enforce business rules on another level by providing clear indications of what an entity can consist of.
Type safe languages help prevent bugs in systems and I’ve seen this in many of the apps I’ve worked on. There is a fine balance between flexibility and stability and you have to decide which way you want to go with your code. Too much of either causes future problems that are not fun to deal with and waste time.
2. Small projects that aren’t frequently changed
For small projects such as this artisanal small batch ice cream manufacturer, the use case for enums as a data type for certain columns is ideal. Some would argue that it makes it difficult to alter the schema, as discussed below under why not to use enums, however the likelihood of this happening is extremely low for this particular business.
I've worked on some projects where it made sense to use enums for some data. At some point I thought it a brilliant idea to store user roles as an enum in the users table, but after having to add additional roles, it became a pain to manage. In this case it is better to have a dedicated roles table, with a pivot table called role_user to connect the user and roles tables. To ensure proper database normalisation, this is the more acceptable way to deal with roles in an application.
3. Self-documenting database
As a programmer, you’ve probably heard of the anti-pattern of using magic numbers. This is when a programmer uses a number such as an integer directly in their code as opposed to a named constant. For example, you have a selection of address types, and assign 1 to represent “residential” and 2 to represent “commercial”. If you come back months later and read your code, you will have to spend some extra time digging around to remember what these numbers represent. This has happened to me as a junior before, I’ve burned myself and learned my lesson.
An arbitrary example, however I’ve seen this in large-scale enterprise web apps and it is an absolute nightmare to keep track of. To make it easier on the human brain, readability is important in any code base, be it large or small. This is not exclusive to databases. Making the schema easier to understand will help you decipher the business rules and build a more robust front end and back end.
In the end saving developers' cognitive energy and time will end up costing you much less money.
4. Data integrity
As explained above, data integrity problems can result in angry customers and therefore lead to financial and reputational harm for your business. Ensuring only valid values are inserted into the database, enums help provide this kind of security.
From professional experience, this gives me as a web application architect peace of mind to know the data that is persisted is sound.
Why not to use enums in MySQL
1. Internal numeric indices can cause havoc with sorting and ordering
Enums are stored internally in the database management system as integer values, and are therefore sorted by their index numbers. These depend on the order in which the items were listed when the column was created.
For example, if I create a table with a size column, 'x-large' will sort before 'medium' and not according to alphabetic order. The example below will sort as it is defined.
CREATE TABLE t_shirts (
size ENUM( 'large', 'x-large', 'small', 'medium')
);
If you want to sort it alphabetically, define it alphabetically instead at creation. According to the MySQL developer documentation, another technique is to make sure the column is sorted lexically by ordering by casting the column as a CHAR.
An empty string always sorts before nonempty strings and NULL will sort before all the other values.
2. Schema changes are required if a business requirement changes
Once defined, it will take some extra work to update the table schema. It's not necessarily difficult, however it can be inconvenient.
If you're working with something like Laravel, simply creating a new migration and altering the existing enum will be sufficient, but this requires steps in both the application code and the database.
I usually define enum classes in my Laravel applications, and doing so will lead to other updates in the application code as well. If you have a large code base with many test cases, you will have to refactor in many places in the code if you use it extensively.
3. Translation and localisation might become really tricky with enums
Imagine you have a web app that has been developed in English, and after growing substantially and internationally, some of your users ask for a user interface in their native, preferred language.
You'd have to create an additional column with the translated enums, or a new table with these and corresponding values. Keeping track of all of this data can become a messy spaghetti in your database, and mess up your sorting and indexing with enum values.
The ordering of enums at creation is important if you want to order the values alphabetically and this fact must be kept in mind when creating a localised version of your enum column.
Carefully consider who the audience of your application is and whether it is worth the investment to localise before jumping into a localisation project.
Conclusion
In essence, I really like using enums, but I use my foresight and available information from my stakeholders when designing a database for the app they need.
Data integrity is extremely important in any business' database, and this is the main reason why I would pick an enum for storing short-form string values over a VARCHAR, or even a JSON column.
If you would like to collaborate with me on an idea for a web app you have, please reach out to me via LinkedIn or send an email to work (at) chilldsgn.com. I'd be thrilled to help you get your next idea online with a robust, easy-to-maintain database.