1

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?

  • 1
    Creating a long VARCHAR column to hold a list of things is a firable offense, so yes, option 2 is better. – Kilian Foth Jan 29 '16 at 10:50
  • I presume you're talking relational here, otherwise you'd just dump the lot in a document store as JSON or whatever. If you're talking relational and there's only one custom field, you could just make it a single column accepting text - though I agree you might want to normalise it to allow for easier expansion if your requirements are likely to change. Probably I'd just dump the form in a document store though. – Mr Cochese Jan 29 '16 at 11:07
  • Yes its relational. But the number of custom fields is unlimited. – serious Jan 29 '16 at 11:13
  • 1
    Is JSON the Holy Grail? – Tulains Córdova Jan 29 '16 at 13:31
  • You should look into the EAV data storage model – Adam Zuckerman Jan 29 '16 at 16:11
2

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

ERD

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.

0

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 ).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.