How Can I Store Data in a MySQL with Changing Columns

Question

I am creating a new table in a MySQL database in a Rails project. The problem is that some fields will always be on the table, but other fields I do not know yet--they will be user generated. I know that it is impossible to add fields dynamically to a table. Therefore, are there any good established solutions for this? Perhaps a gem?

To be clear, let's say I have a table called UserInformation. It has the columns name, gender, height. But I need to allow the entry of whatever else a user wants. So, for User A I need to store name, gender, height, weight. For User B I need to store name, gender, height, and favorite_tv_show. For User C I need to store name, gender, height, and favorite_tv_show, favorite_color, mothers_name.

Because I do not know all the potential attributes, I cannot add them in advance as column names.

I can't imagine I'm the first one to hit this problem. Thank you very much in advance!

Answer 1

Make another table, that contains key (string, indexed), value (string, probably) and user_id (integer). You may have to adjust something to suit your needs, it's just an idea and it may not fit as-is.

For each of the attributes on each user you wish to store like that create a separate row in that table.

I'd call it ExtraField:

class ExtraField < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :extra_fields

  accepts_nested_attributes_for :extra_fields,
    allow_destroy: true,
    reject_if: :all_blank
end

You'd have to edit these attributes using a nested form. It's a bit advanced concept and there are quite a lot of docs about it. In essence, it allows you to update not only the record itself (like user), but also its associated records (like extra fields) using the same set of parameters. This allows you to edit all the fields on a single page.