Key/Value Database Design

rubyonrails-talk
1

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?

2

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

3

> 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)