I have a REMOTE suppliers' table with, let's say, a code and a name, and several client databases will be creating new suppliers.
code | name ------+------- 1 | Mark 2 | John 3 | Jodie
I thought that a federated table would fit the best, as every client could create new suppliers in a centralized table which assigns incremental codes,
Obviously, if the Internet connection is down, clients won't be able to create new suppliers, so this won't work for me.
I thought that the best idea would be assigning a prefix to each client, and the code count would be assigned locally.
Local table client A
code | name -------+------ A1 | Mark A2 | Jodie
Local table client B
code | name ------+------ B1 | John
Then, the data would be merged into a single remote table.
Remote merged table
code | name ------+--------- A1 | Mark B1 | John A2 | Jodie
But at this point, I am lost, I don't know if there is a solution for this problem, or I would need to merge them with a cron job + script.
Maybe a multiple-masters-to-1-slave would work?
Is there any way to schedule merge jobs?
Any idea would be appreciated.