Complex Table Relationships Question
I currently have the following:
- An employee table (built already)– where each individual gets a row with their demographic info
- An annual employment snapshot (built already) – where each individual gets a row for each year they're employed (e.g., if someone has been employed for five years, they get five rows)
Do you have any ideas on how I can achieve the following?
- Track each individual's mentors by year in the employee snapshot table (in any given year, an individual can have several mentors). I envision it looking like what's below.
- Have the mentor linked to the employee table (so, each mentor can be linked to their demographic information). I envision it looking like what's below.
Employment snapshot data:
Name (links to employee table) |
Year |
Mentors (links to a mentors table) |
Employee1 |
2020 |
Mentors |
Employee1 |
2021 |
Mentors |
When I click Employee1's 2020 mentors table link I get:
Name (links to employee table) |
Year |
Mentor (also links to employee table) |
Role |
Employee1 |
2020 |
Mentor1 |
Role1 |
Employee1 |
2020 |
Mentor2 |
Role2 |
Employee1 |
2020 |
Mentor3 |
Role3 |
And the mentors are linked to the employee table (Mentors will always be someone in the people table)
Thank you.
------------------------------
Hagos Hagos
------------------------------