What is the Correct Way To Have An Adjustments View Between Two Tables in SQL

Question

I'm looking for a way to non-destructively alter data. I have one table that has data that shouldn't be edited - it is the master data. I would like to have user edits on this data, but in order to maintain the integrity of the master data I've created a second table that is a mirror of the structure of the master table.

My thinking is that I basically would make a view (vAdjustedData) such that this data is UNIONed together giving me a source that was the most up-to-date version of the data. (The purpose of this is to allow users to undo their data in the adjusted table and have the master tables data reverted to be current).

Columns in the tables will be nullable. The idea is that when a user wishes to make an edit that at that time the master record will be copied, changes applied, and then saved to the adjustments table. My idea for a UNION would mask the original master record with the adjusted record such that when placed in a view I will get the "current" data. When this adjustment is deleted the view will "automatically" roll back.

So I have Table_Master and Table_Adjusted. Table_Master has a primary key. Table_Adjusted also has a primary key, but it is a foreign key to the primary key of Table_Master. If both tables had a varchar column I would expect to write a view that was something similar to this:

(SELECT ID, Value
FROM Table_Adjusted)
UNION
(SELECT ID, Value
FROM Table_Master
WHERE ID NOT IN (SELECT ID FROM Table_Adjusted))

The UNION above should bring all the adjusted values and then all of the values from the master that did not have an adjustment record.

Is this the correct way to do this? To me it seems inefficient. I thought an EXCEPT would work (using SQL2K8) but it didn't seem appropriate.

Answer 1

I'm assuming that you want the adjusted table to only adjust values and not add or remove values. Your query has a disadvantage that it is in theory possible to add new values which don't even exist in the original table. This version prevents this possible risk:

SELECT Table_Master.ID,
    CASE WHEN Table_Adjusted.ID IS NULL THEN Table_Master.Value
         ELSE Table_Adjusted.Value
    END AS Value
FROM Table_Master
LEFT JOIN Table_Adjusted
ON Table_Master.ID = Table_Adjusted.ID

Also your query uses a UNION DISTINCT but really you only need a UNION ALL. This slightly modified version of your query should run slightly faster:

SELECT ID, Value
FROM Table_Adjusted
UNION ALL
SELECT ID, Value
FROM Table_Master
WHERE ID NOT IN (SELECT ID FROM Table_Adjusted)