May 12, 2016 at 2:12 am
Hello Folks,
In one of my project, requirement is to consolidate all employee and their related tables into one centralized database so that data warehouse can be build on top of this as well as front end application can use this database to share employee information across the locations.
We have database structure same at all locations and there are two important tables we want to consolidate first and below are the details on these tables and how data looks like in those.
1. Company(CompanyID(PK),Name,IsDefault): At one location, only one record can have IsDefault=1 and rest 0
2. Employee(EmpNo(PK),CompanyID(FK),ManagerEmpNo,EmployeeReference): There are below possibilities of having record in employee table
A. If employee and his manager belongs to same location then their CompanyID would be 1
B. If employee belongs to same location then in his record CompanyId=1 and if his manager belongs to different location then in his record CompanyID=2(Please note this manager record does also exists in location 2 database, where in his record CompanyID=1)
C. EmployeeReference would be unique across all locations. Like in manager case, his EmployeeReference will same in location 1 and location 2 databases lets say "E-1232".
D. EmpNo is unique for location, it might possible manager's EmpNo=2 in location 1 and location 2 as well
Now when we try to integrate employee table from different location into a single employee table then it is creating multiple references for employee's manager since manager's EmpNo can get repeat across locations databases
Please let me know how can I resolve this issue?
July 23, 2017 at 12:30 pm
For this kind of data structure, the company id of the manager should only have its value based on his manager not based on each employee under him.
For example:
A. If an employee and his manager belongs to the same location, THE EMPLOYEE'S COMPANYID WOULD BE 1.
B. If an employee's manager belongs to a different location, THE EMPLOYEE'S COMPANYID WOULD BE 2.
C. IF AN EMPLOYEE IS A MANAGER, A OR B SHOULD BE APPLIED SINCE THE MANAGER'S DATA WILL ALSO BE IN EMPLOYEE TABLE.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply