I'm asking for your help, because I'm not an advanced mysql user, and I'm not very conscious about every operation that I'm able to do.
The project that I'm working onto is a web-app designed to manage a company. The "admin user" should be able to visualize a organization chart of all the company structure, edit, add employees, etc.
The chart its done, but I'm struggling with the mysql structure. I'm not that expert, in fact, I've never worked with hierarchies, and I'm loosing too much time trying to figure out, probably with a wrong approach.
So, I need some expert tip if it's possibile.
Here is an example on how the ogranic chart structure is
- There are 3 divisions types(A - B - C) -----> *Orange*
- - There are 3 head offices, 1,2,3. -----> *Green*
- - - Every office, can have three divisions. in this case, office 1 has got division A,B,C / office 2 has got division A / office 3 as got division A,C. (customer wants it in this way...)
- - - - Every division has got 5 roles for his employees(*PINK*). In division A, we have for example Sales Manager, Manager, Team manager, sen. agent, agent.
In division B they have different names, and roles are 5.
Division C has only got 2 roles.
So, the organic chart takes the data from a very simple database, where the main columns are ID and parentID (others columns are labels).
There should be a SQL query which builds the data source table, building the company structure starting from other tables:
I'm struggling!
Please, I need to write a function which, starting from the tables of employees, offices and divisions, creates the data source table(table4), in order to populate the organic chart. (IF a entry is already present, update data!).
Don't forget that there are other tables called like "division1_Roles", "division2_Roles" where every roleCode has a roleName.
1 - Manager
2 - Supervisor
3 - Agent ...
In table(3) "Employees" there is another column called "RoleCodeID" which I forgot.
I think that this can be the logic path:
1) Query Divisions table(1) and add them to Chart dataSource (table4)
2) Query Offices table(2), determine which divisions every office have(*how?*), then save in Chart dataSource table(4) the office record.
Note: If a office has got 2 or 3 divisions, there should be created multiple records in table(4) for every office. table4.parentID will change, in order to get the right hierarchy (select * table(4) where ?).
3) Query Employees table(3);
4) Get the EmployeeDivisionID and
IF EmployeeDivisionID == 1 -> query RoleCodes_division1 table to get roleNome
ELSE IF EmployeeDivisionID == > query RoleCodes_division2 table to get roleName
[...]
THEN save the roleName in a variable ($roleName)
5) Add the employee to the Chart dataSource table(4).
IMPORTANT: In the chart, I can add a new employee(under another employee, or office), or an office under a division. Every "box" in the chart has god an ID, which is the ID field in dataSource table(4). In this way, I cannot determine if the user is clicking on an division, office or employee.
For this reason, maybe I need to save officeID, divisionID for every employee in table(4) . But in this way, the other tables are like duplicates.
I don't know folks, I feel a bit mad. Maybe we can try to find a solution step by step, would be a great help for me.
I'm not the kind of person who asks for code, but what I need is the logic to face this problem, and the SQL functions to join the tables correctly. Something like hierarchical sql queries.
I'm using PHP & MySql.
THANKS!!
