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?
ALTER TABLE ... ADD COLUMN ...
, but be aware that it can be costly.