Black hole problem
I am working on matching and daily syncing of our internal data records to an external database that behaves like a black hole, meaning, we cannot view nor extract any data from it for verification. Another of its properties is that one can only do inserts and deletes, so no direct updates.
How to CRUD
So for insertions of new data, it is easy, we just send the data records over in a file marked as create. But for updates, we need to know the original records that we want to update on (must remember and match on all columns), send those data verbatim over in a file marked as delete, once the deletion is done, follow by inserting back the updated records just as if they were new.
My question is: how would you design a local database that can log both our insertions and deletions on the external database, such that when combining the chains of both operations, we know the exact set of records and data that currently exist on the external database? Would it work with two SQL tables logging inserts and deletes separately? If yes, could you give an example of how one could query both tables for the current set of records?
My current idea
Two separate SQL tables to log what I have inserted/deleted.
inserted_records table may look like this:
id | file_version | contract_id | customer_name | start_year 9 | 6 | 1 | Alice | 2015 10 | 6 | 2 | Bob | 2015 11 | 6 | 3 | Charlie | 2015
Accompanied by a separate and empty
deleted_records table with almost identical columns.
Now, if I want to
- change the
customer_namefrom Alice to Dave on line id 9
- change the
start_yearfor Bob from 2015 to 2020 on line id 10
Two new lines in
inserted_records would be generated, line 12 and 13, in turn creating a new insertion file 7.
id | file_version | contract_id | customer_name | start_year 9 | 6 | 1 | Alice | 2015 10 | 6 | 2 | Bob | 2015 11 | 6 | 3 | Charlie | 2015 12 | 7 | 1 | Dave | 2015 13 | 7 | 2 | Bob | 2020
Then their original column values in line 9 and 10 are then copied onto the previously empty
deleted_records, in turn creating a new deletion file 1.
id | file_version | contract_id | customer_name | start_year 1 | 1 | 1 | Alice | 2015 2 | 1 | 2 | Bob | 2015
Now, if I were to send in the deletion file 1 first followed by the insertion file 7, I would get the result that I wanted.
But the problems in my idea are
- When merging and querying for the result of both operations, how do I know that line 1 in
deleted_recordswants to delete line 9 in
inserted_records? A problem of how to track between records in these two tables.
- how can I indicate order of operations between these two tables? Timestamp/hash/foreign key/etc.? As in which file goes before which?