3

I have a requirement whereby my users will need to be able to build their own forms. Which they can specify different datatypes: Text DateTime Checkboxes All customisable mandatory/optional.

I've seen an example DB design on here which suggested the idea of storing the forms, form elements, element types and element submitted values all in different tables.

Whilst I think this is a good approach, I want to see if this could be expanded on from a performance aspect.

My idea is to store the form templates and form responses as serialised JSON objects, this way would reduce the number of rows stored and joins in queries?

Can anyone think of any drawback from using this approach?

So for instance a form template being

{
     "formName": "Newsletter Signup",
     "Id": "1",
     "elements":
     [
         {
           "id": "1",
           "label": "First Name",
           "type": "text",
           "required": "true"
         },
         {
           "id": "2",
           "label": "Email Address",
           "type": "text",
           "required": "false"
         },
         {
           "id": "3",
           "label": "Agree to terms",
           "type": "checkbox",
           "required": "true"
         }
     ]
 }

And a submission being:

{
         "received": "2016-05-18 09:00:00",
         "formId": "1",
         "elementValues":
         [
             {
               "id": "1",
               "value": "Joe Bloggs"
             },
             {
               "id": "2",
               "value": ""
             },
             {
               "id": "3",
               "value": "true"
             }
         ]
     }
  • 1
    Do you have a performance issue? As we all know by now I hope looking into performance is only interesting is you have a performance issue. Or you reasonably expect one. – Luc Franken Jul 19 '16 at 8:12
  • For the data submitted by these user defined forms how is it consumed later? Do you need run reports against it? Also you may want to look at a NoSQL storage solution. For example MongoDB supports JSON storage out of the box and might give you more flexibility. – Adrian Dec 16 '16 at 1:29
  • I ended up going for my original idea and mapping the data accordingly whenever it was retrieved. However more recently I've been dabbling with NoSQL solutions like Google Firebase so if I had to re-write this functionality I'd try this approach – MNelmes Dec 16 '16 at 21:53
1

One drawback is that when you want to create a backend interface for your form setup, you need to understand how the JSON data is being used and interpreted. Even with a simple database management tool like phpMyAdmin or MS SQL Management Console someone could easily understand how the form is structured and how to make a field required or copy a whole form.

With just JSON this will take much longer to understand. A much wider range of people can use simple databases, many more than JSON.

  • 1
    or just write a base spec that says "this is how the form is modeled" – jleach Aug 18 '16 at 0:01

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.