XING Devblog

A Rails plugin to store a collection of boolean values as a single bit field

| Posted by

As you probably know, boolean values in software systems represent the states “true” or “false”. For some use cases in a software application, you may need a lot of them. To give you a very simplified example, imagine a form where the user can check which languages he or she speaks:

XING_FlagShihTzu_boolean_selection

 

 

 

 

Let’s assume that every language option should be stored as a boolean value in a “users” database table, being either “true” or “false”, depending on which of the boxes are checked.

XING_FlagShihTzu_database_table

Technically, modern relational database systems such as MySQL can handle many columns per table just fine. However, adding new columns to a table while the database is on load is not that easy if the table already has millions of rows. This is a change that could take hours to complete, depending on the database system and data size. Additionally, there are limits of how many columns can be stored in a single table.

For quite some time, we have been using a nice technique to store boolean values of some large tables more efficiently as a single bit field. Using this approach, we can add new boolean attributes whenever we need to, without needing to alter the database tables, which would cause a downtime. For the part of our system that is based on the Ruby on Rails framework, we have solved this via our own plugin, that we are happy to share:

FlagShihTzu – A rails plugin to store a collection of boolean attributes in a single ActiveRecord column as a bit field.

We have uploaded the plugin with documentation on how it works. It was already covered in episode #5 of the Ruby5 podcast!

As some of our other XING open source projects, the code is hosted on GitHub, a great website to share and collaborate on open source software. So if you have ideas to improve the plugin, please contribute!

m4s0n501

About the author

Sebastian RoebkeSebastian Röbke works as a Manager Engineering at XING. He loves writing clean, well-tested ruby code. XING Profile »


5 thoughts on “A Rails plugin to store a collection of boolean values as a single bit field

  1. Uhm, the languages users is a many-to-many relationship, which creates “a problem” which is solved by creating a joined table languages_users, not by adding more attributes to the users table. And certainly not by using bit fields, which violate the first normal form creating lots of problems later on.

  2. Hi Pawel,

    in general you are right. As I said in the introduction to this post, it should be seen as a “a very simplified example”, and I am open for better examples. However, there are cases where you really want to denormalize your tables, e.g. for performance reasons. And this is where a single bit field column is a good option for boolean values.

    Thanks,
    Sebastian

  3. This is an awesome plugin but I was wondering whether you provide support for pre-existing flags? Do I have to write my own db script that takes the values from old flags and sets it in the flags field? If that is the case won’t it affect the position of new flags? What is your solution when it comes to old flags on a model. Thanks.

  4. Hi Swamy,

    the plugin makes no attempt to support a migration from pre-existing columns into the single flags column. My feeling is that it should stay that way, to keep the plugin simple and because a custom migration offers the most flexibility.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>