0

Assume a Parent -> Child relationship in RDBMS. questionnaire table and question table. question table has a foreign key questionnaire_id to store which questionnaire it belongs to.

Questionaire can have n number of Questions, say max 500.

When a user creates/updates a questionnaire API call sends the big json document to the server.

questionnaire: {
  id: 'xx'
  name:'xxxx',
  questions: [
     {
       id: 'xx',
       txt:'xxxxxx'
       .......
     },
     {
       id: 'xx',
       txt:'xxxxxx'
        .......
    },
    {
       id: 'xx',
       txt:'xxxxxx'
       .......
    }

  ]
}

When we read the questionnaire we are expected to retrieve child questions in the same order as it came in. So the order of the questions had to be persisted somewhere.

Approach 1: Put an order column in question table and use that to order by. Problem with this approach is that updates are too slow. All child elements have to be updated with new positions when a question shuffle happens in the client.

Approach 2: Put a json column in the parent that stores array of child ids in the incoming order. So now a client-side question shuffle only requires a single update query to parent questionnaire table. Sort happens in the code. Also note that no of questions will not exceed say 500.

Which approach do you think is the best and why? Also is there any better alternative approach?

  • Can you expand on what you mean by question shuffle in the client? – jmoreno Feb 2 at 15:44
  • You don't have to update all the entries in the order column when you change one if you leave gaps between them, i.e. don't initially number them 1,2,3... but 10,20,30... . You even take it to an extreme and number them 500, 1500, 2000... Then when you need to reorder you can insert questions in between, and only in rare cases would you have to change existing numbers. – bdsl Feb 2 at 18:41
1

If the order of the question in a questionnaire is fixed, then approach 1 is the way to go.

It is not normal that the update is very slow, whatever the size of your questionnaires. But instead of ruining your relational schema because of performance problems, you'd better solve the latter ones: so make sure that that you have an index on the questionnaire_id. Consider is to have a non-changeable unique question_id and have a separate question_number. In this case, the update of the number is straight forward and you do not need to worry about avoiding duplicate values during the renumbering when a shuffling occurs.

If you would use approach 2, you would not handle correctly the data in the relational model. Sooner or later you would have an issue (for example, how would you make a SQL query that ensures that the data is shown in the right order ?) requiring cumbersome ad-hoc processing, that in approach 1 would be done by the relational engine.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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