Storing Data Using Numerical Representation

Numerical representation is a great way to store a set of boolean values in a single database column. Lets say that you have a user that has multiple roles. We want to store all of a user’s roles inside a single database column. This single-column approach may be more desirable than a separate column to track each role for the user. We can condense this data into a single column using the numerical representation technique.

If you have ever had to work with Unix permissions, you have already been exposed to numerical representation. A file in Unix can have read, write, and/or execute permissions for a user, group, or other. These permissions are represented in notation octal notation. The permissions are as follows:

• execute: 1 (Base 2: 001)
• read: 2 (Base 2: 010)
• write: 4 (Base 2: 100)

If a user has a single permission on a record, then the numerical representation for that data is stored, as you would expect. “Read” would be stored as “2”. To store a combination of permissions, you sum the separate numerical representations. “Read” and “Write” would be stored as “6”. This works because each value is a power of 2, and so has an unambiguous representation. Take the position in the array, and power 2 to this integer. You will end up with the constants 0, 1, 2, 4, 8, 16, etc. Using powers of 2, bitwise operators can help us easily calculate whether a given integer is included in the sum.

So how can we represent this in Ruby? Lets take a look at our scenario from earlier. Lets represent our user model as:

# app/models/user.rb

# == Schema Info
#
# Table name: users
#
#  role                  :integer(11)
class User < ActiveRecord::Base
  # roles
  EXECUTE, READ, WRITE = 1, 2, 4
end

To keep track of the roles, I have defined them as constants, with numerical values that are powers of 2. We can create a custom setter for the role that takes a collection of these constants. These constants have a numerical value that we can sum together to get the integer to store:

# app/models/user.rb

# == Schema Info
#
# Table name: users
#
#  role                  :integer(11)
class User < ActiveRecord::Base
  # roles
  EXECUTE, READ, WRITE = 1, 2, 4

  # Example usage: role=([User::EXECUTE, User::READ])
  # Sets role column in database to sum of array
  def role=(roles)
    super(Array(roles).inject(&:+))
  end
end

In the role= method, we cast our values to an array, then we use inject to sum them all together. The super method provided by ActiveRecord is then called to set the role column in the database to this integer.

Now that our setter is complete, we can use a bitwise operation to determine if our sum contains our constant. Using a bitwise OR operator, if the sum bitwise or’ed against a constant returns the sum, then the constant is included. To express this in code would look like this:

# app/models/user.rb

# == Schema Info
#
# Table name: users
#
#  role                  :integer(11)
class User < ActiveRecord::Base
  # roles
  EXECUTE, READ, WRITE = 1, 2, 4

  # Example usage: has_role?(User::EXECUTE)
  # Returns boolean
  def has_role?(permission)
    (role | self.class.const_get(permission)) == role
  end

  # Example usage: role=([User::EXECUTE, User::READ])
  # Sets role column in database to sum of array
  def role=(roles)
    super(Array(roles).inject(&:+))
  end
end

Here is an example usage:

user = User.first #=> #
user.has_role?(User::READ) #=> false
user.role=([User::READ]) #=> 2
user.has_role?(User::READ) #=> true

There you have it: A simple and easy way to store multiple boolean values in a single column. This solution is very scalable too. As new roles are introduced to the system, just add them as a constant with the next power of 2 value. No database changes are needed to add additional columns. This calculation is even available in SQL when using PostgreSQL, and other database solutions that allow bitwise operators:

select (5 | 1) = 5; -- true

select (5 | 2) = 5; -- false