1

My application uses a relational database but there is a feature requirement to display a survey form to users at the end of each quarter/year etc. I understand that this data maybe used to do some analysis in near future but the issue is the form would have many key value pairs and I am sure that it will keep on changing.

I did some research and found that people suggest to have NoSQL databases for these kind of stuff but I can't change my database for just one feature, other option is to have a JSON column in the database table and store all the key-value pairs there.

I don't have enough experience to know what can be tradeoff or if there are other alternatives?

  • Some companies delegate this feature to sites like SurveyMonkey, or even Google Forms. Is that an option for your project? – Emerson Cardoso Dec 20 '17 at 11:53
  • Who is changing the survey? Are we talking about several surveys which are dynamically loaded, or one survey which may have questions tacked on? My instinct tells me not to abandon your database, but don't force the solution to work with your current database either. – Neil Dec 20 '17 at 11:54
  • BTW - I'm curious: can you check if my answer is helpful for you in your question How to write correct loops? – Emerson Cardoso Dec 20 '17 at 11:58
  • @Neil there will be same survey but may have additional fields based on requirements. – CodeYogi Dec 20 '17 at 15:26
  • @EmersonCardoso we need to show some data from the database as well so we cannot use third party tools. – CodeYogi Dec 20 '17 at 15:26
2

Lots of key-value pairs with changing requirements is the use case for a so-called EAV model, which can be implemented perfectly inside a relational DB system. Though sometimes considered to be an antipattern, there are certain cases where the usage of this model is justified, and from your question, it sounds your situation is one of them. See also this former question for more information about pros and cons of EAV models.

For example, for your surveys, I can imagine the entities are probably the questions, the attributes are the possible answer types (yes/no answer, textual answer, answer from a list of predefined values, integer value on a scale from 0 to 10, etc), and the values are the actual answers which must match the answer type. Of course, this approach needs further refinement, but I think you get the idea.

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.