Database Relationships Help Needed
I need help to start design a database which will use some table relationships.
Let me briefly explain the database. It will have 2 major sets of data: Students and Mentors. One Mentor will monitor maximum 12 students. So each student will be mentored by one mentor and each mentor will have a maximum of 12 mentored students.
Now I have to build a disbursement system for Students (4 disbursements/year) and one for Mentors (12 disbursements/year). The entire project is estimated to run 7 years.
What I do:
- I create a table for students called "Students" that contain personal data, evaluations etc.;
- a table "S_Disbursements" (contain the following fields 'Year', 'D_1', 'D_2', 'D_3', 'D_4' and 'Total' - sum of D_1:D_4);
- a table "S_DisbursementYears" (with a field called "Years" that have 7 records 2011 to 2017);
- a table "S_DisbursementPerYear" (contain the following fields 'Year', 'D_1', 'D_2', 'D_3', 'D_4' and 'Total' - sum of D_1:D_4);
I'll do the same for mentors but with a 12 disbursements/year system.
What I want is that when I set the number of years in "S_DisbursementYears" to let's say 7 records (2011 to 2017) to have in "S_Disbursements" 7 records according to each year:
2011 D_1 D_2 D_3 D_4 Total
2017 D_1 D_2 D_3 D_4 Total
I have tried in many way's to made this relationship work but obviouslly I don't succeed. Any help will be appreciated. I'll attach a relationship screen as well.
Many thanks for your time,