coasterman
Registered User.
- Local time
- Yesterday, 17:49
- Joined
- Oct 1, 2012
- Messages
- 59
Hi
I'm using a real world example of what happens in my own company so I can try and improve my understanding of table relationships.
THe scenario.
We have multiple teams all doing similar work. Each team has a team leader and each team has a team name. A team leader only ever manages one team at a time
non team leader employees are regularly moved between teams as work volumes fluctuate.
The team leaders vary rarely move changes teams and so are typically associated with the same team name (but I appreciate rarely is distinct from never) If they did ever move the team leader inherits the new team name rather then his/her new staff inheriting a different team name via their new team leader
My first thought is all people should sit in a tblEmployees having EmpID_PK and a TeamleaderID_FK so a form containing the team leader name combo box could have as its record source the EmpID from tblEmployees table. A similar approach would be applied to the Team name
What I am struggling with is if a non team leader employee is, assigned a different TeamleaderID_FK then the TeamNameID_FK isn't automatically updated. I can see the association between the team leader and the team name but cant get how to express this in a table relationship.
Do I need a another table perhaps tblTeamLeader(TeamLeaderID_PK,TeamID_FK,EmployeeID_FK) and if so how would I set up the relations to the main tblEmployees and tblTeam
I'm using a real world example of what happens in my own company so I can try and improve my understanding of table relationships.
THe scenario.
We have multiple teams all doing similar work. Each team has a team leader and each team has a team name. A team leader only ever manages one team at a time
non team leader employees are regularly moved between teams as work volumes fluctuate.
The team leaders vary rarely move changes teams and so are typically associated with the same team name (but I appreciate rarely is distinct from never) If they did ever move the team leader inherits the new team name rather then his/her new staff inheriting a different team name via their new team leader
My first thought is all people should sit in a tblEmployees having EmpID_PK and a TeamleaderID_FK so a form containing the team leader name combo box could have as its record source the EmpID from tblEmployees table. A similar approach would be applied to the Team name
What I am struggling with is if a non team leader employee is, assigned a different TeamleaderID_FK then the TeamNameID_FK isn't automatically updated. I can see the association between the team leader and the team name but cant get how to express this in a table relationship.
Do I need a another table perhaps tblTeamLeader(TeamLeaderID_PK,TeamID_FK,EmployeeID_FK) and if so how would I set up the relations to the main tblEmployees and tblTeam