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?
