I have started learning and building micro service based project, but I always stuck into scoping situation and end up creating a sort of monolith. in my below foo-bar example, please suggest what should be the scoping and how achieve desired output.
Services or tables
- Employees
- Department
- Employee-Department-Mapping
Assumption
Employee or Department does not have any cross reference for each other all relationships are maintained in 3rd table Employee-Department-Mapping
Relationship could be One-To-Many or Many-To-Many based on business to business, in this example it is One-To-Many (Department with Employee)
Requirement
I want to get total salaries paid department wise. similar to below query, here I am making simple joins on 3 tables. which is only possible if all are in the same database and single micro service.
Select d.DepartmentName, SUM(e.salary)
from Employee e, Department d, Employee-Department-Mapping c
where d.DepartmentName == c.DepartmentName
AND e.Employee == c.Employee
Group By c.DepatmentId
constraints
- employee table has salary information
- employee department relationship is maintained by 3rd table.
I am not looking for exact answer but an approach to solve such problems. wonder how would you design your micro service if you need aggregated outputs, would you bring all these tables in the single microservice ? I do not want pull millions of records from everymicroservice and do aggregation in memory.