2 Replies Latest reply on Nov 27, 2012 1:15 AM by ClaudiuNemes

    Database Relationships Help Needed

    ClaudiuNemes

      Title

      Database Relationships Help Needed

      Post

           Hi there,

           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,

           Yours,

           Claudiu Nemes.

      relationships.jpg

        • 1. Re: Database Relationships Help Needed
          davidanders

               Student has many Mentors

               Mentor has many Students

               A many to many relationship requires a Join Table. In this case Disbursements.

               Disbursements Table can have many Table Occurances to enable you to view the Disbursements of the Mentor, of the quarter, of the year.

               What are Table Occurances?
          Tutorial: What are Table Occurrences?

          • 2. Re: Database Relationships Help Needed
            ClaudiuNemes

                 Hi David,

                 Actually no.

                 1. One student will have only one mentor!

                 2. Mentors will have at least 5 students but no more than 12. So one mentor will have more students.

                 3. Students will have 4 disbursements per year, mentors will have 12 disbursements per year.

                 What I'm not able to set in the relationships is:

                 Condition: If I have set in "S_DisbursementYears" and/or "M_DisbursementYears", into the 'Year' field let's say 7 years (records) 2011 to 2017

                 Outcome: When I go to the "S_Disbursements" and/or "M_Disbursements" tables to see for Students a 7 records list like:

                 2011 D_1 D_2 D_3 D_4 Total

                 2012 D_1 D_2 D_3 D_4 Total

                 2013 D_1 D_2 D_3 D_4 Total

                 2014 D_1 D_2 D_3 D_4 Total

                 2015 D_1 D_2 D_3 D_4 Total

                 2016 D_1 D_2 D_3 D_4 Total

                 2017 D_1 D_2 D_3 D_4 Total

                 and for mentors a list like the one bellow:

                 2011 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2012 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2013 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2014 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2015 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2016 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 2017 D_1 D_2 D_3 D_4 D_5 D_6 D_7 D_8 D_9 D_10 D_11 D_12 Total

                 By this I want to avoid creating 28 disbursement fileds for students 2011D1 to 2017D4 and 84 disbursement fields for mentors 2011D1 to 2017D12.

                 Many thanks for your time,

                 Yours,

                 Claudiu Nemes