What is the best practice/design decision to use enums or database lookup tables or both for an asp.net MVC app in the cloud

Question

I am writing an Asp.net MVC app but I am not sure of a design decision. I am unsure if the best design would be to use enum values or store these values as lookup tables in the database. I can imagine lookup tables is fine for a windows app where all these queries are not a problem but for a high traffic web application running in the cloud that uses a relational database I am not so sure.

The application needs to be maintainable and will need to support multi language in future. Other developers are going to have to work on the system so I need the design correct and easily maintainable.

I currently use enums for values like NotificationMethod (None, Email,Sms),
Gender (Male, Female) OrderStatus (Open,Closed,InProgres, DeliveryInProgress PaymentService (Cash, BankTransfer,Paypal, GoogleWallet)

enums suit me as there are business rules in the code linked to the enum and having business rules with enums values instead of strings make the application more readable and less prone to mistakes.

However the above is not suitable for the following scenario. My web frontend has a select/dropdown controls. To populate the dropdowns I have the following code

  var paymentServices=
          Enum.GetValues(typeof(PaymentService))
          .Cast<PaymentService >()
          .Select(d => new SelectListItem()
          {
              Text = d.ToString(),
              Value = ((int)d).ToString()
          }).ToList();

instead of the enum value Email I want to display E-Mail and I would want spaces between words.

So I end up using attributes and the use a static method in an EnumHelper Class to read the attributes as described here

My order and Preference db tables I have about 20 of these enums each per table.

For my scenario is it best practice to

1. Use only C# enums with enum attributes for display values like FriendlyNames, Description, Tooltip.

2. Just use database lookup tables. Disadvantage is business rules will have to be based on String values of the selected value that is in the lookup table. To go to my preference or order edit screens I would have to read 20 lookup tables separately in the database in order to render the dropdown controls. This makes writing code and reporting much easier but the system will be under heavy load (the system has thousands of users)

3. Have simple enums values for business rules and use matching lookup database tables and store in those tables the additional display columns to show on the frontend UI. The disadvantage is I have to keep the basic enum numbering in sync with the database lookup tables.

How do you I decide the best design decision or is there a better solution?

Answer 1

Have simple enums values for business rules and use matching lookup database tables and store in those tables the additional display columns to show on the frontend UI. The disadvantage is I have to keep the basic enum numbering in sync with the database lookup tables.

You could investigate T4 templates, which would mean the enumerations in your application are built from the lookup tables in your database semi-automatically. (You choose an option from the build menu to recreate all of them.) We currently do this on a large project at work and it works well.

The benefit is, of course, you don't have to have magic numbers in your application or keep things in sync manually. Foreign keys and strongly-typed code, a win-win.

This seems related: SQL Table and C# Enumeration

To expand upon this, we also use reflection to get the DisplayValue attributes for enums. However, just because we do it doesn't make it best practice. We run on client machines, not in the cloud, so therefore reflection isn't as big of an issue. I don't know how substantial that reflection would be when you have multiple users being served up pages from one machine. That said, Microsoft makes ample use of reflection when using HTML Helpers, and reflection could be faster than hitting the database 20 times. But I digress...

Answer 2

If your enum values are going to need to be localized, why not be proactive about it and use a .resx file right away for English captions? Using attributes means you're statically declaring what the description should be for each enum value, and that's essentially the opposite of being localizable.

Given using resx = namespace.resxFile (I'd suggest a .resx file dedicated to hold captions for enum values - i.e. I'd put the other UI resources in another file) with each enum value being stored as a string named by convention [enumValue]Caption, your paymentServices list could look like this:

var paymentServices =
    Enum.GetValues(typeof(PaymentService))
        .Cast<PaymentService>()
        .Select(d => new SelectListItem()
        {
            Text = resx.ResourceManager
                       .GetString(string.Format("{0}Caption", d.ToString())),
            Value = ((int)d).ToString()
        }).ToList();

When comes the time to localize, all you'll need to do is translate your .resx file and set the resource's culture to the desired one.

The downside is obviously you can't have a feature that, for example, would let users edit the translations and add new supported languages, since the .resx file is built into your app; this also means code needs to change whenever a new enum value is needed.

The upside is that you're not hitting the database, and your code is self-contained, meaning future devs don't need to lookup anything anywhere else than in the code base.

Which is best really boils down to what your needs and requirements are.