Paper form to database

Question

I am doing a Rails 3 app that replaces a paper form for a company. The paper form spans two pages and contains a LOT of fields, checkboxes, drop downs, etc.

I am wondering how to model that in the DB - one approach is to just create a field in the DB for every field on the form (normalized of course). That will make it somewhat difficult to ad or remove fileds since a migration will be needed. An other approach is to do some kind of key/value store (no - MongoDB/CouchDB is not an option - MySQL is required). Doing key/value will be very flexible but will be a pain to query. And it will directly work against ActiveRecord?

Anyone have a great solution for this?

Regards,

Jacob

Answer 1

I would recommend that you model the most common attributes as separate database fields. Once you have setup as many fields as possible then fall back to using a key-value setup for your pseudo-random attributes. I'd recommend a simple approach of storing a Hash through the ActiveRecord method serialize. For example:

class TPS < ActiveRecord::Base
  serialize :custom, Hash
end

@tps = TPS.create(:name => "Kevin", :ssn => "123-456-789", :custom => { :abc => 'ABC', :def => )'DEF' })
@tps.name # Kevin
@tps.ssn  # 123-456-789
@tps.custom[:abc] # ABC
@tps.custom[:def] # DEF