troubleshooting Question

Database Model for an Employee DB

Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland asked on
DatabasesMicrosoft Access
16 Comments1 Solution183 ViewsLast Modified:
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:

Blank-Diagram.png
I decided to start with the core tables (employees and Departments/teams) and I have created this ERD:

Sanagate-ERD-E.png
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:

Blank-Diagram--1-.png
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros