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.