I am trying to come up with a data schema/methodology for storing the data collected from various different user application forms.
All forms contain the basic info like name, address, etc but then they diverge in what they collect. Each application will have its own unique subset of questions they want to ask. Because of this one “answers” table is out of the question. Would i be better off normalizing the database into a questions table and answers table? Another option considered would be to have a table of answers for the standard/universal questions and then serializing and storing the other answers in a field. Given that, would i be better moving to a straight up nosql database option? Maybe nosql is overkill and i really just need EAV?
Once information is collected, it won’t be edited. Just needs to be read.
1
If the forms don’t change too often, and their structures are well-defined, I’d suggest going with one table to hold common info, and then other tables to hold values particular to certain forms, linked back to the main table. I think an EAV-based approach would be more appropriate if your system will be expected to handle data from forms that are added/updated very frequently (too fast for you to create/update tables), or if you have a need to have a form-creation tool connected to your system, where users can author whatever type of form they want. I haven’t used any nosql solution but it might also help.