Sample Header Ad - 728x90

How would I maintain referential integrity across a database where I use versioned records while keeping data in sync?

1 vote
1 answer
105 views
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.
Asked by Barry Chapman (125 rep)
Nov 16, 2019, 02:15 PM
Last activity: Nov 18, 2019, 07:48 PM