Search code examples
javasql-servermicroservicesdatabase-trigger

How can I keep databases of two web applications in sync?

I have two webapps, each with its own backend microservice and each microservice has its own database. For any changes in tables of database of microservice1, I want to change(create/update) entries in tables of database of microservice 2. How can I do that?

Context:

Webapp 1: UI for human resource coordinators to schedule an interview.

Microservice 1: Backend service that schedules an interview.

DB for microservice 1: Stores information related to interview of a candidate. 

  interviews: [ {
      "interviewId": "1",
      "candidateId": "abc",
      "interviewers": [
      {
         "interviewer_name": "Thor",
         "schedule": {
            "startTime": "",
            "endTime": "",
            "roomNumber": 101
         }
      },
      {
         "interviewer_name": "Loki",
         "schedule": {
            "startTime": "",
            "endTime": "",
            "roomNumber": 101
         }
      }
   ]
} ]

Webapp 2: UI for interviewers to coordinate on questions to ask in an interview.

Microservice 2: Backend service for interviewers to coordinate on question selection. i.e. each interviewer selects what question he/she is going to ask from a candidate in an interview (this is to ensure no two interviewers end up asking same question from a candidate).

DB for microservice 2: Schemas

// QuestionBank : Table containing questions, that interviewers can select.

// Interviewers : Table containing all interviewers in the firm.

// InterviewToInterviewer : (many to many mapping of interviews with interviewers). One interview can have many interviewers, and each interviewer can participate in many interviews.

// InterviewToInterviewerToQuestion : (many to many mapping of interviewToInterviewers with questions). For each interview an Interviewer can select many questions and each of the question in a questionbank can be a part of many interviewToInterviewer entry.

Current Workflow:

As soon as interview is scheduled from webapp1:

  1. An email is sent to all the interviewers. Email contains a link to a webapp 2, clicking on this link opens webapp2 that provides an interface for interviewers to select questions they plan to ask in an interview.

Requirement:

  1. If the questions are not selected by interviewer, then I want to send reminders to them. For this I want webapp2 to know that an interview is scheduled.

  2. I want webapp2 to know about any lineup changes (in a given interview, interviewer is changed or an interview is cancelled etc) that happens.

Solutions I thought off:

  1. As soon as interview is scheduled/changed from webapp1, webapp1 will calls webapp2 (webapp2 exposes an API for that) to let webapp2 know that a new interview is created or an existing interview is updated.

  2. For any new entry/update in interview table in DB1, a DB trigger is launched to DB2. I am not sure whether this is possible also.

Out of the two approaches above can someone help me with the pros and cons of one choosing over other. Or there is some other alternative approach to achieve this.

Leads here are appreciated.

Solution

  • Do not make these two service tightly coupled, you might face latency if 2nd service has dependency on any other service. What if 2nd service is not up. So try to make use of messaging queue here. Have your first service to publish interview event which can be in status(new, canceled etc. along with other details interview id, interviewers etc). Make 2nd service to listen to it. As you said interview request goes to multiple interviewers.Configure a reminder job/scheduler in 2nd service, and have configuration for that in your data store with details eg: interviewers id, interview id, time range etc so that you can send reminders to interviewers easily. This should help you. Let me know if i have not got your question correct. Microservices use change event to get data consistency in given system.