I need to track data on 4 groups of individuals (adult learners, students, supervisors, stakeholders) over time (semesters).
- Adult learners are employees who have supervisors that can change over time, even during a semester.
- Adult learners may be enrolled in the program for more than one semester.
- Students are assigned to one or more adult learners each semester.
- Students may enrolled for more than one semester.
- Stakeholders are other individuals who may be connected to adult learners (e.g., referral source), students (e.g., faculty advisor), and/or supervisors (e.g., their supervisors) or just "friends" of the program.
For each individual, I want to be able to display current term involvement as well as previous term involvement.
I also need to be able to analyze individual and group data in a variety of ways overall and by term.
I'm struggling with how many tables to use due to the potential for several many-to-many relationships. Once I have that figured out, I can manage the rest. Just getting the main tables figured out is often my greatest stumbling point. I would appreciate suggestions for how to set up my main tables.