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 ScolaInternshipAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Massimo ScolaInternshipAuthor Commented:
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:

The split-up team will not be headed by a team (which is illogical) but by a person - a specialist.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Massimo ScolaInternshipAuthor Commented:
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?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:


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).
Massimo ScolaInternshipAuthor Commented:
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:


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.
Dale FyeOwner, Dev-Soln LLCCommented:
Personally, it looks like you need a hierarchical table structure. to depict the department\team relationships, something like:

So I would probably have tables:
     Teams - just defines the name of the team, mission, that kind of thing
Massimo ScolaInternshipAuthor Commented:
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?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Massimo ScolaInternshipAuthor Commented:
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?

Massimo ScolaInternshipAuthor Commented:
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?
Dale FyeOwner, Dev-Soln LLCCommented:
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?

Massimo ScolaInternshipAuthor Commented:
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?
Dale FyeOwner, Dev-Soln LLCCommented:
Yes, that is possible.

If you anticipate the need to have sub-teams, or sub-sub-teams, then as I indicated above, I would replace your SubTeamID with ParentTeamID.  That field would be NULL by default, but if you create a sub-team, then you would fill in the TeamID of the PARENT record (the Team above the sub-team in the heirarchy) in that field.  This would give you the ability to change this from Team - Subteam to a structure which looks like

      Team                                       - perhaps Division
              Sub-team                        - perhaps Section
                      Sub-Sub-Team       - some other name

Can people be assigned to cross-department teams?  You could do this, as long as your user interface doesn't only allow you to select from employees assigned to the division which has the lead for the team.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Massimo ScolaInternshipAuthor Commented:
That sounds good. I will take it from here.
Thanks a lot for your help.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's your only accepted solution? I believe you had several other comments which were helpful.
Dale FyeOwner, Dev-Soln LLCCommented:
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".

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.