I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem.
User Table:
- userID
- firstName
- lastName
- phone
Form Table currently contains:
- userID
- question1
- question2
- question3
I'm struggling with the fact that, due to the marketing nature of this tool, the form structure and fields are almost guaranteed to change sometime in the future. There will likely be fields added/subtracted in the future. Any changes in the future are completely unknown now but could become something to the extent of:
Form Table could change to:
- userID
- question1
- question3
- questionA
- questionB
Where question2 was removed and two completely new fields were added.
Should each "version" of the form have its own table or just add columns to the existing table as the form changes? Are there better ways to handle this?
Best Answer
My approach would be to make the table with a VARCHAR(MAX) field for the questions, and another field for the answers, and just dump each into those fields. That table can have date and version information too to make things easier. Everything should be structured within the varchar field, so it can be parsed out later.
A second approach that a co-worker of mine took was to have a table of just questions, and then a table for the form that links the questions to a certain form. Then have a table for responses, which would only link a person's response to a question.
So the forms table would have a series of records that link form 127 to questions 5,3,24,58. The response table would link person 626's responses to those questions, with a date. The worst part of it would be having to make cross joins just to see a single person's responses to a single form, but that's what views were made for.