In Rails, what is the best way to store multiple boolean attributes in a model?

Question

I have a model House that has many boolean attributes, like has_fireplace, has_basement, has_garage, and so on. House has around 30 such boolean attributes. What is the best way to structure this model for efficient database storage and search?

I would like to eventually search for all Houses that have a fireplace and a garage, for example.

The naive way, I suppose, would be to simply add 30 boolean attributes in the model that each corresponds to a column in the database, but I'm curious if there's a Rails best practice I'm unaware of.

Answer 1

You could always have a TEXT column that you hold JSON in (say, data), and then your queries could use SQL's LIKE.

Eg: house.data #=> '{"has_fireplace":true,"has_basement":false,"has_garage":true}'

Thus, doing a find using LIKE '%"has_fireplace":true%' would return anything with a fireplace.

Using model relationships (eg, a model for Fireplace, Basement, and Garage in addition to just House) would be extremely cumbersome in this case, since you have so many models.

Answer 2

For that many booleans in a single model you might consider using a single integer and bitwise operations to represent, store and retrieve values. For example:

class Model < ActveRecord::Base
  HAS_FIREPLACE = (1 << 0)
  HAS_BASEMENT  = (1 << 1)
  HAS_GARAGE    = (1 << 2)

  ...
end

Then some model attribute called flags would be set like this:

flags |= HAS_FIREPLACE
flags |= (HAS_BASEMENT | HAS_GARAGE)

And tested like this:

flags & HAS_FIREPLACE
flags & (HAS_BASEMENT | HAS_GARAGE)

which you could abstract into methods. Should be pretty efficient in time and space as an implementation

Answer 3

If you're wanting to query on those attributes, then you're unfortunately probably stuck with first-class fields, if performance is a consideration. Bitfields and flag strings are an easy way to solve the problem, but they don't scale well against production data sets.

If you aren't going to worry about performance, then I'd use an implementation where each property is represented by a character ("a" = "garage", "b" = "fireplace", etc), and you just build a string that represents all the flags a record has. The primary advantage this has over a bitfield is that a) it's easier for a human to debug, and b) you don't need to worry about the size of your data types.

If performance is a concern, then you will likely need to promote them to first-class fields.

Answer 4

Your 'naive' assumption is correct - the most efficient way from a query speed and productivity perspective is to add a column for each flag.

You could get fancy as some others have described, but unless you're solving some very specific performance problems, it's not worth the effort. You'd end with a system that's harder to maintain, less flexible and that takes longer to develop.

Answer 5

Normally I'd agree that your naive assumption is correct.

If the number of boolean fields keep growing and growing (has_fusion_reactor?) you may also consider serializing an array of flags

# house.rb
class House
  serialize :flags
  …
end

# Setting flags
@house.flags = [:fireplace, :pool, :doghouse]
# Appending
@house.flags << :sauna
#Querying
@house.flags.has_key? :porch
#Searching
House.where "flags LIKE ?", "pool"

Answer 6

Here's another solution.

You could make a HouseAttributes model and set up a two way has_and_belongs_to_many association

# house.rb
class House
  has_and_belongs_to_many :house_attributes
end

# house_attribute.rb
class HouseAttribute
  has_and_belongs_to_many :houses
end

Then each attribute for a house would be a database entry.

Don't forget to set up your join table on your database.

Answer 7

Hi Sanjay

I'm thinking about something like this

You have a House Table (for details of the house)

You have another master table called Features (which has features, like 'fireplace', 'basement' etc..)

and you have a joining table like Houses_Features and it has house_id and feature_id

By that way you can assign features to a given house. dont know whether this matches to your needs, but just think about it :D

thanks and regards

sameera