What datastore to use for user designed forms - Any advantages with NoSQL for EAV

Question

I need to allow for user designed form creation, via a web interface, in my software. ie, they create a question, a type (text, radio, checkboxes etc), options if needed(radio/check), then add, and continue in this process until they have created all fields in the form.

There will be no queries done against them except to view/fill/print them, ie they are adding 'questionnaires' that may be filled out unlimited number of times (some may be 20 times, some millions of times).

After some research it seemed like an EAV type solution sounded good, except there's a lot of negative views on that out there. Many people suggest using a NoSQL database for this type of situation but I don't really see the advantages - you are still having a form with many fields and then results with many fields.

There would be a single possible value for some fields (text/text_area/date), but many would also have multiple options (radio buttons, select drop downs, check boxes).

Here's a sample design in traditional SQL:

form: creator_id, name

form_field: form_id, order, question, type (text, text_area, date, radio, select, check)

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

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

form_field_value: form_result_id, form_field_id, form_field_option_id, value (if a field of options the value would be blank, field of text form_field_option_id would be blank)

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

Are there any advantages in putting this in a NoSQL database, ie Mongo or something similar? If so can you give me concrete examples for what they are and give me a sample design? I've seen a lot of answers like 'NoSQL is better suited to this' but I have no experience in this area, is it because of faster retrieval of results, or what? And what downsides would using NoSQL introduce?

Thanks

Answer 1

MongoDB would probably be a better fit for this app than a relational database. Your fundamental entities, the form design and the form results, are effectively documents whose contents are intrinsically bound together, i.e. a form field makes little sense outside the context of its parent form.

MongoDB would allow you to store these documents as a single structure each rather than scattered across various tables as in your relational data model.

This is YAML just because it's cleaner to write than JSON. The underlying structure would 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 only need to store the keys where they're needed rather than having a column for every thing in every context as you would in a relational database. e.g. there's no need for required: false -- if that's the default then just leave it out.

MongoDB documents have intrinsic order so there's no need to create a field to hold the fields' orders within the form design.

The form results would be stored in the same way. Just store them naturally as you'd 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