Advice on mixing MongoDB w/ MySQL for a web application

Question

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

The problem lies in storing the completed form submissions. Each form can and will vary in the number and combination of form elements, and with a relational database my options are somewhat limited to dynamically creating a new table to hold the submissions of each form (seems like a bad path to go down) or storing each of the submitted forms as JSON in a TEXT column (losing all useful querying abilities of RDBMSs)

I've never actually used MongoDB in a production project before, but I'm thinking it might be a good idea to use my MySQL relational database to store all the forms created by certain users of my application, and then store all the submissions in MongoDB with each document referencing the UUID of the form in MySQL.

The first disadvantage I can think of with this approach is there's no referential integrity between form submissions and the forms located in MySQL. If I delete a form in MySQL, all of the form submissions will have to be manually deleted (if I want to replicate the 'Cascade' effect)

Would I store all of my form submissions for all of my forms in a single MongoDB collection as individual documents? Any advice is greatly appreciated. :)

---

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

I'm now considering creating a new collection to hold all the submissions from each unique form type.

---

EDIT 2

After some careful consideration and the advice of others I've decided to abandon my dual-database approach to solving this problem in favor of a relational-database schema that I think solves the problem of creating dynamic forms and saving the form submissions in such a way that they're easily query-able for complex reporting.

Essentially every record in 'forms' represents a unique form that was built by a user. 'forms_fields' has a foreign key that references the form and an enum-type with the options: 1. checkbox 2. textfield 3. textarea 4. select 5. multi-select 6. date

'forms_fields_options' contains all of the 'options' a select field would have. With these three tables, users can create customized forms.

When another user fills out & submits the form, a record is created in forms_submissions. For each field, a corresponding record will be created in 'forms_submissions_fields' that references the form submission and the forms_fields_id. The final table, 'forms_submissions_options_multiselect' is essentially a join-table to indicate which options from a multi-select form field the user selected.

Answer 1

A colleague of mine recently lead a webinar on just this subject, titled "Hybrid Applications with MongoDB and RDBMS". You can view it here: http://www.10gen.com/events/hybrid-applications

From the comments, it looks as though you have already decided to go the RDBMS route, but hopefully this can give you some ideas for a future project, or be beneficial to someone else reading this thread.

Good luck with your application!

Answer 2

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

Using a tool like MongoDB could be a good fit for the flexible results you want to save, however, there are some trade-offs here, but they may not be exactly what you're expecting.

... storing each of the submitted forms as JSON in a TEXT column (losing all useful querying abilities of RDBMSs)

How many form submissions are you planning to have? What type of querying are you planning to do?

My experience with MongoDB is that it performs very poorly when you're querying against data that is not indexed. In addition, aggregation is generally done in batches using Map/Reduce (or the new Aggregation Framework).

If you compare the complexity of doing roll-ups or the efficiency of doing queries, it's not clear that MongoDB is significantly better here than EAV.

If I delete a form in MySQL, all of the form submissions will have to be manually deleted

Oddly, I have rarely seen this as a problem as you will probably never delete the form in SQL. you will likely do a logical delete and never really remove anything. So this is probably a moot point.

Would I store all of my form submissions for all of my forms in a single MongoDB collection as individual documents?

Again depends on how many forms and submissions you're planning to get. If you have lots of both, then using a collection / submission is going to be very difficult to shard later on.

Honestly, I would use a single collection and then override the _id field to something that can reasonably be used as a shard key. There are some fancy tricks you can play here, but that's beyond the scope of this little write-up.

Summary

End of the day, you can definitely use MongoDB for this problem, but it's not a "home run". If you are unfamiliar with MongoDB, this is definitely a fair "learning project", but expect to hit some roadblocks around querying and aggregation.