I have a question about best practices to design a database which have to hold the following data:
There is a page with a questionnaire which has default questions like Firstname, Lastname, Street... (50 more fields). Now people can register to the page and add a another custom field to the questionnaire. Here comes the tricky part: This field is only visible on forms the person created. Other people should only see their own custom fields on their own questionnaires.
How is the best practice to design something like that in a database? I can imagine two things:
- Create a table for the questionnaire answers where every field is a column. Add another column for a json string which defines the custom fields (nvarchar(max)) and contains the answer of a client. A seconds table holds questionairs ids of the user and in a second column a json string which defines the custom fields.
- Create a table for the default questionnaire anwsers where every default field is a column. A seconds table holds questionairs ids of the user. Create a third table which contains the custom fields for every user. Col1 UserID, Col2 QuestionnaireID, Col3 QuestionName, Col4 QuestionType. In a fourth table the answers of the clients are linked to table 3.
Probably there is another better way?