MongoDB w / MySQL mixing tip for web application

Question

I have a web application using a relational database (MySQL). We are adding a new feature that will allow certain users to dynamically create “forms” from a pool of optional form elements and distribute these forms for completion / submission to other users.

The problem is storing the completed forms. Each form can and will vary in the number and combination of form elements, and with a relational database my parameters are somewhat limited by dynamically creating a new table to store representations of each form (it seems that this is a bad way to go) or to store each of the submitted forms as JSON in TEXT column (losing all the useful features of RDBMS queries)

I had never used MongoDB in a production project before, but I think it would be nice to use my MySQL relational database to store all forms created by certain users of my application, and then save all the views in MongoDB with every document referencing the form's UUID in MySQL.

The first drawback that I can come up with with this approach is the lack of referential integrity between the form submissions and the forms hosted in MySQL. If I delete the form in MySQL, all forms must be deleted manually (if I want to replicate the Cascade effect)

Will I store all my forms for all my forms in one MongoDB collection as separate documents? Any advice is much appreciated. :)

---

EDIT 1 Based on the documentation here: http://www.mongodb.org/display/DOCS/Using+a+Large+Number+of+Collections

Now I am considering the possibility of creating a new collection to store all views from each unique type of form.

---

EDIT 2

After some careful consideration and recommendations of others, I decided to abandon my approach with two databases to solve this problem in favor of a relational database scheme, which, I think, solves the problem of creating dynamic forms and saving forms in such a way that they can easily handle queries for complex reports.

Essentially, each entry in 'forms' is a unique form created by the user. 'forms_fields' has a foreign key that refers to the form and type of the enumeration with parameters: 1. checkbox 2. text field 3. textarea 4. select 5. multi-select 6. date

'forms_fields_options' contains all the "parameters" that will have a selection field. Using these three tables, users can create custom forms.

When another user fills out and submits the form, an entry is created in forms_submissions. For each field, a corresponding record will be created in 'forms_submissions_fields', which refers to the form view and form_fields_id. The summary table, 'forms_submissions_options_multiselect', is essentially a connection table that indicates which parameters from the multiple choice form field the user has selected.

Answer 1

Recently, my colleague conducted a webinar on this topic called "Hybrid Applications with MongoDB and RDBMS." You can watch it here: http://www.10gen.com/events/hybrid-applications

From the comments, it seems that you have already decided to follow the RDBMS path, but I hope this can give you some ideas for a future project or be useful for someone else reading this topic.

Good luck with your application!

Answer 2

This can be done in SQL using EAV . Therefore, NoSQL is definitely not required.

Using a tool like MongoDB may be appropriate for the flexible results you want to keep, however there are some tradeoffs here, but they may not be exactly what you expect.

... saving each of the submitted forms as JSON in the TEXT column ( lose all useful query features for the RDBMS )

How many presentation forms do you plan to have? What type of requests do you plan to make?

My experience with MongoDB is that it works very poorly when you request data that is not indexed. In addition, aggregation is usually performed in batches using Map / Reduce (or the new aggregation structure).

If you compare the complexity of performing collapses or the efficiency of executing queries, it is not clear that MongoDB is much better here than EAV.

If I delete the form in MySQL, all forms must be deleted manually

Oddly enough, I rarely saw this as a problem, since you'll probably never delete a form in SQL. you are likely to do a logical deletion and never delete anything. So this is probably a moot point.

Would I save all my forms for all my forms in one MongoDB collection as separate documents?

Again, it depends on how many forms and representations you plan to get. If you have a lot of them, then using the collection / view will be very difficult to print later.

Honestly, I would use one collection, and then redefine the _id field to what can reasonably be used as a fragment key. There are some fancy tricks you can play here, but that is beyond the scope of this small review.

Summary

End of the day, you can definitely use MongoDB for this problem, but it is not a "home run". If you are not familiar with MongoDB, this is certainly a fair “training project,” but expect it to run into some obstacles around queries and aggregation.

Answer 3

I think that you ignore the fact that RDBMS will allow things like EAV (Entity-Attribute-Value, which is terrible if you abuse it, but it can be great in moderation) or join tables to build several ordered relationships from one forms to various form elements.

I do not suggest that RDBMS is ideal for everything, or even for your situation, but I know that I had to create similar systems and never had to go noSQL to maintain them in a reasonable way.

Edit: More for the point ... saving the actual field values ​​leads to another relation from the original form elements, but if your user interface maintains all the consistency, you can do it as a whole. I would say, looking further, in which noSQL solutions to allow specific types of value-based queries, you need to shed light on your options.