1

I'm embarking upon a project where the goal is to create a bidirectional sync mechanism between two database systems with differing schemas. One is a custom CRM app build on MongoDB, the other is Salesforce (conceptually SQL), but I'm looking to build a mechanism that can theoretically link and sync any two database systems that contain 'similar' data. I have the technical skills to build interfaces to both systems but I'm looking for advice on the best way to approach the challenge. My initial thinking is that I will need to build a fairly complex entity-relationship mapping tool that allows me to declare/define linkages between the two systems. These linkage definitions will contain structures that cope with the fact that the cardinality/schema structures within the two systems are different. Functions within each system will then monitor changes and feed these into a central mechanism that will transform the data into a different 'shape' for insertion into the 'other' system. I'm anticipating that my mechanism will include a data-store that holds a lot of information about primary keys, secondary keys, current field values etc, etc.

Does anyone have any words of wisdom on this challenge? Is anyone aware of any technical papers on schema matching that might help? Or research papers that might contain helpful notions? Or open source tools that provide some capability in any of these areas? Thanks very much.

|improve this question
1

If this is feasible depends heavily on what 'similar' data means, and if the systems are sufficiently different, this can become arbitrary complex, and you may have to resdesign the existing systems for it. It is usually not sufficient to have business entities in both systems which can be mapped to each other in a 1:1 fashion - all kind of transactions involving these entities must be mapped as well.

Let us make an example: since we talking about CRMs, you surely have customer entities in both systems. First thing you have to ensure here are primary keys and unique indexes don't collide. If only one of the systems will forbid to use the same name twice, you can already run into trouble: if a customer c1 is added to system 1 and a different one c2 to system 2, but accidentally both have the same name, what will this mean to the syncing process when system 1 has a unique constraint on the name?

  • Maybe the customer in system 1 was entered earlier, so c1 "wins", but if c2 cannot be transfered to system 1 without manual intervention, maybe including a bunch of c2 related data which then cannot be transfered to system 1, your users probably won't be happy.

  • If the synching mechanism will try to "merge" both customers automatically, but in reality these are different companies, your users won't be happy either.

  • The syncing mechanism could also be allowed to resolve the issue by automatic renaming to keep c2 separate from c1 in system 1, but this needs a thorough analysis of how this renaming should take place and how the users can deal with such renamed customer records.

This problem can be overcome if you use surrogate IDs exclusively with different ID ranges for each system, and no other unique indexes. That is how DB replication (between system with equal schemas) is often implemented, but you are talking about existing systems - are you free to redesign them in that way?

Even if you solve this, what will happen if in system 1, there is extra data for each customer which will forbid deletion of it as soon as it is entered, and this extra data has no pendant in system 2? Now in system 2, someone is allowed to delete c1 (since there is no such related data), but in system 1 this deletion is not allowed. What will this mean to the syncing process?

This can be overcome by forbidding direct deletions for entities in system 2, at least after they were synchronized to system 1, which means you have to track the syncing state and model this into the system.

What this all means here is:

  • it may be possible, but you have to design the replication / syncing deeply into the system.

Don't expect to bring a generic bidirectional synching mechanism just afterwards to two existing systems as a "separate, independent module", without heavily touching the existing design. Using the "unidirectional" approach, with one leading system, is an order of magnitude easier to realize and avoids most of those pain points.

|improve this answer
2

You should not do this.

Doing a unidirectional sync is challenging, adds a pile of constraints to your system, but is a fairly well known problem at this point. When you make things bidirectional, you lose your single source of truth. How do you prevent update loops? What happens if a race happens where records conflict? How do you do deletes without the other system replacing the record?

It would be better instead to make one of the systems your source of truth and change its users to do dual updates, usually using some sort of intermediary (a message bus or durable queue).

|improve this answer
  • Thanks for your answer. I appreciate it is tricky, but I can see solutions to your issues by using timestamps, audit-trail data and configuration settings. Out of interest, did you vote to close the question? – Journeyman Dec 5 '19 at 16:29
  • 1
    @Journeyman - I did not vote to close. This seems like a reasonable enough question. It is perhaps borderline too broad, or borderline asking how to write code so I can see why some might vote to close. My experience with various timestamp/audit solutions has shown them to be... unreliable. – Telastyn Dec 5 '19 at 16:38
  • Thanks @Telastyn. I know that the change-stream that I will receive from each system will contain accurate field-level UTC time-stamps, so I'm planning to couple these with config settings to deal with source-of-truth and deletion matters. Thanks again. – Journeyman Dec 5 '19 at 16:45
  • 1
    @journeyman - Your timestamps are not guaranteed to be perfectly synced. There is always going to be some mismatch, slowly growing until NTP corrects it. For some use cases that won't matter - you can just use first-in-wins or some similar heuristic. For other things, that sort of inaccuracy might not be acceptable. – Telastyn Dec 5 '19 at 16:50
  • @journeyman, the code you have for performing the bidirectional syncing is also prone to bugs. If a buggy code is deployed and both the databases go out of sync, then you have a difficulty is managing the system. We worked on a bidirectional sync, and burnt ourselves badly. – Nachiappan Kumarappan Dec 7 '19 at 11:21

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.