Enums in the DB or NO Enums in the DB...

Question

For me, the classic wisdom is to store enum values (OrderStatus, UserTypes, etc) as Lookup tables in your db. This lets me enforce data integrity in the database, preventing false or null values, etc.

However more and more, this feels like unnecessary duplication to me. Not only do I have to create tables for these values (or have an unwieldy central lookup table), but if I want to add a value, i have to remember to add it to 2 (or more, counting production, testing, live db's) and things can get out of sync easily.

Still I have a hard time letting go of lookup tables.

I know there are probably certain scenarios where one had an advantage over the other, but what are your general thoughts?

Answer 1

I put them in the database, but I really can't defend why I do that. It just "seems right". I guess I justify it by saying there's always a "right" version of what the enums can be by checking the database.

Answer 2

If it has to be maintained I would leave them in a lookup table in the DB. Even if I think they won't need to be maintained I would still go towards a lookup table so that if I am wrong it's not a big deal.

EDIT:

I want to clarify that if the Enum is not part of the DB model then I leave it in code.

Answer 3

I've done both, but I now much prefer defining them as in classes in code.

New files cost nothing, and the benefits that you seek by having it in the database should be handled as business rules.

Also, I have an aversion to holding data in a database that really doesn't change. And it seems an enum fits this description. It doesn't make sense for me to have a States lookup table, but a States enum class makes sense to me.

Answer 4

Schema dependencies should be stored in the database itself to ensure any changes to your architecture can be easily perform transparently to the app..

Answer 5

I prefer enums as it enforces early binding of values in code, so that exceptions aren't caused by missing values

It's also helpful if you can use code generation that can bring in the associations of the integer columns to an enumeration type, so that in business logic you only have to deal with easily memorable enumeration values.

Answer 6

Consider it a form of documentation.

If you've already documented the enum constants properly in the code that uses the dB, do you really need a duplicate set of documentation (to use and maintain)?