I usually use C# Enums translated by EF Core now, which works perfectly fine on the C# side. The only missing part would be to give the DB the information about the enum names, so it could show those to me instead of just the raw numbers. But I assume there is no way to do that. Adding entire tables just doesn't seem worth it for this use case alone.
If you peek at a particular table often enough and it has a crazy enough enum to maybe justify making your life a little easier, what you can do is add a table just for debug purposes and use a view that maps the int to it, but otherwise only use the direct int:enum cast in your app/orm/etc. thereby not paying any runtime cost for foreign keys or integrity checking.
I am going down this path. There are a lot of downstream use cases (e.g. support, reporting, etc) that can benefit from having the canonical names & human-friendly labels in the database.
Keeping database in sync with code like this requires some discipline and manual scripting, but I think it's worth it.
Does this approach ensure data consistency? Or could you accidentally insert a number value into your table which is not represented by any of the enum values?
I “manually” add a check constraint (via EF, so it’s not so much manual as it is “remember to copy and paste this in your db.cs) to assert the value is greater than or equal to zero and less than the number of inhabitants in the enum, but this fails if you manually code the enum values (eg for flags, legacy interop, etc).