# Storing Enum values as Strings in DB

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.

• Another problem i have found is that if a new value is added, such as "FR" on the legacy side of the database, if someone recalls this record and saves it without checking the field it will set the.value back to null (None), not likely to happen in this application but i dont ike assuming this. – Frazer Feb 7 '17 at 9:57
• My gut feeling is that you want a full-fledged separate database table for locations, because you want names which are not restricted by identifier rules, and likely more information as the application evolves (shipping methods, currency, main office, you name it). The unique key could well be the underlying enum value. The C# enum definition should then contain a prominent warning that new enum members should only be appended at the end, in order to keep the numeric values constant. – Peter - Reinstate Monica Feb 7 '17 at 13:38
• I do need to clarify that in this example I am using a very poor legacy database with only one table which is unnormalized, no fault to anyone its just what has been inherited. I feel I have no choice for that reason to store the new fields as a string to retain data meaning until such time as i can produce a new database. But i must add I am thankful for the wider information about use of enums and parameter lookups as this will be of benefit elsewhere. – Frazer Feb 7 '17 at 13:54
• @FrazerMountford there won't ever be such time. Application will grow and you won't have budget and/or time to do more and more disruptive changes. Your own code (not just database) will be messy. Entropy will win. Time to fix things is now, the first time you need to do a change. – Adriano Repetti Feb 7 '17 at 15:57
• It's funny when you see find a stackexchange question that has code you recognise from a previous job :-D – Stuart Jul 28 '17 at 17:07

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";
}

• Thanks, the more i think about it the more i think the use of enums is dumb in my problem. However I do wonder if it would be best to use a List of Strings of Country Codes instead of the above, it seems a little overkill to use an editable parameter table at this time which is probably why i thought to use enum in the first place. – Frazer Feb 7 '17 at 13:16
• The set of existing countries is not open. (It may evolve, slowly, but then anything except pure math will.) An enum appears to me like a perfectly valid data choice to identify a country. – Peter - Reinstate Monica Feb 7 '17 at 13:42
• @PeterA.Schneider downvoted? To me it is open and no one said they are countries but locations and you can have any number of them. There's even an example, SGSingapure is not a country. – t3chb0t Feb 7 '17 at 13:50
• @FrazerMountford are you saying that the locations are countries? then well, it could be an enum, it's not very likely that they would change often. but I they are any location then as far as I'm concerned it'd be an open list. – t3chb0t Feb 7 '17 at 13:54
• It is a very small list of countries that the customer is based in, which in theory could be added or other future customers could have a different list, SG Singapore in my mind should be SGP (As Per ISO) but this is what the customer has defined. – Frazer Feb 7 '17 at 14:01

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:

• 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?)
• 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.

• 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.
• Heh, I just wrote that in my comment :-). – Peter - Reinstate Monica Feb 7 '17 at 13:39
• @FrazerMountford: This is your answer. You commented on t3chb01's answer that you were wondering about a database table related solution (a lookup table or master table if you want some buzzwords) is better. This seems like overkill until you start implementing it. You'll probably need a drop down list of values in the user interface. Turns out all the info (foreign key value and display text) can be stored in the database. You can throw begin/end dates on this table to deprecate old values too, and still maintain backwards compatibility. – Greg Burghardt Feb 7 '17 at 13:39
• Upvoted but I don't think this is any better then the string solution. I admit, usually it would be and I would have suggested it too (as a matter of fact I do it all the time like this) but OP said that other applications rely on those exact strings. It looks like they couldn't dynamically resolve them (like in an additional table). I might be wrong, who knows... only OP ;-] but until then it's a fight between enum vs string. – t3chb0t Feb 7 '17 at 13:47
• @t2chb0t: You can make the primary key of the lookup table the same as the strings the other applications expect. Then you get the flexibility of what a relational database provides as well as the data integrity. I see wins all around with this solution. – Greg Burghardt Feb 7 '17 at 13:53
• @GregBurghardt I know how it is supposed to work ;-) as I've said, I do it all the time but my answer is based on However in my example I need to store the value as a string anyway so that other systems will be able to understand it.. This doesn't look like the other systems could cope with joins. Maybe they cannot be changed? You cannot say something is the ultimate solution if you don't know all the details. I don't claim strings are perfect but under this circumstances they might be. With a new system this solution would be definitely the one to choose. – t3chb0t Feb 7 '17 at 13:57
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!?)

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
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.

• I agree, when everything is driven by code then this may be the right way (I'm thinking a code first approach, for small systems it's appropriate). I'd just change enum declaration to include 0 (otherwise default value is invalid) – Adriano Repetti Feb 7 '17 at 19:31