How to store enumerated flags in a table

Multiple bit flags can be used with permissions or application settings. But how do you save them? Here's a simple technique on how to store those multiple bit flags as an integer in a table.

In web applications or fat clients (executables), settings and security permissions are always an issue. You have a number of bit fields that allow you to set something on or off.

But how do you store them?

Do you keep adding more and more settings per user to where you have a small table with deep columns?

That was one dilemma I had when I wrote a web application 4 years ago. How do you store all of the bit settings for each user without hitting the ceiling of the maximum number of columns per table. ;-)

Before we refactor!

When I inspected the table, it looked like this:

There were 24 bit fields in the table. Now, I know the maximum number of columns in a table for SQL Server is more than 24, but I will discuss my reasoning below.

Time to Refactor!

First, let's setup our enum type with the Flags attribute.

[Flags]
public enum PermissionEnum
{
    CanView = 1,             
    CanSend = 2,
    CanTrain = 4,
    IsManager = 8,
    IsHourly = 16
}

Next, we create our class to manage the settings (we'll call it PermissionModel). The PermissionNumber will represent an integer of all of the PermissionEnum settings.

So let's say that based on our table above, user 54 has the following permissions: CanView, CanSend, IsHourly. That would give us a PermissionNumber of 19 (1 + 2 + 16) to store in the table.

PermissionModel.cs

public class PermissionModel
{
    private readonly int _permissionNumber;
    public PermissionModel(int permissionNumber)
    {
        _permissionNumber = permissionNumber;
    }
}

Now, for every setting in our enumerated type, we need to have the same number of getters/setters in this class. Here is one method from the class.

public bool CanSend
{
    get
    {
        var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
        return value.HasFlag(PermissionEnum.CanSend);
    }
    set
    {
        var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
        if (value)
            PermissionNumber = (int)setValue.Add(PermissionEnum.CanSend);
        else
            PermissionNumber = (int)setValue.Remove(PermissionEnum.CanSend);
    }
}

When we do a GET in the CanSend property, we take the PermissionNumber that was passed in the constructor and convert that into a PermissionEnum. Then we use the HasFlag to determine if the CanSend bit is set, returning a true or false.

Now for the SET-ter of the bit. When we set the value, we get the current settings from the PermissionNumber and check the value passed in to determine if it's a true or false. When true, we use the Add Extension Method I mentioned in my 10 Extremely Useful .NET Extension Methods post to set the CanSend bit flag. If false, we "remove" the bit flag. Either way, the PermissionNumber is set to the new integer return from the Add or Remove method.

So what do we have?

Here is the finished PermissionModel class for the bit settings.

public class PermissionModel
{
    public int PermissionNumber { get; set; }
    public PermissionModel(int permissionNumber)
    {
        PermissionNumber = permissionNumber;
    }
    public bool CanSend
    {
        get
        {
            var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            return value.HasFlag(PermissionEnum.CanSend);
        }
        set
        {
            var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            if (value)
                PermissionNumber = (int)setValue.Add(PermissionEnum.CanSend);
            else
                PermissionNumber = (int)setValue.Remove(PermissionEnum.CanSend);
        }
    }
    public bool CanView
    {
        get
        {
            var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            return value.HasFlag(PermissionEnum.CanView);
        }
        set
        {
            var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            if (value)
                PermissionNumber = (int)setValue.Add(PermissionEnum.CanView);
            else
                PermissionNumber = (int)setValue.Remove(PermissionEnum.CanView);
        }
    }
    public bool CanTrain
    {
        get
        {
            var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            return value.HasFlag(PermissionEnum.CanTrain);
        }
        set
        {
            var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            if (value)
                PermissionNumber = (int)setValue.Add(PermissionEnum.CanTrain);
            else
                PermissionNumber = (int)setValue.Remove(PermissionEnum.CanTrain);
        }
    }
    public bool IsManager
    {
        get
        {
            var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            return value.HasFlag(PermissionEnum.IsManager);
        }
        set
        {
            var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            if (value)
                PermissionNumber = (int)setValue.Add(PermissionEnum.IsManager);
            else
                PermissionNumber = (int)setValue.Remove(PermissionEnum.IsManager);
        }
    }
    public bool IsHourly
    {
        get
        {
            var value = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            return value.HasFlag(PermissionEnum.IsHourly);
        }
        set
        {
            var setValue = (PermissionEnum)Enum.ToObject(typeof(PermissionEnum), PermissionNumber);
            if (value)
                PermissionNumber = (int)setValue.Add(PermissionEnum.IsHourly);
            else
                PermissionNumber = (int)setValue.Remove(PermissionEnum.IsHourly);
        }
    }
}

With an ORM, this makes things even easier. When your ORM creates the entity with the UserId integer and permissions integer, you can create a partial of the entity (ref: Use an ORM to Create Your Data Layer) and add these bit methods right to the entity so it's contained in your business layer.

You would change your "PermissionNumber" in your class to the property name in the entity...all in a nice neat package.

Now, the Why?

Now, why did I do it this way?

1. Every time someone adds a new bit setting, they need to change the table, change the code, change the UI, etc. I would rather take the approach to eliminate a point of failure in my application where I don't need to touch a layer and it still works as opposed to making another <n> fields in the table for each setting.
2. Your business rules should not reside in your database. Your database is for persistent storage (and maybe stored procedure calculations). The great part about the entities is that you can add more settings to the entity and to the integer and not worry about the size of the table expanding. All you are changing is a single integer field.

Conclusion

In this post, we refactored the bit settings in a table and moved the settings into the business layer to make it more maintainable and easier to work with in our application.

Do you believe there was a better way to do this? Please voice your opinion below in the comments.