CODEWITHSUNDEEP
c#databaseenumsconstants
mrblah
mrblah

Reputation: 103497

Should I store Enum ID/values in the database or a C# enumeration?

Say my database tables have columns like UserType, SalesType, etc.

Should I have database tables with UserTypeID, userTypeName or should I just create a C# enumeration?

Upvotes: 14

Views: 4143

Answers (4)

James Curran
James Curran

Reputation: 103495

If the list is stable enough to use an enum, then I would use an enum in your code plus a table in the database (make it a foreign key for data consistency).

Upvotes: 0

devio
devio

Reputation: 37205

In my projects, I use my application dbscript to generate C# consts from database, so code always matches db values.

Of course, you only need to have C# enums if your code does something specific depending on the value of the Type field.

Upvotes: 0

Jamie Ide
Jamie Ide

Reputation: 49261

It depends. I listed a few pros and cons for each approach below. In general, I strongly prefer enums if the application needs to use a value to make decisions. As Mehdrad mentioned, you can use both approaches but it requires extra effort to keep the lists in sync.

Lookup tables:

  • Referential integrity can be enforced through foreign keys
  • Easy to add or remove existing values
  • Table can be extended to add additional fields (active flag, etc.)
  • Requires additional class if using business objects
  • Easy to use value and description in reports

Enum:

  • Check constraint can enforce data integrity
  • Best choice if code needs to use value for branching (e.g. x == SalesType.Web vs. x == "WEB")
  • Requires software release to change values
  • Cannot display description in SQL queries (without CASE)
  • Enum may not be appropriate for display in UI (there are workarounds)

Upvotes: 8

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421988

What's wrong with both? If value's are user-defined or changing, definitely enum will not be suitable.

If values are strictly non-changing (such as gender), you can have them as enums for ease of reference in the application and also in the DB as separate table to enforce foreign keys and as a reference.

Upvotes: 13

Related Questions