Designing low fault software rest API calls to achieve data synchronization between two different systems

Question

I have been looking for a better design approach and will be providing my problem statement here with a similar example. As I don't have logic already in place but have the solution in mind which I think of first trying a better approach.

I have two different system-A and system-B wherein system-B is dependent on system-A.

I am working on a registration process for system-A where I will be registering all the schools in an area and storing the school table in my database(which will consist of schoolId, schoolname, teacherId). And I will have a teacher table(teacherId, schoolId, studentId) in it. Also, a student table (studentId, teacherId, schoolId). And coming to my requirement I will be adding new schools whenever a new school is built or come to use for registration and a school will have multiple teachers who will be handling multiple students.So When a record for any of the above table is added, I need to update my other system.

Inorder to achieve this I am thinking to write a trigger in system-A which will be looking for any records inserted in these above tables or when a update happens like if a teacher has been changed for a group of students, then these trigger gets called and sends it to system-B via Rest api calls. And any school can be added any time and the teacher or students can be added anytime once a school is added.

So what is the best process to achieve data synchronization assuming that any network call can fail any time. In my design I donot have any column which will track if that record has been processed or not. So am I using a better approach? or is there any better and efficient way?.I am using Java, Rest template and sql server to store data.

Please help with any corrections to the above design and if I miss anything.

Answer 1

You need some form of memory on system-A about which records were changed but not yet synchronized, there's no way around it.

Proposed solution:

Let the triggers for database changes insert entries into a CHANGES table instead of trying to synchronize immediately. To handle ordering-sensitive changes these entries should have a sequential ID.

After an entry has been added, a synchronization procedure is run which enumerates entries in order and transmits them to system-B. Upon successful transmission, each entry is deleted. If a transmission isn't successful, the process stops and maybe sends a notification e-mail to someone who can look into the issue.

This procedure is also run periodically to catch up on intermittent failures.

If you want to separate trigger handling and synchronization further, you may omit calling the sync procedure from the triggers and run it periodically only. Depending on the interval between calls, you will have short windows in which the databases are not synchronized; if they are just used to look up data and not for automated processing this is most likely acceptable.