JSON or XML or serialized array to save in MySQL database

Question

Web app. Get data from services in json & xml formats. And from internal module in hash.

Decided to choose one format for all stored data.

details :

1. More read than write.

2. Data low nested (< 10).

3. Char count between 1000 - 100000.

4. Programming language - ruby.

5. Framework rails.

6. DB mysql.

What's your recommendation?

Answer 1

I'm storing it as JSON in MySQL for a similar project since it offers a lot of flexibility. XML would offer the same, but it's a little verbose and since my application is JavaScript based, JSON is helpful because the parsing step can be skipped.

You might also want to checkout ActiveModel in Rails 3. It allows using a model which offers all the benefits you would get out of ActiveRecord but it doesn't need to be stored in a database. It is very useful for validations on your JSON/XML for example, even though ultimately it will be stored as a blob or large text.

Answer 2

For performance reasons, we store large hashes as serialized Ruby objects in Marshal format. You need a column type of Blob. This works really well. JSON would be fine but we found it a little slower to marshal / un-marshal. I'd stay away from XML unless you really need interoperability with a third party/

Answer 3

I would definitely recommend JSON over XML

For building or consuming API's, ActiveModel works well.

For straight JSON parsing YAJL-ruby is a nice library: http://rdoc.info/projects/brianmario/yajl-ruby

In Activerecord you can easily store hashes, arrays etc in text columns using by serializing the attr:

See "Saving arrays, hashes, and other non-mappable objects in text columns" here: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001799

Example:

class User < ActiveRecord::Base
  serialize :preferences
end

user = User.create(:preferences => { "background" => "black", "display" => large })
User.find(user.id).preferences # => { "background" => "black", "display" => large }