I have a web app that runs on PHP, having database in MySQL. Each company has its local server with own instance of that app (racing halls) and one cloud server with its own instance too.
I have tables like Users, Tickets, RaceResults. Each table has some relation to other tables. What I need is to save Users, Tickets on a cloud instance of the app AND distribute these to other hall instances. RaceResults needs to be only uploaded to cloud.
I have an idea of doing this using these following steps:
- Each table I want to get on a cloud needs to have cloud_id column remembering the ID of the record that is saved on the cloud.
- I need to have a REST API created on both clients and server - client sends an api request to server whenever I need to sync the records - cloud returning new ID - and distribute these records to other client instances if needed.
- Each table I want to sync has it's own relations - which need to be resolved either on client or on server - for example tickets.user_id will be different from the client instance - each of these columns need to be changed before sending or after receiving in order
What I don't like on this approach is
- There are a lot of problems during debugging and error handling - what if this specific user referenced from ticket doesn't exist here?
- What happens if I want to centrally remove a driver - If I delete him in cloud database, it has to somehow process on other machines
- Each table I sync is having its own process to sync, own code to change the fields which will be sent (for example ticket.user_id must be changed to users.cloud_id before sending to the cloud)
- If I add another client instance (hall) to the stack, I need to sync all the tickets to the new hall. When I delete the client instance from the synced stack, I need them to get deleted. This all comes with a lot of code I have to write for each specific case myself.
My question is, is there some working solution, for something similar, or am I doing it completely wrong?