12 Replies Latest reply on Jul 30, 2016 6:36 AM by beverly

    Relationship problem

    macc

      Hi everyone.

       

      I'm working on solution for vocational school. Currently I'm struggling with database structure/setting up relationships.

       

      Students in our school are learning for three years. Student is associated to fixed profession that he or she is learning.

      Each student during school year has to take part in professional course accordingly to his profession.

      So far we allow to study in one of twenty professions.

      Every profession has 3 grades of course (one for first grade students, one for second grade students and one for third grade students).

      Therefore in one school year there can be up to 60 professional courses. This courses takes part outside of school in proper institutions. Every course is linked to 3 institutions (one that organise, one that is actual place where course takes part, and one who has dormitory).

      If student wish, there is possible for him to be accommodated in dormitory during professional courses. Courses usually take 4 weeks.

      As many institutions are organising such courses, in every year we can choose different institution to carry out course.

      And every year start dates, end dates of courses and pricing are changing.

       

       

      So far i managed to do my thing by creating solution for current school year. That solution is no longer sufficient. Now I need to add history/tracking info. I'm puzzling with this and can't get a clear solution for that.

       

      So far i got this tables:

      Student

      Proffesion

      Course

      Institution (this one has 3 TO: Organiser, Place, Dormitory)

      Class

       

      Relationships look like this:

      Student >– Class

      id_Class = id

       

      Student >– Course

      id_Course = id

       

      Student >– Profession

      id_Profession = id

       

      Course >– Organiser

      id_Institution_Organiser = id

       

      Course >– Place

      id_Institution_Place = id

       

      Course >– Dormitory

      id_Institution_Dormitory = id

       

      Course >– Profession

      id_Profession = id

       

      id_Course is filled with script that evaluates id of proper course based on profession and class with Case () function.

       

      After puzzling i came to something like this:

       

      Student –> SignUp <– Course

       

      In Students table:

      id

      and more information

       

      In SignUp:

      id

      id_Student

      id_Course

      ifAccomodated (Yes/No field – yes if Student was accommodated in dormitory)

       

      In Course:

      id

      id_Profession

      grade (I, II, or III)

      schoolYear

      startDate

      endDate

      id_Organiser

      id_Place

      id_Dormitory

      courseCostPerStudent

      dormPricePerStudent

       

      Is it a good approach? I would like to assure before I will start thinking how to populate this tables.

        • 1. Re: Relationship problem
          philipHPG

          It looks like a good start, especially with the SignUp join table.

           

          A few questions and comments:

           

          1). I don't see where Class fits into things

          2). In your list of relationships you identify that Student is related directly to Course, however later on you discuss the SignUp join table where Student and Course is related through the SignUp table. I'm assuming that is in place of the direct relationship.

          3). You might want to think about abstracting out some of the details in Course. Some of the details will change from year-to-year (dates, costs, and perhaps some of the other fields), but other pieces will likely be consistent over the years (such as the Profession and Grade). Abstracting it out will allow you to identify students who have taken the same course even if they were offered in different years/places/costs, etc.

          • 2. Re: Relationship problem
            macc

            philipHPG thank you, for answering. I will try to explain it more clearly.

             

            1) Every student is assigned to class. Class determines to which course student should be assigned.

            2) Direct relationship between Student and Course is current solution – to work only on current school year.

            SignUp table is the proposition of change to keep track of courses.

            3) I was thinking about leaving in Course table only these fields: id, id_Profession, grade.

            If it is the good way then I will need another table, like: CourseInYear, and put there:

            id

            id_Course

            schoolYear

            startDate

            endDate

            id_Organiser

            id_Place

            id_Dormitory

            courseCostPerStudent

            dormCostPerStudent

             

            Then relationship should look like this?

            Student –< SignUp >– CourseInYear >– Course

            • 3. Re: Relationship problem
              philipHPG

              1). From your description I got the impression that Profession and Grade determined which courses a student would take, but perhaps there is another level of complexity that hasn't come out in the description. It's not necessarily wrong to have a Class table, I just didn't see it described and so wanted to query it as a separate table.

               

              2) Okay, yes, I think it makes sense to have the SignUp join table

               

              3). Yes, I think that looks good.

               

              As you build you may discover additional levels of complexity, but I think this is a good starting point that should give you a fair amount of flexibility for the future.

              1 of 1 people found this helpful
              • 4. Re: Relationship problem
                macc

                1) Profession and Grade determines which courses a Student should take – that is correct. But Class is needed to determine Grade. In current solution I have a calculation with Case() function that is evaluating of id_Course for Student based on Class::name in which Student attends and profession he is learning. I used auto-enter calc (replacing existing value) that changed id_Course if name of class was changing.

                 

                Class table has following fields:

                id

                name (one of:Ia, Ib, Ic, IIa, IIb, IIc, IIIa, IIIb, IIIc)

                classMasterName (first and last name of caretaker of group)

                classroom

                 

                id of Class was fixed for a Student. After one school year Students promotes to another grade and Class::name is changed (i.e. from "Ia" to "IIa"). After promoting he had to be assigned to another grade of course. Students from classes: Ia, Ib, Ic are assigned to first grade; IIa, IIb, IIc to second grade and IIIa, IIIb, IIIc to third grade.

                 

                Then, Class is connected to one of two schools, as solution is working for two schools.

                I didn't mentioned about many things as I wanted to show problem as clearly as I can .

                 

                Is it look more clear or have I blurred problem even further?

                 

                2)Before I will start asking further question how to automate assignments, do you propose first to establish tables and relationship?

                 

                Once again I want to thank you, as so far I was struggling with this all alone.

                • 5. Re: Relationship problem
                  philipHPG

                  Thanks for the additional explanation on Class - I thought it might be something along those lines, but just wanted to be sure.

                   

                  It sounds like things are coming together well.

                  • 6. Re: Relationship problem
                    b.art

                    Hello,

                     

                     

                    Since I'm programming in FM I was lead by the book (and the idea) that joint tables etc need to be build and a whole bench of relations

                    need to be created that refer to each other. You obtain a spider net of relations and it is sometimes difficult to understand after a while

                    but for FM it is OK. Think always how another programmer could rebuild on your solution.

                     

                     

                    Therefore, there is a in my opinion a much better way.

                    You should work with the "Anchor-Buoy method". Its far more understanding, and far more memory-efficient.

                    There is an excellent course available on Udemy made by Richard Carlton.

                     

                     

                    Instead of thinking about tables, think about "Table Occurrences" (TO). And all the TO of a table refer to the same table.

                    As your layouts/forms will be based on one TO and you eventually need some other information that is stored in other tables you create a relationship between that TO and the tables that provide this needed information. And only those relations need to be created.

                     

                     

                    for instance :

                    you can create the tables you mentioned above but there is one major table that does not occur in your list.

                    You need to create a table where all individual "followed courses" are stored.

                    Then you create on the relation graph a TO "followed courses" that becomes your anchor and attach all your needed buoys on it :

                    those buoys can be :

                      - a TO of all the available students (eventually filtered - active/non-active (for instance if you which that the graded students are

                        ommited of the list, etc..)

                      - a TO of all the available courses (eventually filtered - maybe some are not available that year)

                      - a TO of all the available grades

                      ... etc.

                      

                     

                     

                    In fact tend to create from each TO only one level of relationship to other tabel occurencies that will provide you the needed information.

                     

                     

                    I hope you understand my point.

                    • 7. Re: Relationship problem
                      beverly

                      check this thread for a discussion on relationship graphing:

                       

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Relationship problem
                        macc

                        Hi,

                         

                        I am leaning toward the Anchor-Buoy method, I have read about basics but I wasn't able to find full explanation of method that could be obtained for free. From some time ago I create every new layout based on new TOs. Web was growing too fast – so I had to separate some TOs:). I know that FM works on TOs and therefore relationship is established between TOs, not tables themselves.

                         

                        Couldn't SignUp TO be a anchor? So far I don't understand what your suggested TO named "followed courses" should contain.

                        • 9. Re: Relationship problem
                          macc

                          beverly,

                          thank you for showing me this chunk of information.

                          My current RG is closer to this Post A/B way than to A/B – what I was missing in my solution was lack of consistency in TOs naming. And now you have given my guidelines. Thanks, that was really helpful!

                          Now it's job for me to implement those changes .

                           

                          Let me ask you one thing – can you suggest some good naming convention for FM?

                          So far I was closest to naming convention published on filemakerstandards.org but was struggling with some advices there.

                          • 10. Re: Relationship problem
                            beverly

                            I too struggle with the advice there, sometimes! Overall not too bad, but some name just plain breaks when you need the "name" somewhere other than FileMaker (Web, SQL, Excel, etc.)

                             

                            There are several threads on this forum about naming conventions. Choose what makes sense to you, don't be to obscure or overly abbreviated, be consistent, use the text feature of the graph to make yourself notes and "group" TO's, use coloring (on the RG) to help.

                             

                            beverly

                            • 11. Re: Relationship problem
                              b.art

                              Hello there,

                               

                              As an answer to your structure Name issue I again refer to the excellent online course I followed recently (presented by M. Richard Carlton). As mentioned before the forms are based on the TO, and relations are designed towards the needed "side - information tables"

                              In that course M. Carlton explained a good strategy for naming the related TO's (see underlying graph)

                              He uses the pipe symbol as a separator between that name and the related field

                               

                              Hope this can also be helpful for you.

                               

                               

                              IMG_0345.PNG

                              • 12. Re: Relationship problem
                                beverly

                                Check FM's naming suggestions (fields, but may apply to any object):

                                 

                                While the "|" is not mentioned in the list of  'do not use', I work with data where the pipe is a good delimiter (in data exchange) because it's rarely used. I don't know how Excel or Web or ODBC handles this character when it's not the 'data', so I avoid it's use in names.

                                 

                                note the warning:

                                If you're exchanging data with another application, ...

                                 

                                if you ever need to communicate with the rest of the world (& I do!), then the use of only alphanumerics (A-Z, a-z, & 0-9) along with the underscore ("_") is preferred. I will use spaces sometimes, but sparingly.

                                 

                                beverly

                                1 of 1 people found this helpful