Adding option columns without killing Active Record

I just finished implementing the new notification system for our site, which involved adding 15 different new options that our users can customize regarding how they are messaged from crusher. We’ve learned that the more columns you have in a model, the more memory it takes up and eats away resources. Given that I really didn’t want to be adding 15 new columns to the user table. What else can you do though? Then I thought – hey, why not just have a single integer that holds the 15 new options and store the value in base 3. Then have each position in the integer represent one of the options. I went down this route and it ended up working really well (I think). I have one tiny int that is in base 6, and one medium int that is in base 3 and that holds all the options we need!  Here is the code I ended up writing to pull out the base 3 values :

  def base_three_message_digest_value
    self.message_digest_value.to_s(3)
  end

  # get out a specific position in a number for base 3
  def get_message_digest_value_for_position(position)
    if base_three_message_digest_value.length > position then
      value_position = base_three_message_digest_value.length - position - 1
      base_three_message_digest_value[value_position..value_position].to_i
    else
      0
    end
  end

  # set a specific position in a number for base 3
  def set_message_digest_value_for_position(position, value)
    if base_three_message_digest_value.length > position then
      value_position = base_three_message_digest_value.length - position - 1
      new_value = base_three_message_digest_value
      new_value[value_position] = value.to_s
      self.message_digest_value = new_value.to_i(3)
    else
      self.message_digest_value = self.message_digest_value + (value.to_i * (3 ** position))
    end
  end

then to pull out users with a specific value I do something like this :

(SUBSTR(CONV(message_digest_value,10,3),LENGTH(CONV(message_digest_value,10,3)) – 7,1)) > 0

Has anyone else out there done this? Am I missing something, or was this actually a good to way to help our user model stay small and reduce the number of db columns? So far all seems great.

Advertisement