Pros/Cons of storing serialized hash vs. key/value database object in ActiveRecord?

Question

If I have several objects that each have basically a Profile, what I'm using to store random attributes, what are the pros and cons of:

1. Storing a serialized hash in a column for a record, vs.
2. Storing a bunch of key/value objects that belong_to the main object.

Code

Say you have STI records like these:

class Building < ActiveRecord::Base
  has_one :profile, :as => :profilable
end
class OfficeBuilding < Building; end
class Home < Building; end
class Restaurant < Building; end

Each has_one :profile

Option 1. Serialized Hash

class SerializedProfile < ActiveRecord::Base
  serialize :settings
end

create_table :profiles, :force => true do |t|
  t.string   :name
  t.string   :website
  t.string   :email
  t.string   :phone
  t.string   :type
  t.text     :settings
  t.integer  :profilable_id
  t.string   :profilable_type
  t.timestamp
end

Option 2. Key/Value Store

class KeyValueProfile < ActiveRecord::Base
  has_many :settings
end

create_table :profiles, :force => true do |t|
  t.string   :name
  t.string   :website
  t.string   :email
  t.string   :phone
  t.string   :type
  t.integer  :profilable_id
  t.string   :profilable_type
  t.timestamp
end

create_table :settings, :force => true do |t|
  t.string   :key
  t.text     :value
  t.integer  :profile_id
  t.string   :profile_type
  t.timestamp
end

Which would you choose?

Assume that 99% of the time I won't need to search by the custom settings. Just wondering what the tradeoffs are in terms of performance and the likelihood of future problems. And the number of custom settings will likely be anywhere from 10-50.

I would rather go with the second option, with the settings table, because it follows the ActiveRecord object-oriented conventions. But I'm wondering if in this kind of situation that would come at too high a performance cost.

Note: I am wondering in terms of RDBMS only. This would be a perfect fit for MongoDB/Redis/CouchDB/etc. but I want to know purely the pros and cons in terms of SQL.

Answer 1

I would recomend just creating a model call Attribute and have each of your objects that need many of them has_many. Then you don't have to mess around with serialization or anything brittle like that. If you use the :join syntax you don't have any real performance issues with this.

Serializing data into your RDBMS is almost always unwise. It's more than about queries, it's about the ability to describe and migrate your data (and serialization shatters that ability).

class Building < ActiveRecord::Base
  has_many :attributes
end

class Attribute < ActiveRecord::Base
   belongs_to :building
end

create_table :attributes, :force => true do |t|
  t.integer :building_id
  t.string :att_name
  t.string :data
  t.timestamp
end

Answer 2

I had the same problem, but finally made the decision.

Hash serialization option makes maintenance problem. It is hard to query, extend or refactor such data - any subtle change needs migration which means reading each record deserializing and serializing back, and depending on refactoring serialization exception may happen. I tried both binary serialization and JSON - the second is easier to extract and fix but still too much hassle.

Separate settings table is what I'm trying to use now - much easier to maintain. I plan to use Preferences gem for that which mostly does all abstraction for easy use. I'm not sure if it works with Rails 3 yet - it is small so I can extend it if needed.