How to store results from dynamically generated forms in MongoDb?

Question

I am new to MongoDB, but am looking at it to solve this problem:

My application has a dynamic form builder that lets users create custom surveys, contact forms, etc. I want to log all of the form submissions and let the users who created the forms search through and export their submitted data.

I come from a typical PHP/mySql background, and see some challenges in storing this data in a mySql database. Each form could have any number of fields of all types. I would either need to normalize my DB to an EAV structure to store the data, or dynamically create a new table for each form, or serialize the form data into TEXT columns (ick).

The "schema-less" (or "dynamic schema") nature of MongoDb seems like the perfect solution to my problem, but my n00b-iness leaves me unsure where to start.

1. Should the results of each custom form be stored as separate collections?
2. Should I have a "forms" collection, and embed the results as sub-documents for each form?
3. Is there another, better way?
4. Is MongoDb actually a good solution for this, or am I way off base?

To restate my problem one more time: I need to store data of variable and unknown structures in an easily searchable and sortable way.

Thanks!

Answer 1

I wouldn't store results as embedded documents within the form document, since you may not know a priori how many submissions to expect. MongoDB limits each document to 16MB, but in practice you probably want to stay well below this threshold.

Since your forms are variable, but pre-determined (that is, each form may differ but the forms are defined ahead of time in some sort of admin UI), I'd recommend using two collections:

The first (call it forms) will store data about the makeup of each form: what fields, what types, in what order, etc. You could imagine documents in this collection would look something like this:

{ _id: ObjectId(...),
  name: "...",
  // other fields, for permissions, URL, etc
  fields: [
    { name: "username",
      type: "text",
      validation: { required: true, min: 1, max: null },
    },
    { name: "email",
      type: "text",
      validation: { required: true, min: 5, max: null, email: true },
    }
  ]
}

This lets you construct the forms dynamically (along with some server-side code) as needed for display in your application. It also gives information about what the fields are and what validation is required for them, which you can use during form submission. You'll want an index on URL or whatever field you use to determine which form to display when serving web requests.

The second collection, submissions or something, would store the submitted data for each form. Documents would look like:

{ _id: ObjectId(...),
  form: ObjectId(...), // the ObjectId of the record in "forms"
                       // that this is a submission on
  // other information here about the submitter:
  // IP address, browser, date and time, etc
  values: {
    username: "dcrosta",
    email: "[email protected]",
    //any other fields here
  }
}

If you need to be able to search by field-value pairs (or just values) in the submitted forms, a variation on this uses an array for the values field, like:

{ ...
  values: [
    { name: "username", value: "dcrosta" },
    { name: "email", value: "[email protected]" }
  ]
}

You can then create an index on the values field, and search like:

// find "dcrosta" as username
db.submissions.find({values: {$elemMatch: {name: "username", value: "dcrosta"}}})

Or create an index on "values.value" and search like:

// find "dcrosta" as value to any field
db.submissions.find({"values.value": "dcrosta"})