DB Structure for custom forms

Hi all,

I am building an app that has the ability to create/edit forms with custom fields as a feature. Form templates can be assigned to personal record to be completed by users (google forms? :) )

I am using PG with schemas and formbuilder.js to build the form. Right now I am saving the template as a JSON string, which is copied to the record data column (as a JSON string) is created and modified when the form is saved (in the model) by adding the values to the record

How would you structure the db/solution?

Thanks!

Bump.

Hey Luca,

Here are just a few thoughts FWIW, I haven't done much with form templating so that may be a specific use case that nullifies everything below...

... by modifying the form template (i.e. adding/removing fields) every existing record should be also modified.

If you have your associations set up, then just create an update function that goes through each form that used that template and update accordingly for the changes made to the template. There may be a more efficient way to handle this if you're dealing with thousands of records that need to be updated at once.

I would like to attach multiple forms to a single record

Structure your JSON with versions, this should allow you to store as many forms in a single record as you want. So for example, instead of:

data => {
  foo => value,
  bar => value
}

You could do something like:

data => {
    form_1 => {
      foo => value,
      bar => value,
    },
    form_2 => {
      baz => value
    }
}

I am also not sure JSON string manipulation is the right/most efficient way

 
Are you experiencing any specific performance hits or are you referring to maintainability? I've used some relatively complex json structures to store all sorts of stuff and I've never really had any issues. Searching json fields can be a bit tedious at times but that's just an inconvenience.

Hey Jacob thanks for your reply !

I think perhaps I should keep form template structure and values separate and only make references to both in records. That way when the template changes all record entries automatically change (This needs some sort of archive functionality so that data deleted when fields are deleted is not completely lost).


Ciao,
Luca

Hey Luca, 

No problem, sorry nothing was of more help, definitely sounds like it's above my current level!

Do you have any material that you've been specifically referencing as you've been building / working with this setup that you would mind sharing? Your setup really has me curious as to how it all works and I'd love to do some reading on the subject if you have it readily available!

Thanks, and good luck! :)

Hey Jacob no need to be sorry, as a matter of fact I was looking for other's opinion on this one and your information was very relevant so thanks again.

I have been using this article as example http://mobyinc.com/labs/building-a-dynamic-form-system. It does more and less what I describe above. The only difference is that I might still use formbuilder.js rather than rebuild the form creation UI myself for now and store each field to the form_fields table in the model running through the formbuilder json string. 

Hey Luca, 

Thanks for the link! I had that same article bookmarked from a while back but never made it back to it, haha!

I think I have a better idea of what you're talking about here now, so I still think the need for multiple forms to be attached to a single record could be accomplished by just adding another layer to the json you store as I previously mentioned... you'll just have to do a little more work to make sure it's the proper version being fetched.

As for your other issue: I would like to move from this method as it has some limitations, one being that by modifying the form template (i.e. adding/removing fields) every existing record should be also modified.

I really have a bad feeling about handling it this way. If you add a new field to a form, then every old form would be updated to just have a nil value since it would require the user to come back to the form and update it. If you remove a field, then once every other record is updated, that data is gone for good (unless you do a soft delete, but that's a whole new issue), so a simple mistake (accidentally deleted the wrong field) could have a horrific ripple effect. So I think if you instead just did versions of a form, you could add or remove fields without being so destructive.

However, there could be some legit reasons to have this ability, but I'm really not sure of a good way to handle this "automagically" without doing what I mentioned in my first response by making a method that checks if a field has been added or removed upon save, and then go through each saved record and adjust the saved json string accordingly. I just don't know of any other way for the database to know a value in a json string has changed and therefore must update every other instance of that particular form that has been submitted previously. If you figure this out, please come back and share!