Simplifying wide, unnormalized tables in Rails

Question

Background

I'm designing a Rails app to record research data. Most of it can be conceptualized as being "survey" (or "questionnaire") data.

We already have several Access databases and CSV files that hold this data. The existing design is that each survey has its own table with one column per question. Many of these tables have 100+ columns.

I just got a report of how many columns there are for all the surveys in total. The count is 18,683. Yes, 18,683 columns -- a lot more than I was expecting. (I think there are more columns than rows, in total. Some tables may be vestigial, but I know that several are very important.)

To reduce the complexity of the problem, I've been thinking that I would make some models like:

• Survey
• Question
• Response

This strategy would reduce the number of columns from thousands to just a handful. However, I'm not sure how to approach it through Rails because:

• We need nice views (better than autogenerating forms) for several of the surveys.
• We need validation (some surveys are simple, but others are quite complex).
• We need to link the data to other records and report it. My bosses would expect each survey's results to be pivoted (e.g. one column per question) so they can analyze it. Is there an elegant solution for this?
• Storing responses seems more difficult than with the wide tables (unless there's a good strategy for a "polymorphic column").
• ...and other cases where I'd probably want to treat each survey as an ActiveRecord model.

Question

How can I simplify this "question1, question2, ..., question143" monstrosity? Although I'm sure there's not a completely elegant solution, what's the best choice?

I'd be interested in alternative databases if they would make things easier. I'm not very familiar with document databases (like mongodb or couchdb), but from what I know, they might be helpful.

Answer 1

I think that MongoDB would be good solution for this; it'd definitely be worth your time to familiarize yourself with it. The schemalessness would ease things quite a bit. For instance, you could store each survey in a document looking something like this:

survey = {:title => "Thoughts on X",
          :questions => [
            {:text => "What year were you born?",
             :type => "Fill in the blank",
            },
            {:text => "Pick an option:",
             :type => "multiple_choice",
             :choices => ["a", "b", "c", "d"]
            }
           ]
          }

You could then write code capable of interpreting these survey documents and presenting them on the web. You could certainly include special validation information in each document, etc.

A separate collection could contain user responses. You could use MongoDB's map-reduce for aggregations.

Just some initial thoughts. Jump on the MongoDB user list if you decide to explore this direction.