I am building a database that consists of a few tables that are used as data sources for a form our application generates.
[form] [question] [responses] id id id name form_id question_id desc question response created created created modified modified modified
This is a loose example, but a web form is generated using
[form] and then questions generated using
[question] table. The responses are captured in
[responses] table. The problem I see with this, is if a response is captured, and then the
question is modified, the historical data will be out of sync (meaning that the
response would be directed at the original pre-modified
question). This would be an auditors nightmare.
What is the best way to approach this? I feel I need to keep some sort of version information or shadow records of some kind, but I am not sure the best way to go about it.