We have a CMS application that supports creating custom web forms, which each have a different set of fields which hold different types of data. Email addresses, multi-select radio buttons, text areas, etc. Some forms only gets submitted once or twice, others are submitted many thousands of times. To store this in a normal relational database you either need many tables, or you need to normalize your data (probably Entity-Attribute-Value (EAV) style). We didn't want hundreds of tables, and designing a good EAV system can be tough (Magento anyone?), so we looked at other options.
We've settled currently on using MongoDb, with one collection to hold the (versioned) definition of each form's fields (data type, order, validation rules, etc), and another collection to hold all of the submissions (this is a laaarge collection). There _is_ a "relation" between the form definition and the submissions, but because you always query for submissions based on the form (by form_id), you don't really need to do "JOINs" (you just query out the form definition once, before the submissions). Also, because the forms are versioned, and each submission is associated with a particular version of a form, there is no need to retroactively update the de-normalized schema of past submissions (although this does limit your ability to query the submissions if a form is updated frequently - or drastically). It's not perfect, but this use case for MongoDb has been working well for us so far.
My answer to this prompt was starting to get long, so I actually wrote it up in more detail on my blog (the first update in months!). Included are some other drawbacks and tradoffs there. Check it out here if you are interested:
I would love to hear how other folks have solved similar issues like this? Or if anyone sees a way to improve on our current solution? Feel free to respond here or on the blog post. Cheers
We've settled currently on using MongoDb, with one collection to hold the (versioned) definition of each form's fields (data type, order, validation rules, etc), and another collection to hold all of the submissions (this is a laaarge collection). There _is_ a "relation" between the form definition and the submissions, but because you always query for submissions based on the form (by form_id), you don't really need to do "JOINs" (you just query out the form definition once, before the submissions). Also, because the forms are versioned, and each submission is associated with a particular version of a form, there is no need to retroactively update the de-normalized schema of past submissions (although this does limit your ability to query the submissions if a form is updated frequently - or drastically). It's not perfect, but this use case for MongoDb has been working well for us so far.
My answer to this prompt was starting to get long, so I actually wrote it up in more detail on my blog (the first update in months!). Included are some other drawbacks and tradoffs there. Check it out here if you are interested:
http://chilipepperdesign.com/2013/11/11/versioned-data-model...
I would love to hear how other folks have solved similar issues like this? Or if anyone sees a way to improve on our current solution? Feel free to respond here or on the blog post. Cheers