Two questions about data structures

I am building an application and would like the groups advice on the
following two scenarios.

  1. I have a table about an entity and there are a LOT of options about
    this entitiy. For example isLeftHanded? isRedHaired? doesLikeRedRoses?
    doesHatePolenta? etc. What is the best way to keep these kinds of
    binary information about an entity? Is there any harm in just adding
    binary fields? Should I use a large number field and OR all the time
    (does limit the number of options per field and makes for messy SQL
    though). Should I have a text field with delimited data in it? Should
    I join a separate table?

  2. related to above: I need to keep other types of prefs that don’t
    apply to all entitities of a type. For example:
    preferedInvoiceFormat, clientURL etc. The tricky thing is that it
    would be nice to have some defaults so that if a preference was not
    specified for a client then a global one could be used. It gets even
    more tricky when you have something like this. product, location,
    package, client, price. The idea is that a product will cost
    different amounts depending on who is ordering it, where and as a part
    of which package. Again there should be a way to specify defaults
    like so widget, *, *, ,10.00 or ,alabama,,,20.00. I the first
    example unless otherwise specified all widgets cost 10 dollars. In the
    second example unless otherwise specified all products in alabama cost
    20 dollars.

Has anybody dealt with data like this and if so how.

Tim U. wrote:

I have a table about an entity and there are a LOT of options about
this entitiy. For example isLeftHanded? isRedHaired? doesLikeRedRoses?
doesHatePolenta? etc. What is the best way to keep these kinds of
binary information about an entity? Is there any harm in just adding
binary fields? Should I use a large number field and OR all the time
(does limit the number of options per field and makes for messy SQL
though). Should I have a text field with delimited data in it? Should
I join a separate table?

Do you want a class to represent such an entity?

class Entity
def initialize
@opts = {:left_handed => false, :red_haired => true}
end

 def method_missing(name, *args)
   key = name.to_s[0..-2].to_sym
   case name.to_s
   when /\?$/
     @opts[key]
   when /=$/
     @opts[key] = args.first
   when /!$/
     @opts[key] = !@opts[key]
   else
     super
   end
 end

end

entity = Entity.new
entity.red_haired? => true
entity.red_haired!
entity.red_haired? => false
entity.left_handed? => false
entity.left_handed = true
entity.left_handed? => true

Furthermore, you could make the class smarter by allowing the options to
be symbols that reference other options, i.e.

`right_handed’ will be the opposite of

`left_handed’.

entity.right_handed = :left_handed!

entity.female = :male!

`hippie’ will have the same value

as `long_haired’

entity.hippie = :long_haired

And perhaps even allow procs:

entity.hippie = proc{|opts| opts[:long_haired] and
opts[:likes_hendrix]}

You could use #respond_to? to determine the type of the value.

Cheers,
Daniel

I’m such a nice guy…

class Entity
def initialize
@opts = {:left_handed => false, :red_haired => true}
end

 def method_missing(name, *args)
   key = name.to_s[0..-2].to_sym
   case name.to_s
   when /\?$/
     value = @opts[key]
     if value.respond_to? :to_sym
       if value.to_s =~ /!$/
         !@opts[value.to_s[0..-2].to_sym]
       else
         @opts[value.to_sym]
       end
     elsif value.respond_to? :to_proc
       instance_eval(&value)
     else
       value
     end
   when /=$/
     @opts[key] = args.first
   when /!$/
     @opts[key] = !@opts[key]
   else
     super
   end
 end

end

entity = Entity.new
entity.likes_hendrix = true
entity.long_haired = true
entity.hippie = proc{long_haired? and likes_hendrix?}
entity.hippie? => true

Cheers,
Daniel

I understand what you are doing I think. It’s a neat solution. But how
do I store that stuff in a database?

I was thinking more about how to save all those options in the
database and still be able to manipulate them via activerecord. One
thing I could do of course is to stream the options to yaml and store
that but that strikes as me as beeing a bit too rubyish. What I mean
is that if the data is to be used by something else (say a report
engine) then it won’t do any good to store it in a yaml blob.

Another thing I though of was to use some database specific XML
thingie. I know postgres can store XML and query it with xpath but
again that strikes me as beeing a bit too specific.

Tim U. wrote:

I understand what you are doing I think. It’s a neat solution. But how
do I store that stuff in a database?

@opts contains the keys and the boolean values (if you go for the
extended version, you may have to go through the values and turn the
symbols and procs into booleans.) I’m not sure what kind of database
structure you have, but those could be used as name-value pairs of some
sort. That’s how I’d do it.

Cheers,
Daniel