Link to home
Create AccountLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

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:

User generated image
I decided to start with the core tables (employees and Departments/teams) and I have created this ERD:

User generated image
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:

User generated image
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
Avatar of Massimo Scola
Massimo Scola
Flag of Switzerland image

ASKER

For clarification, I was given the following information: It could be that in the future, an additional hierarchy level might be added - that is, if the team gets too big. Like here:

User generated image
The split-up team will not be headed by a team (which is illogical) but by a person - a specialist.
Avatar of Scott McDaniel (EE MVE )
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.
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?
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).
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
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?

User generated image
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.
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?

User generated image
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 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
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?
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That sounds good. I will take it from here.
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