I am creating an internal inspection app/survey and am unsure what is the best way to maintain the historical data integrity when an update is made to a survey question. For example, if an admin goes in to modify a predetermined survey question, I do not want any of the completed surveys to have their related data updated. This way, when an audit is done the survey is seen as it was originally. I am using SQL Server.
The options Ive come up with on my own are either
I have not done something like this before with a relational database and am having trouble locating any tutorials.. Any suggestions?
I would do something like this I would add two columns,
1) data_ID
2) Data_Original_ID
Every time you create a record the Data_ID field gets populated but Data_Original_ID remains null until you update the record.
When ever a record is updated The Data_Original_ID column gets populated with the original ID and remains unchanged.
But every time a row is updated it gets assigned a new data_ID but Data_Original_ID will keep its value.
Give a view to your user to see only the latest data something like this
View Definition
CREATE VIEW [dbo].[view_TableView]
AS
SELECT a.* FROM TableName AS a
WHERE (a.Data_Original_ID IS NULL AND a.data_ID NOT IN (SELECT DISTINCT Data_Original_ID
FROM TableName
WHERE Data_Original_ID IS NOT NULL)
OR a.data_ID = (SELECT MAX(data_ID)
FROM TableName
WHERE Data_Original_ID = a.Data_Original_ID)
GO