SQL Table and C# Enumeration

Question

Suppose I have n types of users in my application.

I am using an UserType enumeration to distinguish them.

Do I need to keep a table in my database named UserType?

So that I can find the user type any time by querying the tables rather than searching the application source code.

Doing so my source code may become somewhat complicated.

Should I admit this trade off?

Answer 1

Yes, use both: UserType lookup table and enumeration

For the sake of understanding data structures we do create lookup tables with defined types even though they never change. This way you keep referential integrity by also relating tables to this lookup.

Automate your enums
By using T4 templates you can easily automate your business layer code to reflect DB changes. So whenever you change your SQL script to change values in your lookup table, you just run your templates and create additional values in your enums. And BTW: all T4 templates can be executed with a single click in Visual Studio 2008. Select your solution in Solution Explorer and click an icon in the mini toolbar of Solution Explorer. Voila. T4s all generated.

Flag enums
They are all fine and dandy, but it complicates T-SQL scripts in case you would also use them in the same way as in your business layer. Maybe it is more wise to use many-many relationship instead, but you won't be able to automate enum creation, so making a change on DB layer would also mean making a change on business layer.