Storing Enum values as Strings in DB

Question

I have a Data First Migration and have been struggling a little with storing an enum value as a string.

(Im aware that enums should be stored as ints but, personally I have never liked this, yes enums are hard and fast values but I have always percived them to vary a little bit as the code evolves, i am therefore worried about the integrity of using an int value as new values are added.)

However in my example I need to store the value as a string anyway so that other systems will be able to understand it.

So the database has the following generated property

public string billing_location { get; set; }

The View Model

public string billing_location { get; set; }

public MobiusBillingLocation BillingLocation
{
    get
    {
        MobiusBillingLocation mbl;
        if (Enum.TryParse(billing_location, true, out mbl))
        {
            return (MobiusBillingLocation)      Enum.Parse(typeof(MobiusBillingLocation), billing_location, true);
        }

        return MobiusBillingLocation.None;
    }
    //This also needs setting in the controller code
    set { billing_location = value == MobiusBillingLocation.None ? null : value.ToString(); }
}

public enum MobiusBillingLocation { None, UK, US, SGSingapore }

The code has been designed so that it will be happy with null values and translates None as null and back again.

My questions are, does this approach seem reasonable? Any ideas / suggestions for improvments? Would you use this approach for enums on a code first model for example?

UPDATE

I will add that, in this database, due to it being legacy and unnormalized (thats another topic) and has systems on it that i can't touch I cant be adding extra lookup tables at this time, the data in it however needs to have meaning to a user, ie integer values won't work as they don't pass meaning.

I am however also interested in some side discussion about use of enums.

Answer 1

There are clear guidelines for Enum Design where one of them says:

X DO NOT use an enum for open sets (such as the operating system version, names of your friends, etc.).

and this

public enum MobiusBillingLocation { None, UK, US, SGSingapore }

looks like an open set. const values might be a better solution in this case. You won't have to convert anything and you can use any names for the values.

public static class MobiusBillingLocation
{ 
    public const string None = null;
    public const string GreatBritan = "UK";
    public const string UnitedStates = "US";
    public const string SomethingSingapure = "SGSingapore";
}

Answer 2

DO NOT USE a raw string and DO NOT USE an enum. Move list to a separate table and add a relation between them.

I usually don't like assertions because there are always exceptions. enum (not string) may be appropriate in some circumstances (for example a small or simple application where the approach is code first.) In this case (if your ORM doesn't do it for you) take a look to Jim's answer, I think it might be the way to go.

---

Integers (enums) have some benefits:

• They're small and index friendly.
• They're case insensitive.
• City/country can change name.

And some drawbacks:

• They're harder to remember and error-prone.
• You need a separate list somewhere to keep track of them.
• They do not easily support partial matches.

Strings have these benefits:

• They're easy to recognize and to use in hand-written queries.
• You can search for partial matches.
• They're self-contained (no need for a list elsewhere outside DB.)

And some serious drawbacks:

• Your data isn't normalized.
• They waste much more space.
• To rename a city you have to touch all records, for big databases this is a huge issue.
• They can't handle duplicates.

The other solution is to combine them creating a separate table Location (or City or Country) and simply relate it to your record. This has these benefits:

• It's easy to change names.
• It handles aliases. Don't underestimate this aspect:
  • I'm living in 's-Hertogenbosch which is also called Den Bosch (oh yes, name has both apostrophe and minus sign.)
  • My wife is Taiwanese, in Italy (where we lived before) we were used to see her country registered as R.O.C., Republic of China, Taiwan, Formosa and few times even China (Taiwan). Sometimes software they used understood Taiwan even if official name they shown was R.O.C.
• It's easy to perform a partial match search or to write an UDF to match similar words (have you ever searched Ikea furniture? Did you type those exact names?)
• You can add more information simply adding more columns to that table.
• It doesn't waste disk space.
• It's index friendly.
• It's self-contained. This is more important than what you may think, it's not just for UI but also for many BI reports you can produce without duplicating information (in code and somewhere else.)

And one drawback:

• To write queries is slightly more complicate because of the JOIN.

To summarize: which are the benefits of strings over a separate table? Nothing I can think about. Integers? They're smaller (but few Kb aren't an issue nowadays, are they?) Benefits? Database integrity, open for changes, customizable (you do not need to recompile your code or to write complex queries to add a new country), more features (partial matches, aliases) and UI/BI.

---

Few notes collected from comments:

• If you're working with a legacy database the time to do changes is now, not someday in future. If you make your own code bad designed then the cost of the change in future will be even bigger (and it won't happen.)
• Older applications can query a view instead of a table.
• Countries change name, they split and they merge. Business grows and shrinks.
• Don't ever think anything is immutable in real world. Did you have a bit in your table to denote male/female? Bad choice. You moved to an hard-coded enum? Wrong choice because different customers will need different lists...
• Be open for changes. In future you might want to add a column for international phone code, a column for a formatting string used for addresses...
• Your concern about safety and integrity of integers (which BTW isn't mitigated with strings) come from the basic error: moving database data (the list of available countries) outside the database. Keep it inside it database and database integrity rules will work for you.

Answer 3

if (Enum.TryParse(billing_location, true, out mbl))
{
    return (MobiusBillingLocation)Enum.Parse(typeof(MobiusBillingLocation), billing_location, true);
}

There is no need to parse it twice. You can just use:

if (Enum.TryParse(billing_location, true, out mbl))
{
    return mbl;
}

---

For the case, that the value couldn't be parsed, I would throw an exception (or at least add logging). If that case occurs, you have a serious problem.

Actually, I would prefer to store the underlying integer value because:

• if anybody renames the values of the enum, the connection to the database is lost. Sure, that problem exists also for numeric values, but IMHO that is less possible.

• Conversion between enum and int is for free (Probably entity framework is able to do it for you!?)

Answer 4

Bear in mind the other answers about open sets and related tables -- they are almost always the better option, and probably are the better option in this exact case.

However, if the enum is the appropriate device in C# -- and you are certain that the C# code should drive any change, rather than the database -- my preferred approach is to store the underlying value type in the database (bigint, int, smallint, tinyint) and then constrain the column with the valid enum values.

If you want to take it even further, in many database engines, you can use a database UDF (preferably an inline table function) to tie the value to the text string.

e.g...

public enum Foo : byte { Bar = 1, Bax = 2, Baz = 3 }

public class Fooable
{ 
    public string Name { get; set } 
    public Foo Foo { get; set; }
}

and...

/* SQL Server 2008 upwards */
CREATE TABLE fooable
(
    name VARCHAR(100),
    foo TINYINT
)

ALTER TABLE fooable 
  ADD CONSTRAINT ck_fooable_foo_inrange
  CHECK (foo_col IN (1, 2, 3))

CREATE FUNCTION tf_foo()
RETURNS TABLE AS RETURN (
    SELECT * FROM (VALUES
        (1, "Bar"),
        (2, "Bax"),
        (3, "Baz")
    ) x(enum_value, enum_text)
)

Depending on how much time you want to commit to this, it is possible to autogenerate the ALTER TABLE and CREATE FUNCTION statements based on the defined enum values, by using a post-build task to reflect over the enum members -- T4 text templating is one possible route.

But, really, go back and look at the related table/foreign key approach. In the long-run you will get more mileage out of that than out of this. The key to the above approach is the certainty that a C# enum is the canonical source of a valid range of values. I highly doubt you have that, if you are wrestling with using strings to store your data.