I have to design the database table for my questioner form. I am using Postgres DB. The earlier approach I had taken given below as my number of the question was fixed and the requirement was the one question can only have one answer.
id submit_by_id q-1 q-2 q-3
1 12 a b c
2 12 a b c
3 11 a b c
but later the requirement has changed and one answer can have multiple answers. I don't want to save answer as the JSON format since I have to perform the search, filter, and sorting operation. The problem with the above design is that if you want to save multiple answers you have to repeat redundant information. for example
id submit_by_id q-1 q-2 q-3
1 12 a b c
2 12 a b c
3 11 a b c
4 12 d b c
to overcome this problem I have design two separate table
question table
id question
1 xyz
2 abc
3 cda
answer table
id submit_by_id question_id answer
1 12 1 espn
2 12 1 star
3 11 2 xyz
4 11 3 abc
That way the number of repetitive information could be less. So the question is how do get the collective answer from the query. I want to send the response like
[{q1:[a,c]}, {q2:[a]}]
Or is there any better way to do this.
