1

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.

Question

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.

The 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_name from Alice to Dave on line id 9
  • change the start_year for 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_records wants 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?
2
  • 1
    1. Forget the black hole; 2. Write your app using a regular, non-black-hole database; 3. Validate; 4. Write a job that synchronizes your database with the black hole.
    – John Wu
    Oct 15 at 16:15
  • Another way to interpret: I store my records locally in table A, and store my insert/deletes operations to the external one in tables B and C locally as well. Then I'd need the result of applying all operations in B and C would end up match A. I already uses a regular database so I could query for the latest set of data right away. I cannot validate against the external one since it is a black hole so in some way I have to track the operations that I've sent to it. Also I cannot synchronize my database as a one-off thing as this integration requires daily sync as my internal data grows.
    – valeesi
    Oct 15 at 17:28
0

I would suggest to modify your approach to move inserted, deleted and updated items all in the same table. Mark deleted items by a new boolean 'is_deleted' column. That way you can find the latest version (deleted or not) with one simple query.

This reminds a lot of event sourcing, which takes these ideas much further, by sending events specific to the business domain instead of database operations. In your example you could send "Customer Name Changed" and "Customer Start Year Changed" events, for example in a Kafka topic. Then the data model of your internal data source and the black hole could chose whatever database layout is most appropriate and you don't have to worry about syncing on that level. I am not necessarily suggesting that event sourcing is the right solution for you, but might be worth the time to see whether some of its ideas might apply to your situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.