存储序列化的哈希值与键/值数据库对象的ActiveRecord的缺点优点/？ [英] Pros/Cons of storing serialized hash vs. key/value database object in ActiveRecord?

问题描述

如果我有每个人都有基本是简介若干个对象，我使用的存储随机属性是什么，有什么的利弊：

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. 存储序列化的哈希为创纪录的一列，VS。
2. 存储一串钥匙/值对象的 belong_to 的主要对象。

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.

假设你有STI记录这样的：

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

每个 HAS_ONE：简介

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

选项2键/值存储

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?

假设99％的时候，我也不会需要通过自定义的设置进行搜索。只是想知道的权衡是在性能和​​未来的问题的可能性条件。和自定义的数设置将有可能从10-50任何地方。

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.

我宁愿用第二个选项里，它的设置表，因为它遵循ActiveRecord的面向对象的约定。但是我如果在这种情况下，将付出过高的性能成本疑惑。

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.

注：我想知道在RDBMS只表示。这将是一个非常适合的Mon​​goDB / Redis的/ CouchDB的/等。但我想知道，纯粹是SQL方面的优点和缺点。

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.

推荐答案

我有同样的问题，但最后作出上述判决。

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

哈希序列化选项使得维护问题。这是很难查询，扩展或重构这样的数据 - 任何细微的变化需要迁移，这意味着读取每个记录反序列化和序列化回来，这取决于重构序列化异常可能发生。 我想这两个二进制序列化和JSON - 二是更容易提取和厘定，但还是太麻烦

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.

单独设置表是什么，我尝试使用了 - 更容易维护。我打算使用 preferences 宝石为它主要完成所有的抽象，便于使用。我不知道如果它与Rails 3中，但 - 这是小，所以如果需要，我可以扩展它

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.

更新2013年11月

近日发布的Rails 4支持的PostgreSQL强大的新功能9.1+如 hstore 或 JSON 作为您的动态数据集的列类型。这是一篇文章涵盖 hstore使用Rails中4 。这两种类型的支持索引和高级查询功能（JSON有PG 9.3）。 Hstore也可到Rails 3用户的ActiveRecord-的Postgres，hstore 的宝石。

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.

我在我的项目hstores迁移一些非关键性preference表的过程。在迁移我刚刚更新表定义和执行每桌一条SQL查询来移动数据。

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.

这篇关于存储序列化的哈希值与键/值数据库对象的ActiveRecord的缺点优点/？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！