CODEWITHSUNDEEP
mysqlruby-on-railsdatabasearraysjson
Nick Sanders
Nick Sanders

Reputation: 703

storing JSON data in mysql

I have an organization table for example

ID, name, address, phone, email, etc...

Now I want to add multiple addresses, phones and emails.

Is it a good way to store json data in email column like this

[ "[email protected]", "[email protected]", "[email protected]" ]

Or create another table just for emails and another for phones etc...

If storing json data is better - what is the best way to use it in rails?

Upvotes: 9

Views: 13816

Answers (3)

Nick Sanders
Nick Sanders

Reputation: 703

Here is what I found

http://api.rubyonrails.org/classes/ActiveRecord/Base.html

Saving arrays, hashes, and other non-mappable objects in text columns

Upvotes: 8

Ankun
Ankun

Reputation: 444

suggest you store those information in a single table. according to your requirement.it seems use a good polymorphic model will be better.

The code may like this.

module MultiAttr

  def self.included(base)
    base.send :extend, ClassMethods
  end

  module ClassMethods
    def multi_attr(*args)
      args.each do |attr_name|
        class_eval <<-EOF
          has_many attr_#{attr_name}, :class_name => "MultiAttributes", :as => :owner,
             :conditions => {:key => '#{attr_name.singularize}'}

          def add_#{attr_name.singularize}(val)
            self.attr_#{attr_name}.create(:key => #{attr_name.singularize}, :value => val)
            #{attr_name}
          end

          def #{attr_name}
            self.attr_#{attr_name}.map(&:to_attr_model)
          end

        EOF
      end
    end

  end


end

class AttrModel < String

  def initialize(record)
    @record = record
    super(record.value)
  end

  def remove
    @record.destroy
  end

end


#owner_type, owner_id, key, value
class MultiAttribute < ActiveRecord::Base
  belongs_to :owner, :polymorphic => true

  def to_attr_model
    @attr_model ||= AttrModel.new(self)
  end
end

how to use

class User < ActiveRecord::Base
  include MultiAttr
  multi_attr :emails, :addresses
end

user.emails #=> ["[email protected]"]
user.add_email "[email protected]" #=> 
user.emails.first.remove

these codes not tested. but its my basic idea.

Upvotes: 0

rgvcorley
rgvcorley

Reputation: 2923

Storing data as a JSON string in a single database field means that you will not be able to manipulate/query the data using SQL - which kind of defeats the point of storing the data in a database, you may as well store it in a text file.

I'd recommend a one-to-many relationship between your organization table and tables for email addresses and phone numbers. See video explaining different relationship types

Upvotes: 1

Related Questions