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?

CodeYogi
  • 2,186

1 Answers1

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.

Doc Brown
  • 218,378