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

user_169992 0
Viewed 7791

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.

key-value-store activerecord ruby-on-rails ruby sql
3 Answers

I was facing the same dilemma you described and ended up going with the key/value table implementation because of the potential maintenance advantages that others mentioned. It's just easier to think through how I could select and update information in separate rows of the database in a future migration as opposed to a single serialized Hash.

Another catch I've personally experienced when using a serialized Hash is that you have to be careful that the serialized data you're storing isn't larger than what the DB text field can hold. You can easily end up with missing or corrupted data if you aren't careful. For example, using the SerializedProfile class & table you described, you could cause this behavior:

profile = SerializedProfile.create(:settings=>{})
100.times{ |i| profile.settings[i] = "A value" }
profile.save!
profile.reload
profile.settings.class #=> Hash
profile.settings.size #=> 100

5000.times{ |i| profile.settings[i] = "A value" }
profile.save!
profile.reload
profile.settings.class #=> String
profile.settings.size #=> 65535

All that code to say, be aware of your DB limits or your serialized data will be clipped the next time it's retrieved and ActiveRecord won't be able to re-serialize it.

For those of you that do want to use a Serialized Hash, go for it! I think it has potential to work well in some cases. I stumbled across the activerecord-attribute-fakers plugin which seems like a good fit.

metavida_55390
Related