Key/Value Database Design

Rails
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

On Aug 21, 2006, at 3:44 PM, [email protected] wrote:

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

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)