Database Model for an Employee DB
I have been asked to create an employee database. This is what I have been able to analyse so far:
The company consists of several departments, each of which has at least one team. Every employee is a member of at least one team. Some teams have a team leader, which in turn is headed by the head of the department. Teams without a team leader are headed by the head of the department directly. Some departments have an administrative team, whose job it is to support the head of department and are thus headed by the department head.
And this is what the hierarchy looks like at the moment:
I decided to start with the core tables (employees and Departments/teams) and I have created this ERD:
This morning, I have been approached by my manager and I have been told that the database should be able to deal with changes in hierarchy. For example, a new hierarchy is added, like a team splitting up like in this diagram:
Then my schema would not work anymore.
Can anyone assist me with this? How do you deal when a new level is added?
Obviously, that's something that would be part of the functional requirement of the db (Access).
Thanks for your support.
Massimo
Sanagate-ERD-E.vdx
The company consists of several departments, each of which has at least one team. Every employee is a member of at least one team. Some teams have a team leader, which in turn is headed by the head of the department. Teams without a team leader are headed by the head of the department directly. Some departments have an administrative team, whose job it is to support the head of department and are thus headed by the department head.
And this is what the hierarchy looks like at the moment:
I decided to start with the core tables (employees and Departments/teams) and I have created this ERD:
This morning, I have been approached by my manager and I have been told that the database should be able to deal with changes in hierarchy. For example, a new hierarchy is added, like a team splitting up like in this diagram:
Then my schema would not work anymore.
Can anyone assist me with this? How do you deal when a new level is added?
Obviously, that's something that would be part of the functional requirement of the db (Access).
Thanks for your support.
Massimo
Sanagate-ERD-E.vdx
If a Team is split, and a new Team were added, would it still be related to the old team? Or would it be an entirely new Team?
If it's not related remove those members from the old Team, add them to the new Team, and add the Team to the Department.
If it is related, then you'd have to add a field to the Team table named ParentTeam (or something like that), and set that to the value of the original team. Assuming a Team can only have one Parent, then that should work. This is known as a Self Join, where an object can be related back to the same type of object.
Also, your flow diagram suggests a Team can be "owned" by a Department and not have a Team Leader. How are you modeling that relationship? I don't see anywhere a Team can be related to a Department, but you may have excluded it from your diagram. On the surface, it would seem that a Team should be related to a Department directly, and then you should select a Team Leader. A Team Leader could also be associated with a Department, but it would not be the linking object between Team and Department. Instead, it would be an attribute of a Team.
If it's not related remove those members from the old Team, add them to the new Team, and add the Team to the Department.
If it is related, then you'd have to add a field to the Team table named ParentTeam (or something like that), and set that to the value of the original team. Assuming a Team can only have one Parent, then that should work. This is known as a Self Join, where an object can be related back to the same type of object.
Also, your flow diagram suggests a Team can be "owned" by a Department and not have a Team Leader. How are you modeling that relationship? I don't see anywhere a Team can be related to a Department, but you may have excluded it from your diagram. On the surface, it would seem that a Team should be related to a Department directly, and then you should select a Team Leader. A Team Leader could also be associated with a Department, but it would not be the linking object between Team and Department. Instead, it would be an attribute of a Team.
ASKER
Hi Scott
An example: The call centre team. It might be split in the future, into Call Centre German, Call Centre Italian and Call Centre French.
It will not be headed by a team, but by a person. You asked whether the new team would still relate to the old one. Yes, it would because the people are the same (see call centre example). I cannot do the self-join example because I am not relating to a team (my error in the question asked) but to a person.
Some teams are not headed by a team leader but by the head of the department.
The teams are related in the DepartmentTeams table. Every team has a team leader - an employee foreign key.
I would then select the head of the department to be the team leader. That should work, shouldn't it?
An example: The call centre team. It might be split in the future, into Call Centre German, Call Centre Italian and Call Centre French.
It will not be headed by a team, but by a person. You asked whether the new team would still relate to the old one. Yes, it would because the people are the same (see call centre example). I cannot do the self-join example because I am not relating to a team (my error in the question asked) but to a person.
Some teams are not headed by a team leader but by the head of the department.
The teams are related in the DepartmentTeams table. Every team has a team leader - an employee foreign key.
I would then select the head of the department to be the team leader. That should work, shouldn't it?
I would then select the head of the department to be the team leader. That should work, shouldn't it?It would work, but your data flow diagram misrepresents that. Your Team should be directly related to the Department. The "Team Leader" is simply an attribute of the Team, and wouldn't be a separate entity (and shouldn't, therefore, be included in your ERD).
Sound like you need Teams and also SubTeams (or some other term that may make more sense, like Division, Group, Squad, etc). A SubTeam could be related back to a Team, and would include specific members of that Team. It would be something like:
tSubTeam
----------------
SubTeamID
SubTeamName
TeamID
EmployeeID
You could then have as many SubTeams as needed.
I think of this kinda like a Baseball team. Everyone is a member of the Team, but you also have specific "groups" of players, like Pitchers, Catcher, Outfielders, etc. In cases like this, you'd have a Team table, and then you'd have a Position table, which would put a player in one or more Positions (a player could be both a Catcher and an Infielder, for example).
ASKER
It would work, but your data flow diagram misrepresents that. Your Team should be directly related to the Department. The "Team Leader" is simply an attribute of the Team, and wouldn't be a separate entity (and shouldn't, therefore, be included in your ERD).
Are you saying that I should have Department as an attribute of team? Like:
Team
TeamID
DepartmentID_FK
TeamLeaderID_FK
Or what do you mean? Do I need to recreate everything?
I thought it was good practice to have a separate table where the Departments and teams are listed.
Personally, it looks like you need a hierarchical table structure. to depict the department\team relationships, something like:
So I would probably have tables:
Departments
Employees
DepartmentEmployees
Teams - just defines the name of the team, mission, that kind of thing
TeamAssignments
TAID
DeptID
TeamID
EmployeeID
EmployeeRole
EffDate
ThruDate
ParentTeamID
So I would probably have tables:
Departments
Employees
DepartmentEmployees
Teams - just defines the name of the team, mission, that kind of thing
TeamAssignments
TAID
DeptID
TeamID
EmployeeID
EmployeeRole
EffDate
ThruDate
ParentTeamID
ASKER
Hi Dale
I think that's what it is ... a hierarchical structure .. because I need to have the ability to expand and shrink hierarchies.
Is TeamAssignments a repeating table? Or what do I put inside TeamAssignments?
Is it like this?
I think that's what it is ... a hierarchical structure .. because I need to have the ability to expand and shrink hierarchies.
Is TeamAssignments a repeating table? Or what do I put inside TeamAssignments?
Is it like this?
Does a Team "belong" to a specific Department? It looks like that's true, and If so you need some way to relate those objects, like storing the DeptID in the Team table. To my mind, Department is an attribute of the TEAM not the EMPLOYEE, so it would not belong in a table that assigns Employees to a Team.
If a Team can belong to multiple departments, then you need to have a Join table, similar to Dale's DepartmentEmployees table.
You can use a Self Join, as Dale and I suggested, to determine any children of a Team. But I'd suggest that attribute be stored in the Team table, since it's not really a attribute of an Employee assignment table. Your Team table (or Dale's TeamAssignment table) should store information about which Team an employee is assigned to, information about their role, dates of service, etc etc.
If a Team can belong to multiple departments, then you need to have a Join table, similar to Dale's DepartmentEmployees table.
You can use a Self Join, as Dale and I suggested, to determine any children of a Team. But I'd suggest that attribute be stored in the Team table, since it's not really a attribute of an Employee assignment table. Your Team table (or Dale's TeamAssignment table) should store information about which Team an employee is assigned to, information about their role, dates of service, etc etc.
ASKER
Yes, every team can only belong to one Department.
I created a SubteamID in team - referencing TeamID of the same table with 1:1 relationship.
Does this ERD look better now?
I created a SubteamID in team - referencing TeamID of the same table with 1:1 relationship.
Does this ERD look better now?
ASKER
One Problem I have with this is the linked TeamID to SubTeamID.
I have created this in Access and the Problem I have is that when I create my first team... what will my subteamID be? Will it be the current one I am creating?
test.accdb
I have created this in Access and the Problem I have is that when I create my first team... what will my subteamID be? Will it be the current one I am creating?
test.accdb
I think I agree with Scott regarding assignment of the DepartmentID to the Teams table.
I would not use a "SubTeamID", but rather a "ParentTeamID", but we may be over thinking this. Are you really talking about creating sub-teams, or simply splitting teams?
I would only consider them to be "sub-teams" if they will report to a parent team. Otherwise, these "split teams" just become new teams and you divide the original team personnel into these two new teams?
Dale
I would not use a "SubTeamID", but rather a "ParentTeamID", but we may be over thinking this. Are you really talking about creating sub-teams, or simply splitting teams?
I would only consider them to be "sub-teams" if they will report to a parent team. Otherwise, these "split teams" just become new teams and you divide the original team personnel into these two new teams?
Dale
ASKER
Hi Dale
The idea is to create an employee database that is flexible/adaptive to support several hierarchies should the company grow (or heaven forbid, shrink).
One example is the call centre. There are many people in that call centre and maybe in the near future, that team will be divided into a German call centre and a French call centre that will report to a new person (not team) - a new level in the hierarchy.
Is this possible with Access to add this flexibility?
The idea is to create an employee database that is flexible/adaptive to support several hierarchies should the company grow (or heaven forbid, shrink).
One example is the call centre. There are many people in that call centre and maybe in the near future, that team will be divided into a German call centre and a French call centre that will report to a new person (not team) - a new level in the hierarchy.
Is this possible with Access to add this flexibility?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That sounds good. I will take it from here.
Thanks a lot for your help.
Thanks a lot for your help.
That's your only accepted solution? I believe you had several other comments which were helpful.
I would agree, this was a great discussion, and Scott provided at least as much, if not more, to this process than I did.
If you are new to EE, you can select multiple responses as "this is my solution" and at the end of that process, you can even award "bonus" points for those responses which have been marked as "No, but helpful".
Dale
If you are new to EE, you can select multiple responses as "this is my solution" and at the end of that process, you can even award "bonus" points for those responses which have been marked as "No, but helpful".
Dale
ASKER
The split-up team will not be headed by a team (which is illogical) but by a person - a specialist.