Search code examples
sql-serverdatabase-designsurveydata-integrity

Best way to maintain data integrity in a survey database


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

  1. Creating a new "QuestionId" each time a question is updated and marking the existing question as inactive.
  2. or Storing the original "question" with each "answer"

I have not done something like this before with a relational database and am having trouble locating any tutorials.. Any suggestions?


Solution

  • 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