synch enumeration with static data from a database

Question

Hi

I have an enumeration of delivery status codes. And when I save delivery data to the database they are stored with a foreign key to a table containing the same data (i.e. the same delivery codes)

What is the best strategy for keeping an enumeration in synch with data in a database?

Do you just remember to add to the enumeration when a new code is added to the database?

Or load the data into a dictionary when the application starts? And use the dictionary instead of an enumeration? Though this means that I don't have a strongly typed representation of the data - which I definitely want.

Or something else?

The data is not very volatile but new codes do get added once every blue moon

Would appreciate any suggestions.

thanks

Answer 1

When I use a enumeration in the code, I usually store the formatted name as a varchar in the database rather than keep a table of the enumeration values in the database. I realize that this is not as normalized as one might like, but I believe it is better than trying to keep the database and my enumeration synched. All that is needed is to format on insert/update and parse on select to reconstitute the value back into the enumeration.

I only do this when I believe that the enumeration is going to be fixed -- although, I too have made infrequent updates. If I believe that it is likely that the data will be regularly updated, I won't use an enumeration and will have a separate table in the database with foreign key references.

Answer 2

I use T4 templates in Visual Studio 2008. This way, I can force code generation during build and it generates Enums for each table that I want.

A good starting point would be Hilton Giesenow's sample, which I believe answers exactly your question.

A more interesting approach, from a different angle, would be to use SQL views to emulate enums in the database. This way you synchronize the database with your C# (or others) code. You can find a great post about it on Oleg Sych's blog, here.

Answer 3

I asked this question on thanksgiving day and it kind of didnt get any attention. And I am sure it is something a lot of devs will have faced.

So this will hopefully bump it onto the front page again