I'm writing an application that would have a feature similar to Google Forms.
In this feature, some end users of the system will have the ability to design custom forms. Those forms will subsequently be filled by other users. So there are two classes of data that need to be stored in the database:
- Form definition: form sections, fields, types of fields, choices available for certain fields, whether the user is confined to selecting one or allowed multiple choices, whether the field is optional or required, etc.
- Form data: an instance of a filled form. It has a many-to-one relationship with form definition.
The system needs to allow users of the system to collaboratively design their own forms and modify them over time. To keep things sane, a form can only be used once it is "committed" or published, after which time the form schema cannot be modified.
Users can also fill instances of these forms. They can save a partially filled instance that hasn't been completely validated yet, as a draft. Once they "file" the form, it gets validated and if valid it will not be modifiable anymore, but will be stored and becomes "view only". We will also need the ability to generate reports of responses to those forms and export form data as a CSV file.
My plan is to use JSONB fields in my PostgreSQL database to store both the form definition as well as the form filing.
Is there a way to use the form definition JSON blob to automatically derive some sort of schema against which form filing instances can be constrained?
Are there open source examples or academic papers discussing this sort of design which we can learn or borrow from?
Asked by hashemi
(111 rep)
Oct 7, 2021, 04:40 AM