1

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:

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

Also, what if the form is edited and fields are changed?

1
  • That is really a good question. Dec 12 '15 at 5:32
1

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.

1
  • Can you elaborate what you mean by "unworkable at scale"? I am leaning towards MongoDB with one collection per form. There's also a question of how to deal with form updates (Here MongoDB can deal with varied fields between documents). What do you suggest for a site similar to thumbtack.com ?
    – JC Lee
    Oct 1 '15 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.