17

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?

Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
user366312
  • 16,949
  • 65
  • 235
  • 452
  • Now, after that many months, somebody finally asked that question. +1 from me. –  Sep 07 '09 at 19:36

4 Answers4

14

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.

Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
  • Sounds like you're Subsonic user – Chris S Sep 07 '09 at 20:21
  • not really. Entity Framework with custom T4 templates that also allow scalar stored procedures and DAO + BO... Well I use T4 all over the place. Makes my life much easier. I even use it to generate my .bat file that deploys sql scripts to ma DB in an automated fashion. – Robert Koritnik Sep 07 '09 at 20:23
  • Any idea what a T4 template like that would look like Robert? – Adam Nofsinger Jan 28 '10 at 21:24
  • 1
    This http://dotnet.org.za/hiltong/archive/2008/08/06/generating-enums-from-database-tables.aspx may get you started, but for the sake of re-usability, have one T4 that has the code, and N others that use that one and set variables appropriately to generate enumerations from particular table. Contrary to example on the link, I also generate XML documentation because my lookup tables usually have 3 columns: ID, Name and Description. I use description for documentation, so StyleCop doesn't complain if I don't name the file as `Something.generated.cs` or put it in a `#region Generated code`. – Robert Koritnik Jan 29 '10 at 08:10
  • 1
    @Adam Nofsinger: I've added a link in my answer. It links to my own blog post where I explain in detail and provide code of a generic T4 template that can generate enums (with XML documentation) from DB lookup tables for you. – Robert Koritnik Apr 18 '11 at 07:49
9

Often in practice you have a table in the database and the corresponding enumeration in the source code.

Enumeration makes it easy for you to work with values that otherwise would not have sense to you.

Having a table in the database lets you perform queries and see in results values that make sense + enforce data integrity (foreign keys).

The challenge here is to keep the table values synchronized with those of the enumeration. But it works well in practice.

3

You can set this as an int column in the database, and then use the Flags() attribute for your enumeration. You're limited to 32 UserTypes (2^32) however.

[Flags]
public enum UserType
{
    None = 0,
    Viewer = 1,
    ContentEditor = 2,
    Editor = 4,
    Admin = 8,
    Root = 16,
    Disciple = 32,
    God = 64
}

Update
I skipped the part about not wanting to read through the source. How is the source using the enumeration? If it's using the number then the look-up table won't affect performance and is a good idea for reference. If the application is using a string name, then maybe it would be easier to adopt the ASP.NET roles provider (iirc) technique of simply using an indexed varchar column for UserType, on the user table.

So:

Name    Email           UserType
Bob     blah@blah.com   admin,author

There can still have a reference table if needed.

I stick to the enumeration solution in the code, but that's because on most of the projects I'm involved with the roles list is static. So if the roles do need to be customisable then a separate look-up table would be the best option, but still using the flags system mentioned above (instead of an intermediate many-to-many table).

Also for a large amount of users, the de-normalized Flags system would be preferable and I'd guess faster. Do you care enough about 3rd normal form for the many-to-many way (but one that ensures better referential integrity). The comma separated list of roles would probably be just as fast and less cumbersome to manage for a small set of users.

Tom H
  • 46,766
  • 14
  • 87
  • 128
Chris S
  • 64,770
  • 52
  • 221
  • 239
  • 2
    Only useful for an attribute that can hold combined values. The question is about a 'type' and that suggests 1-at-a-time. And [flags] isn't all that relevant. – H H Sep 07 '09 at 19:53
  • I may have to convert the UserType column to UserType enum everytime after performing a query from C# code. {Name, Email, UserType}->{Bob, blah@blah.com, 1-2} – user366312 Sep 07 '09 at 19:59
  • You can add or remove the flags attribute if they ever become roles, as you get in unix. There's nothing indicating UserType is a single value in the question and 'type' (the type that the user is) infers a role system – Chris S Sep 07 '09 at 20:25
0

I would do that. I would create a separate table which contains all the possible usertypes. By doing so, you'll keep an eye on the integrity of your data at the DB level.

Why would it make your code somewhat complicated ? I do not understand that.

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
  • I may have to convert the UserType column to UserType enum everytime after performing a query from C# code. – user366312 Sep 07 '09 at 19:48
  • 1
    Use an OR mapper which does that for you ... If you do not want to use an OR mapper, then you can abstract this away in your own data access layer. – Frederik Gheysels Sep 07 '09 at 19:57
  • It also depends whether you have any kind of DAO. Using an O/R mapper is one way, but you could as well have anything else. You could have the integer property and also add an enum property that the does conversion on the fly using integer property. – Robert Koritnik Sep 08 '09 at 06:22