Storing Surveys in CouchDB vs. MySQL

Question

Hi there,

I am looking into the best ways for storing surveys to a database with Rails. I have checked out this great Smerf Survey Plugin for Rails which stores surveys into a Relational Database with the following tables:

smerf_forms: name, code
smerf_forms_users: user_id, smerf_form_id, responses (as text)
smerf_responses: smerf_forms_user_id, question_code, response (as text)

Looking at the log for a basic survey example that comes with Smerf, there's a few database calls:

Processing SmerfFormsController#create (for 127.0.0.1 at 2010-01-24 20:09:58) [POST]
  Parameters: {"responses"=>{"g1q3a4s1"=>"", "g1q1a3s1a3s1"=>"", "g1q1"=>"1", "g2q1"=>{"1"=>"1"}, "g1q2"=>"2", "g1q3"=>{"1"=>"1", "2"=>"2", "3"=>"3"}, "g2q3"=>"12", "g1q4"=>["4"], "g1q5"=>["1", "3"], "g2q1a4s1"=>""}, "commit"=>"Press to Save your answers", "authenticity_token"=>"a4aDgvjzX0UK9HrQFdpdPyfALWGL22rcjRZfxDY3Ww0=", "smerf_form_id"=>"1"}
  SmerfForm Load (1.0ms)   SELECT * FROM "smerf_forms" WHERE ("smerf_forms"."id" = 1) 
  SmerfFormsUser Create (0.5ms)   INSERT INTO "smerf_forms_users" ("responses", "smerf_form_id", "user_id") VALUES('--- !map:HashWithIndifferentAccess 
g1q3a4s1: ""
g1q1: "1"
g1q1a3s1a3s1: ""
g1q2: "2"
g2q1: !map:HashWithIndifferentAccess 
 "1": "1"
g1q3: !map:HashWithIndifferentAccess 
 "1": "1"
 "2": "2"
 "3": "3"
g1q4: 
- "4"
g2q3: "12"
g1q5: 
- "1"
- "3"
g2q1a4s1: ""
', 1, -1)
  SmerfResponse Create (0.2ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('1', 1, 'g1q1')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('2', 1, 'g1q2')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('1', 1, 'g2q1')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('1', 1, 'g1q3')
  SmerfResponse Create (0.3ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('2', 1, 'g1q3')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('3', 1, 'g1q3')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('4', 1, 'g1q4')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('12', 1, 'g2q3')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('1', 1, 'g1q5')
  SmerfResponse Create (0.1ms)   INSERT INTO "smerf_responses" ("response", "smerf_forms_user_id", "question_code") VALUES('3', 1, 'g1q5')
Rendering smerf_forms/edit
Rendered smerf_forms/_smerf_form (11.0ms)
Completed in 51ms (View: 17, DB: 3) | 200 OK [http://localhost/smerf_forms]

My question is, would it be better to store surveys using CouchDB (a Document-Oriented Database), instead of a Relational Database, since surveys are more documents than anything else? Along those lines are these questions:

• Is the above output okay, should it be more optimized? I mean, people won't be filling surveys out constantly so there's no need for them to be lightning fast, so I'm wondering if CouchDB would actually be any faster
• It's already built nicely with this plugin, so should I just use it or would it be a real benefit to have this in CouchDB. I want to be doing a LOT of analysis on this survey data (lots of searching, sorting, joining...).

Thanks for the help.

Still trying to wrap my head around when and where to use CouchDB :)

Answer 1

If the survey plugin works now, I would use it as is.

Adding another database system to your app will introduce a lot of extra work ... development time, plus deployment and system administration.

On top of this, I just don't think CouchDB is as good as some of the alternatives ... I have found MongoDB to be faster and more useful (ad-hoc queries that actually work for a start). MogoDB also has some outstanding support libraries for Rails now.

Answer 2

Surveys are not documents, they are a collection of questions with many sets of answers. They key point is when you run a survey, you don't want to just store the results, but analyze them. If you treat the results as a single document, it will make analysis difficult. Having the data in a relational database will simplify analysis.

Answer 3

I strongly disagree with Sam's comments:

• A survey and a survey response are great examples of documents. In fact smerf loads the form definition from a YAML file on disk and, although I'm not at all familiar with smerf, I'm guessing smerf_forms_users.responses is a serialised set of answers.
• A document-oriented database does not mean data cannot be analysed. CouchDB and MongoDB both have mechanisms for extracting and analysing information buried deep inside the documents.

Also, note that by serialising the form definition to YAML there is no connection left between the questions and the answers anyway so it's arguably not a relational solution. (I would do exactly the same if I was using an RDMBS for this - imagine the schema!)

In a document-oriented database there would most likely by survey documents (one per survey) and survey response documents (one per user per survey). Simple and clean.

However, even though I think a document database would be a good fit, I do agree with Toby's comment that if serf works use it rather than adding additional complexity.

As for which is best - CouchDB vs MongoDB - that's partly personal preference and partly requirements.

Answer 4

Surveys are great fit for document databases. The main thing to ask yourself is: "would storing this as JSON make my life easier?"

Meebo uses CouchDB for survey responses among other things. They store 160+ million documents in a CouchDB-Lounge cluster.

@Toby - it's a matter of tradeoffs. With CouchDB you get proven reliability and scalability, replication that's not matched by any other database, and it's all via HTTP. With MySQL or Mongo you get fast results for a few users, and some form of automatic indexing / query planning.

Answer 5

Zodiac.NET is a good example of storing the surveys. It use XML for survey definition:

http://www.mentor-logic.com/index.php/products/components/zodiacnet