You might want to think outside the box here. Your main issue isn't related to a database solution that will somehow replicate the clients that are unable to connect to the central database, but more of a conceptual issue of how to replicate database clients that can be OFFLINE (not connected to the main server) or ONLINE (connected to the main server) and/or the data that is modified OFFLINE or ONLINE.
The (Basic) Requirements
For the pseudo concept I'm thinking of implementing a CRM (customer relationship management) tool, where salespeople can insert and modify data on their laptops. The data has to be synced with a master database (looks like what you are trying to achieve).
So I'll have a centralized database and databases on the clients. The clients are located all over the world. Some report directly with the main headquarters in California (USA), other salespeople will be located in different locations like London (UK), Paris (FR), etc.
Questions to Ask
Will the data be stored in the decentralized locaitons?
Will the data be stored only in the central locaiton?
Will people be modifying data when on the road?
Will people at the decentralized locations be modifying data directly?
On which database/system?
What if the link to California goes down?
How will the assistants work with the CRM data?
If changes are made to the data, who takes precedence? Is the salesperson the master of modifications? The assistant? The person at Headquarters in California?
Could the data be split so as to allow for multi-side modification? HQ is allowed to change the address of the customer, but the salesperson is responsible for the actual (personal) contact information of the company representatives.
Do local decentralized locations (e.g. London, Paris) know more about the local companies than the HQ in California? Would it be better for them to have a decentralized country database? If so should certain countries be linked to the decentralized databases, rather than allowing HQ to be the master of all data?
Online / Offline
Is OFFLINE actually OFFLINE or just OFFLINE from a (de-)centralized main database?
Is ONLINE the connection to the (de-)centralized main database?
Could you envision a main / satellite / client setup where each location has its own database? (Could be anything from MySQL to Microsoft SQL Server to Centura/Gupta).
You could have markers on certain tables (two columns) that determine when a record was last modified. This could be a date column
lastmodified and a
00000000000000000000000..... which contains the position of the last person to modify the data.
Contents of changedby Column in Contact Person Table
||| | |
||| | +---------: Changed by Salesperson Levrat in Paris
||| +-------------------------: Changed by Salesperson Murry at HQ
||+--------------------------------------------: Changed in Paris
|+---------------------------------------------: Changed in London
+----------------------------------------------: Changed at HQ
When the decentralized server in Paris syncs with the HQ it overwrites the changes made at the HQ, because Paris knows better what Contact Person information is relevant for the customer in Bordeaux.
When the salesperson Levrat comes back into the office, then his information overwrites the data in Paris. Which subsequently is resynced with the database at HQ.
Each synchronization resets the flags.
Building a Solution
Now this is just a basic example and if you add the
lastmodified column, then you could possible build a synchronisation the permits the last changed record to overwrite anything up or down the line, depending on your requirements.
Add another column and you have could have certain data only synchronized to the satellite locations.
Add a pulldown button to the applicaiton login and the client can login to the central (HQ), decentralized (PARIS) or local database.
Add a push-button in the client applicaiton and the client is asked to synchronize with Paris (default) or HQ (optional).
Add the split between main customer data and customer representatives and you can sync some data in one direction and other data in the other direction depending on who has data sovereignty.
First you need to know what your current requirements are and what your future requirement could be. Then you can start to think about implementing a tool (out of the box), a complex solution or your own custom tailored build.
It's a long journey. If you take the time to analyze your requirements first and to invest a lot of time before you even start programming or looking for a solution, then you'll pass the finishing line. Otherwise you may fail to reach the finishing line....