For a multi-site / branch system, synchronizing databases have always been a complicated challenge...
The easiest way is to have a central DB Server on the cloud where all your site connects to and read/write data directly off the central DB..
I've been thinking of a "logic" to do "delayed" mirroring for many years , just playing w/ ideas.. but this time i need to apply it.
Here's to describe my scenario
Company A has a chain of stores, and 1 main office
The main office has a database of inventory from each store , and as well as customers
The challenge for me is that:
1) Yes there will be a central database server on the cloud via the Main office's reliable internet connection..
2) the remote stores must have a snappy system (no delay) due to sluggish and intermittent internet connection.
3) and the stores must not be dependent on internet connection to function
Obviously this means maintaining totally separate systems on each store..
i was thinking of designing things this way:
1) The main server at the office maintains records of all branch data
2) the stores maintain their own data for real time operations
3) whenever the stores are able to connect to the internet, the stores will SYNC with the main server based on WHAT is new and WHAT has changed...
4) The system is able to determine what needs to be updated on each side via a dedicated timestamp column that is ALWAYS updated whenever something is changed
eg.
ON each store the table looks like this:
TABLE CUSTOMERS
cid (customer ID)
name
address
last_update (epoch of when row was added or updated)
on the main server it looks like this:
main_cid (the unique IDs)
cid (the actual customer ID of the customer from the stores)
name
address
last_update
5) the system is designed to ALWAYS use and update a sort of "last_update" column
6) FOR UPDATES
Whenever the remote store can connect to the internet, it attempts to prepare and upload data (perhaps even SQL statements) of all the necessary updates done on the remote side since the "last_update" done.
7) FOR NEW ROWS - similar to UPDATES, it's all based on the last_update stamp
8) For DELETED ROWS - 2 ways to tackle this either
a) design the system to never actually DELETE rows, just mark them as "deleted"
b) maintain a temporary that remembers all deleted rows and reports that to the server
SO far this is a ONE WAY mirroring, where the REMOTE STORES send updates to the main server.. and at this point i think the logic is sound , yes?
Now it gets ugly when the MAIN server makes an update on its copy of the data, and if THOSE updates need to reflect on the remote databases as well..
Thankfully, for this particular project's case, REAL TIME updates are not necessary... the main server really just needs to hold the data from all branches together for management/accounting/etc purposes... and it's a rare case when the office needs to update data that came from the remote branches...
so i was thinking:
A) Design the system to allow UPDATES to the relevant tables/data ONLY when the remote branch has acknowledge that an update is coming = meaning THEY CAN NOT USE the system during updates... perhaps schedule Main to Branch updates between 12mn-6am only.
😎 Do not give any UPDATE capabilities on the main server, and if they need to update something like a customer's address, they need to send that request to the relevant branch and they do that update from there == and the oNE WAY mirroring will take care of the rest
I've been drawing logic/relations/flow on paper for a while now i can't seem to find a problem w/ the above.. so i'm about to dive into this style.. but hoping for some wisdom from this community first lol
Regards to all of you!
