Rails - Aug 2006 - Key/Value Database Design

What are the Rails community''s thoughts on key/value tables in database
design? Specifically, two separate tables: keys, and values. It seems
as though this technique is not very elegantly supported in Rails. You
can get functionality through the use of has_many :through and some
auxiliary key -> value mapping methods by iterating through both lists
and joining the results into a hash, but it still seems... ugly.

Ideally, I''d love to NOT have to resort to this option, but my work
calls for it as we deal with a lot of data whose origins we do not
know, and creating new fields in the database for each key is not
practical.

Thoughts?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Aug 21, 2006, at 3:44 PM, atraver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:
>
> What are the Rails community''s thoughts on key/value tables in  
> database
> design? Specifically, two separate tables: keys, and values. It seems
> as though this technique is not very elegantly supported in Rails. You
> can get functionality through the use of has_many :through and some
> auxiliary key -> value mapping methods by iterating through both lists
> and joining the results into a hash, but it still seems... ugly.
>
> Ideally, I''d love to NOT have to resort to this option, but my
work
> calls for it as we deal with a lot of data whose origins we do not
> know, and creating new fields in the database for each key is not
> practical.
>
> Thoughts?

	I just pasted this code for someone esle a few days ago but I''ll  
paste it again here. THis allows you to treat a model like a hash of  
key=>value pairs. I''m not sure if this is exactly what you are
asking
for but maybe it is,

     create_table "db_hashes", :force => true do |t|
       t.column "key", :string, :limit => 40, :default =>
"", :null
=> false
       t.column "value", :string, :default => ""
     end


class DbHash < ActiveRecord::Base
   class << self
     def [](key)
       pair = find_by_key(key.to_s)
       pair.value unless pair.nil?
     end

     def []=(key, value)
       pair = find_by_key(key)
       unless pair
         pair = new
         pair.key, pair.value = key.to_s, value
         pair.save
       else
         pair.value = value
         pair.save
       end
       value
     end

     def to_hash
       Hash[ *find_all.map { |pair| [pair.key, pair.value] }.flatten ]
     end
   end
end


	And then you can use it like a hash:

dbhash = DbHash.new

dbhash[:foo] = ''bar''


-Ezra





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

> > What are the Rails community''s thoughts on key/value tables
in
> > database
> > design? Specifically, two separate tables: keys, and values. It seems
> > as though this technique is not very elegantly supported in Rails. You
> > can get functionality through the use of has_many :through and some
> > auxiliary key -> value mapping methods by iterating through both
lists
> > and joining the results into a hash, but it still seems... ugly.
> >
> > Ideally, I''d love to NOT have to resort to this option, but
my work
> > calls for it as we deal with a lot of data whose origins we do not
> > know, and creating new fields in the database for each key is not
> > practical.
you could do something like 3 columns. ResourceID/URI, KeyName/URI, Value. then
you can add as many key types as you want. id use BerkeleyDB for this rather
than SQL, since its essentailly a giant scalable hash table. Redland or ARDF
will let you query the store with SPARQL/RASQAL/etc.

in AR, you could do something like Key.find_by_name("keyname").values
i think.. but it would be sort of hacked together with auxiliary integer keys
unless you rewrote some of the joins..

Google: Results 1 - 1 of about 2 for acts_as_hash. (0.22 seconds) 
> >
> > Thoughts?

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---