We are currently building a dynamic survey system. Currently we have the following layout;
So a Survey has a number Sections has a number of questions, etc.
The advice I'm after would be down to storing the answers. Currently, the answers has a FK to the question, but my concern is if the question is changed at anypoint it would affect the answer.
for example, if the question were is it red? and then changed to is it not red? any answers previously would then become invalid.
My first instinct was to add a QuestionText to the Answer Class (as in the diagram). This would make the FK to the Question redundant. The issue I would then have is regarding summerizing results will become a bit of a pain.
The other option would be to controller this from my service level, so for example if a question is edited then i would do a check at this point and ensure that if the said question has any answers disallow the edit and create a new question?
What would people recommend (as I assume this will have cropped up somewhere else before).
Thanks in advance.
Even systems like Qualtrics do not handle this issue very well. If your survey has been published and you change a question, they explicitly warn the user that previous answers may not be valid, and may invalidate the survey results.
I would recommend adding a timestamp to the answer table,
datetimesubmitted, and a timestamp to the question table,
datetimelastedited. If the
datetimelastedited is more recent than
datetimesubmitted, warn the user, and perhaps leave those data points out of the summary statistics.