Syncing referential integrity tables and enums

Question

I ponder this question from time to time, so I thought I'd ask you guys about it.

Let's say I have a database table that looks like this:

Table: Visibility
Id   Value
--   -----
 0   Visible
 1   Invisible
 2   Collapsed

This is just a table for ensuring referential integrity. It is basically an enum stored in the database for the purposes of ensuring that any Visiblity values that appear in other tables are always valid.

Over in my front end, I have some choices.

1. I could query this table and store it in, say, a Dictionary<string, int> or a Dictionary<int, string>.

2. I could write an enum by hand and just manually edit the values in the rare event that there is a change to the table. E.g.,

   public enum Visiblity { Visible, Invisible, Collapsed }

3. Something else????

Which would you advise and why?

Thanks.

Answer 1

For fairly trivial stuff like this, I usually go with an enum. I can identify with you in the sense that I feel it's not completely right... but in my opinion it's the lesser of two evils.

Some additional justification for this: if for some reason a fourth value were to be added, your code would need updating anyway to be able to be able to handle that. While you're at it, it's very little trouble to also update the enum.

Answer 2

In the event that you need to add a new value to the table will this require a coding change in your application to support that new value? If so, make it an enum.

Answer 3

If you have any business logic based on the enum, your main option is to sync then manually. If these rows are also foreign keys for another table (let's say you have a look up table of statuses) you should make the ID type a regular int with a unique index instead of an Identity so that you can easily maintain the ID/Value pairings the same if you have databases in different environments.

Answer 4

Depending on your database you could make the visibility field an enumerated type. That way the data would have to be one of the options you specified when created the table.

Answer 5

If you have to do branching code in your application based on the values in that table, you'd want to represent that table as an enum. If not, making it just another class is fine.

This is where code generation comes in handy - if you are using something that can generate a table as an enum, you won't have to think about keeping the table and enum in sync - just add your rows to the table, and the next time you generate your business layer, the enum updates itself.

Answer 6

In contrast to Scott Ivey's answer, I prefer (but seldom use) an approach where I only maintain the enums, and on app startup (or maybe on a build event), use Reflection to ensure that table values match my enum values. This doesn't require any code generation, but is susceptible to late detection of referential constraint violations.