When should you map a column to Enum type in code

Question

Hi all:

Maybe this is a dumb question, but when should you map a column into a enum type. For instance, we all know that the column "Gender" which is represents as "M" or "F" in the database can be map to a enum called Gender. Since there are generally 2 gender :), we can be pretty sure that this enum will not need updating. However, what about enum set up for business logic? For instance, let say for region, if you have a Enum that represents all region within your database, whenever you insert another region into your database, that enum has to change, hence another promotion is needed. Is this a good practice? Is there best practice around this?

Thanks!

Answer 1

You're better off making a separate table for region, with it's own, unique ID. Then use the region's ID in your main table.

This allows more flexibility in the future, since adding a region is just adding a new record.

See any text on Database normalization for details and full explanations.

Answer 2

This is actually a complicated question: when you are using an enum, it is traditional to also have a lookup table in the DB as Reed suggests. But this raises the problem of synchronizing the definitions.

What we do is generate the tables from enum definitions as part of our build/deploy scripts to ensure that both reflect the same set of legal values.

We then have convenience functions that take a DataSet returned from a DB query, and map id columns to (newly created) columns with enum values. But we do that right away after the query results are retrieved. So my advice would be to map them as soon as possible, preferably in your DAL.