Why I Don't Like ENUMs in Database

ENUM DB field looks like a very readable and clean solution, but it is not flexible enough. Let me explain, in this short video.

- - - - -
Support the channel by checking out our products:

[fahnleindieselschweif]

"Things that never change, like gender" made me laugh 😁

[klerfe]

This topic came one day after I code ENUMS for order statuses. So Povilas you were right on time with this one! Thanks!

[MusakaluKabamba]

Great Tip on the ENUM usage! I'm currently on a project where I realised I need to pull the ENUM values to create dynamic drop-down using those values and that piece of code was almost magical.

This is a much simpler and easy way to scale / evolve sets of values in a system

[SAS-qqce]

You are doing a great service for us jr devs

[alexaverkiyev]

Never use enum in database because:
-poor performance
-not flexible
-enum values don't make sence in i18n, you still have to implement them separately

Use [tiny, small]int unsigned and implement their values as constants in php. There's spatie/laravel-enum package for convenience if you need one.
Usually separate table is not used for simple enums. All their styling and translations could be done locally, without using db.

[jacekandrzejewski]

Now with PHP8.1 you can use php backed enums with int keys for that. Best option now IMO

[kartiksuthar]

PHP 8.1 has support for Enums which could be another alternative. This might be similar to defining constants in code which sometimes developers like to do.. instead of having a separate table.

[tisodotsk]

I don't like that "enum table" either. In both cases you also need that enum defined in code, so why not define it only there and use only integer "ID" in database? Only downside I think of is that you don't know what for example value 3 means when you look into database and must check it in code. No problems with add/rename or translations.

[mro_coding]

Totally agree and I use the foreign key most of the time.

[princehusky]

Usually if there are cases where there are possible addition to the future (e.g status), i create what i called "Master Table" which in some cases only certain user can do data entry of that master data, and on the other transaction tables i just do indexing from necessary Master Table

[MonethiTebohoArthurnacious]

I agree, but I would rather have the foreign key in status table. That way, one can track the history better, when was it reviewed, processed, e.t.c. so you can have nice tracking that way.

[bboydarknesz]

If we talk about flexibility, I prefer use the second way.
In my old company I used that way because status keep added / change and make me sick with the ENUM and sometimes have to redefined them in a constant variable x'D

[gregzorb]

I use enum for some constaints. For example I have a class with financial statuses and operations statuses. I create 2 classes with them. Then I create migration with enum. When I want to add - I make migration with "alter table". If I want to update status name - it's bad idea for finance, it's bad idea by SOLID.
I don't use foreign keys for financial operations because I hate use DB for business logic.
In the future - it's more readable to look for string values in thousands transactions.

[friedrichvillegasmurillo]

I learned making the ER in the database always separating tables normalizing the data you want to consume. I think you should always make a table with a relationship between them and extract the information you need so you can change it whenever you want without headaches. Make it dynamic.

[AmrSubZero]

i used to store integers in database and define them conditionally in code like if is 1 so it's "completed" for example.
and i remember many times i did refactor projects based on this scenario and been able to deal with that case in flexible.

[SinghatehAlagie]

I mostly use constants when dealing with statuses

[MrValles]

"Something like traffic lights color: red, yellow and green. I'm pretty sure they are the same in all countries of the world"
What do you mean yellow? You mean orange? hahahaha

On topic:
Very interesting video, as always. Never looked at it this way. But will definitely use this approach in the future!

[mhyeganeh]

Very good topic... Thx.

I think it worth it to talk about handling Enums in code level too in another video and introduce some packages... e.g: BenSampo or Spatie's.

[spaldosk]

I totally agree with this, also try to avoid enums in the dB. One thing that often happens tho is I need a static reference to say those statuses in my code. Using the integer ID is not so nice, ideally I wanna say "if status is foo do this" but I don't really wanna wanna rely on the name field "foo" either as that could change. What I usually do ATM is define an additional column "tech_name" or something with the comment "never change this". Then reference this in my code for conditional statements etc. Also not ideal ofc because someone could still change it. Any thoughts/advice on this? Anyway love the vids as always, cheers dude!

[mabdullahsari]

03:02 China reverses red & green :)

Also, an important thing you have not mentioned is the fact that changing an ENUM column can result in locking your table for a long time if the database contains millions of records.