7 Replies Latest reply on Oct 6, 2015 7:01 AM by michelefish

    Tracking Over Time


      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.

        • 1. Re: Tracking Over Time

          Good Morning,

          I have had some time to think this over and I have deleted my first couple of posts, I apologize. I wanted to make my thoughts more concise and not like a book to read.


          First a few questions:

          • What kind of reports do you need from the data?
          • What type of information do you need to keep on individuals?
          • Do you collect the same information for all of the different roles? If not what is different?
          • Do you want to collect information on individual classes per semester?
          • Does the learners mentor change from semester to semester?
          • Can a learner have a mentor assigned in many different ways? IE they have one that they will always have plus an individual for each class?



          I made some assumptions to make the graph below:

          • You keep pretty much the same data on everyone, but have need for some additional information on certain types of people.
          • You want to track individual mentors per class per term.
          • You want each learner to have an overall mentor as well.


          To that end I was able to come up with the graphs below, but I could be completely off base on all of these. I can only see 2 many to many relationships that would fall into those assumptions, but there could be more depending.


          With the following real tables:


          • 2. Re: Tracking Over Time

            Good morning! Thank you for your offer to help.


            Much of the data for adult learners and students will be different. In addition, they will be completing different weekly surveys with the data being collected via the web. However, supervisor and stakeholder information will be quite similar.


            The data analysis would look at trends, such as the number of adult learners by unit or supervisor or area of study; the number of students by year in college, major or faculty advisor. Nothing very complex, at least not at this point.

            • 3. Re: Tracking Over Time

              michelefish wrote:


              Much of the data for adult learners and students will be different. In addition, they will be completing different weekly surveys with the data being collected via the web. However, supervisor and stakeholder information will be quite similar.


              With the information listed above, I just want to make sure I have this correct before drawing stuff up. Besides the survey, what type of different information is collected on each type of person? Does every different classification have a survey?


              Will each adult learner be assigned a unit, supervisor, and area of study?


              On top of adult learners, will you want to track each individual student from year to year? If yes, will you also want to track their major and faculty adviser from year to year as well?


              What is the role of the supervisor verses the stakeholder?


              I promise I'm not trying to be a burden with these questions, I just want to make sure I have a good picture so I can come up with a good data model to start with.

              • 4. Re: Tracking Over Time

                Adult learners have supervisors, their work unit, and areas of study, all of which can change over time.

                Adult learners are assigned to one or more students or stakeholders as learning partners, which can change over time, e.g., if they continue for more than one semester.


                Students have majors, faculty advisors, year/term in college, course taken (2 possible courses, one follows the other), and assigned adult learner(s).

                Students may become learning partners as stakeholder later.

                Adult learners complete weekly surveys.

                Learning partners complete weekly surveys.


                Stakeholders may be volunteer learning partners (not students taking the 2 courses), but most are contacts that the program wants to maintain information on.


                I'd say that a supervisor really is a stakeholder with a supervisor designation. However, for ease of data import of all possible supervisors (but not for other stakeholders), I'm thinking a separate table may be helpful.


                I want to have Learning Partners as a main table but my client wants them separated into Students and Stakeholder.

                • 5. Re: Tracking Over Time

                  I agree with you that having Partners as a main table is the best way to go. Have you asked the client if using a flag to separate students from volunteers would be acceptable? It would still give them a clear division of data, but would also allow them to easily transition from one role to the other.


                  It adds a lot of unneeded complexity to the database to store students and volunteers separately.


                  I would also try and keep supervisors in that file as well. I would write an import script that would flag them as a supervisor as the records are imported, rather than having another table to the solution. I'll model my thoughts up here and post them so you can see what I'm thinking.  (I explain much better with pictures than words)

                  • 6. Re: Tracking Over Time

                    Please bear in mind that this is a really rough table guide I've spent maybe 10-15 minutes on:


                    The way the learners are setup in this chart should give you the ability to have a history for any of the groupings they are a part of. I also included end dates and an active field in all of the relational tables to allow for reports and sorting.


                    I am not 100% on the partners for classes. If you moved students to its own table, then you will have to somehow copy that data from when they go from being a student to just a volunteer. Hence the preference to keep them in the same table. I wasn't sure how you wanted to link faculty so I left it out, but if it is by the class they are in, then I would just list them in the classes table.


                    The survey is really a quick brush with not much thought into it. I just wanted to list it on the chart. I would have to put a lot more thought into how I would handle those than what I currently have time to do. using id_related users you can tie it back to a learner or a partner.


                    Tables Page:


                    I hope this helps, and if anyone else has some ideas I would love to hear them as well. I'm always up for growing and being a better programmer.

                    • 7. Re: Tracking Over Time

                      Thank you. This gives me a start. I won't be working on this for a day or two, but do appreciate your help!