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 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.

Update Nov 2013

Recently released Rails 4 supports great new features of PostgreSQL 9.1+ such as hstore or json column types for your dynamic data sets. Here is an article covering hstore usage in Rails 4. Both types support indexing and advanced querying capabilities (Json with Pg 9.3). Hstore is also available to Rails 3 users with activerecord-postgres-hstore gem.

I am in the process of migrating some of non critical preference tables in my project to hstores. In migrations I just update table definitions and execute one SQL query per table to move the data.