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.