DB Design for non static front-end

Question

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:

1. 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.
2. 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?

Answer 1

You should consider the idea that specifications change. Today there are 50 default fields, tomorrow there could be 51. You do not want to be adding a column every time the requirements change. The reverse might also be true; what if a column is no longer mandatory or is no longer required?

The sample ERD below allows the following:

• people can take a questionnaire more than once
• the questions, whether default or custom, can be different every time
• the questions can be in different languages through the use of LocaleId
• you can tell what was default or custom through the QuestionTypeId in Questions

There are more tables required for the infrastructure of supplying the default questions and inserting custom questions.

Also, my head was reeling with questions/questionnaire overload. Some better naming would help make things more readable.

Answer 2

I would go for option 1., but I think there is no reason for:

a json string which defines the custom fields.

Some databases ( eg. PostgreSQL ) have support for traversing JSON, so to get list of keys from a JSON you make:

select key from json_each('{"a":"foo", "b":"bar"}');

So in your example you can just select that JSON from answers table ( via answer ID ) and use json_each to generate list of questions. That way you won't have duplicated data in your database.

Also, I would store it as jsonb type ( supported by some DB, eg. PostgreSQL ).