5

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.

devuxer
  • 41,681
  • 47
  • 180
  • 292
  • "I could query this table and store it in, say, a Dictionary or a Dictionary."??? 2 times the same thing??? – KdgDev Jul 22 '09 at 20:10
  • @WebDevHobo, I was fixing it while you were commenting :-P – devuxer Jul 22 '09 at 20:12
  • This is an exact duplicate of http://stackoverflow.com/questions/876008/database-design-multiple-lookup-enum-tables-or-one-large-table, which I found by searching http://stackoverflow.com/search?q=sql+enum for about 2 minutes. – John Saunders Jul 22 '09 at 20:14
  • @John Saunders, Please explain to me how that question is the same as mine. That's talking about whether to have one giant code lookup table or multiple little ones. I'm talking about how to sync up "enum-like" data between a front-end and back-end. – devuxer Jul 22 '09 at 20:20
  • Right. Picked the wrong one. I'll be back. – John Saunders Jul 22 '09 at 20:32
  • It's taking too long to find that. I'm pretty sure there was another question like this recently, but if it's taking me this long to find it, then I can't expect the OP to find it before posting. I see that my edit has already been rolled back. I'll ask the moderators to remove my close vote (if they can). – John Saunders Jul 22 '09 at 20:39
  • @John, thank you. I promise I did check before I posted :) – devuxer Jul 22 '09 at 20:45
  • @John, I'm sure I've also seen it before, but maybe we need a 'statute of limitations'? – ProfK Jan 04 '10 at 05:42

6 Answers6

3

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.

Thorarin
  • 47,289
  • 11
  • 75
  • 111
1

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.

Andrew Hare
  • 344,730
  • 71
  • 640
  • 635
1

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.

dmertl
  • 805
  • 8
  • 13
1

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.

Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • @Scott, interesting comment about code generation. Are you saying generate an enum at runtime based on a query of the db table? If yes, do you have an exmple handy? – devuxer Jul 22 '09 at 20:34
  • I was talking more about compile time there. I use code generation a lot to generate data/business layers - and the templates that I use for those have the option to generate a table as a standard class or as an enum. So - in this case - I'd add the row to the table, regenerate & recompile code, and then deploy the changes (which in this case would just be a mid-tier DLL unless you're adding code somewhere else to make use of the new enum value) – Scott Ivey Jul 22 '09 at 20:38
  • @Scott, ahh, so something similar to SqlMetal.exe? – devuxer Jul 22 '09 at 20:43
  • Yeah, something like that. I use CodeSmith along with custom templates that our company develops internally. Works great - i'd highly recommend looking into it if you haven't already. – Scott Ivey Jul 22 '09 at 21:09
  • I did the same thing with an msbuild task for larger tables (but the source isn't mine to give away). It was ugly but it worked – blowdart Jan 04 '10 at 05:33
0

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.

Babak Naffas
  • 12,395
  • 3
  • 34
  • 49
0

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.

ProfK
  • 49,207
  • 121
  • 399
  • 775