Ideal database to store/query data submitted through dynamic user created forms I am designing a web application that allows administrators to create public facing forms. The fields that can be included in the forms are anything from text fields to radio buttons.

I need a solution that allows the users to filter through form submissions.

I have 2 solutions in mind:


A document database such as MongoDB to store submitted form data
Store submitted data in a MySQL JSON data type


Also, what if the form is edited and fields are changed?
Storing the submissions is easy. Any persistence software with sufficient bandwidth will do. Heck, even the OS's native file system will do. From this perspective JSON in MySQL is excessive as you have the DBMS overhead.

Similarly with an update. The system need only overwrite the current submission with the new one.

The real constraint on your design will be on retrieval. You use the word "filter." I presume you want to see all submissions with a given value in a particular form field. To do that efficiently will require indexes. Capability in this area varies widely across the various NoSQL and relational products. You must learn what each can provide, gain a good understanding of your requirements, and choose.

If you adopt a relational store you will likely gain a lot of advantage by shredding the JSON into table(s). This is how an RDBMS is meant to work. It may be difficult to settle on a schema, however, if there is great variabilty in your forms.

There may be some advantage to holding, in addition to the submissions, a separate list (document / table) for each answer and pointing to all submissions which contain that value. This may become unworkable at scale because there will be a document keyed by answer holding an array of submission IDs. When any of those submissions is updated this document will also have to be updated. This will be a bottleneck when there are a large number of updates per second. It will require careful programming to keep the submission document and answer cross-reference document in sync during updates since Mongo does not have transactions that span documents.