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
Last activity: Nov 18, 2019, 07:48 PM