1

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.

1

The three ways I can see this being handled is to either:

1) Have an audit table that tracks any changes to the questions\forms tables. This would store the records being updated before they are changed, along with a date.

2) If you foresee the questions\forms being changed often, it might be better to store the actual questions in the response table, only use the question table to generate the forms.

3) Don't allow questions to be changed at all. Any modifications to a question would generate a brand new question_id. This would ensure data integrity.

  • option 3 sounds pretty good to me :) – Barry Chapman Nov 19 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.