Why we should not use enums in databases.

The problem with enums in databases are many fold. They are tempting because it seems like they make your database more readable, however. I avoid using them and as of today I’ve never found a good use for them which doesn’t fall foul of one of the many problems that designing a database which contains enums leaves you open to.

Enums fall into that area of programming that seems to make things easier, but comes with a whole load of problems that will hit you later.

Making the database more readable is a Non sequitur, databases do not need to be readable but data models need to be consistent and well ordered.

Why I don’t use ENUMs8. First, it’s not standard SQL (at least to my knowledge), and historically hasn’t been consistent between Database products. More importantly as data, it’s terrible to maintain easily, as it’s within the table structure.

The problems with enums in database

Firstly they look like text, which is a bad thing because we understand text and databases don’t really, so we can read them (providing that they are in our native language) and easily see what they mean. However this is only important during development, and besides you get to know what the value 212 means after a while.

  • Enums are case sensitive, spaces are significant.
  • Enums are sometimes not comparable
  • Enums do not allow translation into other languages.
  • Enums are not ANSI standard and are therefore implemented differently (if at all) in different database engines.
  • When using a text string to compare within code it is possible to misspell it and the language will not warn you.
  • Enums are ordered in sorts the order in which they were created.
  • Enums are not easily maintained, and lock the database into a version of the world when the database was designed.

The big problems with enums explained.

The big problems with enums in my list above are

  • Enums are not easily maintained, and lock the database into a version of the world when the database was designed.
  • When using a text string to compare within code it is possible to misspell it and the language will not warn you.
  • Enums are usually ordered in sorts in the order in which they were created.

Taking each one of the above, firstly lets looks at they way that an enum locks the database into a version of the world when the database was designed. anything.

I know it seems that there are a lot of things, such as gender that will never change, but they could, and that is why we need a database model that can handle

For example I’ve seen gender defined as combinations of the following, Boy, Girl, Male, Female, Born Female, Born Male, Not Specified.

A big problem is that if you misspell a constant of variable you will find out really soon because the code will not compile. If you misspell and enum, or if the enum text is changed then the code needs to be changed. This is because we are mixing up the identity and the description. Two distinct values within the database may have a different identity but for display purposes they may be the same.
bq. Decoding and display of data is something that needs to be performed in the view. The model should only model the data. Enums break this.

Changing sort order with an enum field isn’t possible, you get the order in which they were created. So you have to enter them in the order you want them displayed.

You can’t have enums sorted in different ways by the database – you’ll have to do this in the view. Aren’t enums supposed to make things easier??

My solution to avoid lots tiny tables to store enums

My solution, Parameter Enumeration Tables (PET) and adds a little complexity, but the compromise is worth it.

With a PET uou get the ability to store, maintain, and have referential integrity without having lots of tables

References