Problem Statement :
Relational DB schema as below
Employee 1 ---> Many --Contact (e.g. Mobile Phone number,email id ,Office Phone number,Home Phone Number)
Contact Description 1 ->Many Contact (Contact Description Master Table where contact types are mentioned e.g. EMAIL,MOBILE,HOME_PHONE etc. )
Employee Many -> Many Department (An Employee can be associate with many Departments e.g. HR,Admin vice versa)
Employee Many---> One Role (Many Employee associated with one role and one role with many Employee Here Role is master table with many other fields like description ,weightage etc)
Employee Many---> One Status (Many Employee associated with one Status)
Employee One---> Many Address (Home Addresses,Temporary Address etc)
Employee
Role
contactmethod
channel
department
Status
Address
Here User can randomly search on many diffrent criterias at the same time as below
e.g. Search for Employee with name "tom joe" with address "Washington" and has department HR and in order by Role and status
Response will have e.g. one or many fields from each of the above 7 or more tables
Since the number of records in DB for each employee is too high and data is not going to be constant which means
Employee can change address,phone,departments frequenly and Use is looking for the data in realtime it is difficult to manage
the PERFORMANCE Requirement with DB queries is the elastic search is the ideal as a solution?
I have following questions in my mind
1: Will not it duplicate Employee data in ELasticSearch as well as in Relational
2: How to maintain consistency and provide user realtime data e.g. Any change in Relational Db has to be propogated in ELasticSearch immediately
3: I am thinking of document structure as below
"Employee":[
{
"id":1,
"name": "tom john",
"Contacts":[
{
"mobile":123,
"type":"MOBILE"
},
{
"phone":223333
"type":"PHONE"
}
]
"Address":[
{
"city": "New york"
"ZIP": 12343
"type":"PERMANENT"
},
{
"city": "New york"
"ZIP": 12343
"type":"TEMPORARY"
}
]
}
.. simillar data for ROLE,DEPT etc tables
]
4: Also what if a one of Address or Contacts of an employee changes does it need to form abve entire document and put the
same index definition in ElasticSearch ?
E.g. if mobile changes from 123 to 456 as above then do I need to reform the entire document (or get current employee document )
and push update in ElasticSearch ?
So that it looks like
"Contacts":[
{
"mobile":456,
"type":"MOBILE"
},
{
"phone":223333
"type":"PHONE"
}
]