Which datastore to use for custom forms - Any benefits with NoSQL for EAV

Question

I need to allow the creation of a user form using the web interface in my software. those. they create a question, type (text, radio, checkboxes, etc.), parameters, if necessary (radio / check), and then add and continue in this process until they create all the fields in the form.

Requests will not be executed against them, except for viewing / filling / printing, i.e. they add “questionnaires” that can be completed an unlimited number of times (some can be 20 times, several million times).

After some research, it seemed that an EAV-type solution seemed good, with the exception of a large number of negative views on it. Many people suggest using a NoSQL database for this type of situation, but I really don't see the benefits - you still have a form with many fields, and then results with many fields.

There would be one possible value for some fields (text / text_area / date), but many would also have several parameters (radio buttons, select drop-down lists, check boxes).

Here's a sample sample in traditional SQL:

form: creator_id, name

form_field: form_id, order, question, type (text, text, date, radio, selection, verification)

form_field_option: form_field_id, name, value, order (this is used for radio / select / check)

form_result: form_id, application_id (not the name I use, but all the results will belong to the "application")

form_field_value: form_result_id, form_field_id, form_field_option_id, value (if the parameter field is empty, the text field form_field_option_id will be empty)

It would seem quite easy to build forms based on this and get results. It may or may not be exactly effective, but to say that the typical form is 5-30 questions, would it be bad?

Are there any advantages to putting this in a NoSQL database, i.e. Mongo or something like that? If so, can you give me specific examples of what they are and give me a design sample? I have seen many answers, such as "NoSQL is better for this", but I have no experience in this area, because of a faster search for results or what? And what disadvantages will NoSQL use?

thanks

Answer 1

MongoDB is likely to be better suited for this application than a relational database. Your fundamental entities, form design and form results are actually documents whose contents are inextricably linked with each other, that is, the form field has little meaning outside the context of its parent form.

MongoDB allows you to store these documents as a single structure, and not scattered across various tables, as in your relational data model.

This is YAML just because it is cleaner than JSON. The basic structure will be the same.

_id: 12345 creator: Adrian name: NoSQL form demonstrator fields: - id: first_name label: First name type: text required: true - id: last_name label: Last name type: text required: true - id: dob label: Date of birth type: date - id: bio label: Biography type: textarea - id: drink label: What would you like to drink? type: select options: - id: tea label: Tea - id: coffee label: Coffee - id: beer label: Beer - id: water label: Mineral water - id: mailing_list label: Join our mailing list? type: check default: false 

Note:

You need to store keys only where they are needed, and not have a column for every thing in every context, like in a relational database. for example, there is no need for required: false - if this is the default value, then just leave it.

MongoDB documents have an internal order, so there is no need to create a field to store field orders in the form design.

Form results will be saved in the same way. Just save them naturally, as you would expect:

 _id: 545245 form_id: 12345 name: NoSQL form demonstrator results: - id: first_name label: First name type: text value: Adrian - id: last_name label: Last name type: text value: Short - id: dob label: Date of birth type: date value: 1970-01-01 - id: bio label: Biography type: textarea value: Doing things on the internet - id: drink label: What would you like to drink? type: select value: Tea - id: mailing_list label: Join our mailing list? type: check value: false