Hello AskTom Team,

Hope you could help me with this.

Business case:

1) We have system of records which we provide to customers. We refresh this data on demand (could be push or pull) on customer's on-premise instance
2) Cusotmer can override (update) any of attributes we provide OR they can create their own system of records (insert)
3) Cusotmer should be able to revert back to original value of system of record that we provide (step 1)

Our legacy system is implemented this way:

Note: This is a very simplified representation of how it is actually implemented. Actual implementation is much complicated than this but this would demonstrate the case

-- over all idea
1) Create a table that holds system of records
2) Create replica of the table which holds modified records or created records
3) create a view on top of these two tables to show combination of original/modified/customer created data

Folllwing demonstrates the case:

a) Table DEPT_PUBLIC has system of records (original golden set)
b) Table DEPT_PRIVATE has modified system of records AND newly created records by customer
c) positive DPETNO are original records (generated by oracle SEQUENCE)
d) negitive DEPTNO are created by customer (generated by oracle SEQUENCE)
e) all the modified and newly created records are inserted in DEPT_PRIVATE
- we do not actually UPDATE data in DEPT_PUBLIC as we want to keep original golden data as-is
- first time UPDATE of any record is actually INSERT into DEPT_PRIVATE
- only changed attributes are reflected in DEPT_PRIVATE, un-changed attrubutes have NULL value
- subsequent updates would be UPDATES in DEPT_PRIVATE table
- INSERT of new recors would be INSERT in DEPT_PRIVATE table with new -ve DEPTNO
- subsequent updates of private records are in DEPT_PRIVATE table

DROP TABLE DEPT_PUBLIC;

-- create system of records table
CREATE TABLE DEPT_PUBLIC
       (DEPTNO NUMBER,
        DNAME VARCHAR2(100),
        LOC VARCHAR2(100) );
        
-- create primary key
ALTER TABLE DEPT_PUBLIC  ADD CONSTRAINT DEPT_PUBLIC_PK PRIMARY KEY ( DEPTNO ) ;
        
--insert system of records data
INSERT INTO DEPT_PUBLIC VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT_PUBLIC VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT_PUBLIC VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT_PUBLIC VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

SELECT * FROM DEPT_PUBLIC;

DROP TABLE DEPT_PRIVATE;

-- create replica of DEPT_PUBLIC
CREATE TABLE DEPT_PRIVATE
        (DEPTNO NUMBER,
        DNAME VARCHAR2(100),
        LOC VARCHAR2(100) );
        
-- create Primary Key
ALTER TABLE DEPT_PRIVATE  ADD CONSTRAINT DEPT_PRIVATE_PK PRIMARY KEY ( DEPTNO ) ;
        
-- modify/update ACCOUNTING to MY_ACCOUNTING
-- only changed attributes are reflected in DEPT_PRIVATE table, un changed attrubutes are left as NULL
INSERT INTO DEPT_PRIVATE VALUES (10, 'MY_ACCOUNTING', NULL);

-- modify/update OPERATIONS to MY_OPERATIONS
-- only changed attributes are reflected in DEPT_PRIVATE table, un changed attrubutes are left as NULL
INSERT INTO DEPT_PRIVATE VALUES (40, 'MY_OPERATIONS', NULL);

--insert a new custom record MY_NEW_DEPARTMENT
INSERT INTO DEPT_PRIVATE VALUES (-100, 'MY_NEW_DEPARTMENT', 'MY_LOCATION');

COMMIT;

SELECT * FROM DEPT_PRIVATE;

CREATE OR REPLACE VIEW DEPT (DEPTNO,DNAME,LOC) AS
SELECT pub.DEPTNO,pub.DNAME,pub.LOC
  FROM DEPT_PUBLIC pub
 WHERE NOT EXISTS
    (SELECT 1
    FROM DEPT_PRIVATE pri
    WHERE pub.DEPTNO=pri.DEPTNO
    )
UNION ALL
SELECT DEPTNO,DNAME,LOC
  FROM 
   (SELECT pri.DEPTNO,
           COALESCE(pri.DNAME,pub.DNAME) DNAME,
           COALESCE(pri.LOC,pub.LOC) LOC
    FROM DEPT_PRIVATE pri LEFT JOIN DEPT_PUBLIC pub ON pub.DEPTNO = pri.DEPTNO
    WHERE SIGN(pri.DEPTNO )!= -1
   )
UNION ALL
SELECT pri.DEPTNO,pri.DNAME,pri.LOC
  FROM DEPT_PRIVATE pri
  WHERE SIGN(pri.DEPTNO )= -1;
  
select * From DEPT ORDER BY DEPTNO;

So far so good. The problem arises when customer wants to NULLyfy a value.
The intention is, instead of modifying a value the customer want to make the value as NULL.
In this case COALESCE would not work.

Question:

1) How to handle NULL update of original record
2) if you have to re-design this, how would you do it?

Please let me know if you need any further clarifications.

SELECT pub.DEPTNO,pub.DNAME,pub.LOC
  FROM DEPT_PUBLIC pub
 WHERE NOT EXISTS
    (SELECT 1
    FROM DEPT_PRIVATE pri
    WHERE pub.DEPTNO=pri.DEPTNO
    )
UNION ALL
SELECT pri.DEPTNO,pri.DNAME,pri.LOC
  FROM DEPT_PRIVATE pri;