Storing a status as a string or an int?

by EvanJPalmer

the-godfather-1

Well here’s a thing.

Do you store a status in the database as an int or a string?

By this I mean, if you have a class:

public class BlogPost
{
  public DateTime DateCreated { get; set; }
  public string Content { get; set; }
  public [SOMETHING] PublishStatus { get; set; }
}

What should the [SOMETHING] be, and what should it’s corresponding type be in the database?

Usually I’d lean towards having an enum in code, and an integer in the database. The enum is easy to work with in code, and it’s easy to map from the DB (just cast it).

I think I’m starting to lean towards keeping the enum in the code, but using the human readable string in the database.

My reasoning is that it is much more descriptive. So we can look at the data and have no question about what the field means. With an integer we have to trawl through code to find the corresponding enum to find it’s meaning. So I think this change will speed up debugging and reporting.

I think it’s important to map this string back to an enum in your ORM/data access layer, so we can do trouble-free compares.

A way that this would work well with Dapper is to have a private memer that is a string like so:

  private string PublishStatus { get; set; }

This would be automatically mapped from the DB. We could then use a public method:

To get the enum value:

private PublishStatuses GetPublishStatus()
{
  return (PublishStatuses) Enum.Parse(typeof(PublishStatuses), PublishStatus, true);
}

What do you think?

EDIT:

Today I had to order by the PublishStatus. I went to do the ordering the database and realised that now that they’re strings, I can’t to a regular SQL “ORDER BY”.

Then I slapped myself in the face (metaphorically) that wouldn’t be testable anyway. So I wrote my test in C# and ordered it with LINQ like this:

  publishedItems = publishedItems.OrderBy(x =>; (int)x.GetPublishStatus()).ThenByDescending(x =>; x.DateCreated).ToList();

It worked nicely and is testable, which backs up my thoughts this is a good technique!

Advertisements