5 Replies Latest reply on Apr 6, 2012 6:35 PM by jbrown

    Student Information from the Past

    jbrown

      Hey all,

      I've decided to post a lot of questions on here. Here's one.

       

      At my school we are using a database to store information from previous years. Each kid for the current year has a schedule and a grade. I'm a 5th grade teacher, so all my kids have the grade 5 label attached. Next year, I'll move the kids to 6th grade, and so the 5 will be replaced with a 6.

      As I said, the database stores info from the past. It stores last year's grades, when the kids were 4th graders. All of the Academic data are in appropriate tables and those tables are related to the student table by the student ID and year, so I can have many years worth of one student's grades in one table. (I think this is the proper way to store information).

       

      My problem is that when I print a report card from LAST year, when my kids were 4th graders, their report card says 5th grade (current year). I don't want that. I want the grade that the kid was in when he/she earned those grades.

       

      So what should I do? Currently the grade field is in the student table. But now that I'm storing multiple years worth of data, it doesn't make sense, i think to keep the grade field in the student table where it is changed every year.

       

      Should I create a new "StudentGrade" table with three fields: __pk_StudentID ; AcademicYear; GradeLevel

       

      Or should I create a new field in the academic info table and record the grade level that they earned the particular score? Is one better? Is one more efficient?

       

      Just a note, I do have multiple academic info tables (State Test scores, local benchmark test scores, etc) storing multiple year's with of data for each kid.

       

      I'm thinking i should create a new table and put in the kid's academic year and grade level, as my first option shows.

      Am I thinking correct?

      thanks

      jb

        • 1. Re: Student Information from the Past
          beverly

          Either of these.

           

          If you don't have to create yet another relationship to pull the data, sometimes that's ok, so the second method works. You can just find (or even use as a filter in a portal) to show the student grade level and scores.

           

          But purist will say make another table...

           

          Beverly

           

          On 06 Apr 2012, at 6:39 PM, Jeremy Brown asked in whole or in part:

           

          Should I create a new "StudentGrade" table with three fields:  __pk_StudentID ; AcademicYear; GradeLevel

           

          Or should I create a new field in the academic info table and record the grade level that they earned the particular score?  Is one better? Is one more efficient?

           

          • 2. Re: Student Information from the Past
            karendweaver

            Hi Jeremy

             

            Beverly is right, you can do it either way, but think this through very carefully.  I can think of a scenario in which a student may be in more than one "grade level" in one "academic" year.  I have even known of schools where a student may be in different grade levels for different subjects.  4th grade Math and 5th grade English for example, even where the subject does not match the students' "grade".

             

            The question I always ask my clients is - what is "usual" what is "rare" what is "possible" and what is "never" and then I design the database for "rare" or even "possible".  Even if these scenarios do not occur in your school now - are they possible in the future?  And if they are possible, how much of a pain will it be to change your design to accomodate them?

             

            I would personally lean toward storing the grade AND academic year in the Grade table and then use a report or portal filter to show the appropriate results - NOT a join table, since academic year and grade level may not be enough of a filter for what you need..

             

            Karen

            • 3. Re: Student Information from the Past
              jbrown

              I'm considering doing it this way. I understand the purist's point of view, and i'm trying to be pure.Since I do have a lot of other academic data, i feel if i create a join table with student's grades in there by year, i will not have to rewrite the grade in each table for each record.

              At my school, there's never a time when a kid would be in 2 separate grades. We do have a few kids in 8th grade doing 9th grade math, but as of now, that' considered their 'math class' regardless of what grade it is in. Karen, so your consieration wouldn't happen, ever. In the 5 years I've been there, we've never had this situation. For you, Karen, does that get rid of this consideration?

               

              Speaking of join tables. How many join tables should I use? Should I use one for multiple parent phone numbers? What's a general rule about join tables?  Is it just "if one kid could have multiple _________, then a join table is necessary?

               

              I have quite a few join tables now, developing them without complete confidence i'm doing it correct. The data comes up neatly whenever it is called by other teachers, but I feel like I'm missing something - that i'm doing too many join tables.

              As my quote on here says, I'm learning so much about that which I do now yet know.....

              • 4. Re: Student Information from the Past
                karendweaver

                My comments were just comments - just something for you to consider and discard if you think its unneeded.  Just some ideas to get you thinking about HOW to approach the problem - NOT a suggestion for how to solve it.  My recommendation is for you to creatively imagine every possible and some impossible scenarios for using the data and try to determine which ones could impact your solution. 

                 

                By the way - no matter how much I do that, I always, always, ALWAYS find out later that I missed some crazy thing that no one told me they needed that makes me revamp something in a completely different way.

                 

                Now, the "general rule" for join tables is that you might need to use them whenever you have a "many-to-many" relationship.  But it really depends on when and how you want to look at the data.

                 

                A good example would be students and courses.  One student can be in many courses.  One course can have many students.  So you would use a join table, say "registrations" to connect them, IF you wanted to report what courses a student was currently or past registered for, or IF you wanted to report which students were in a course. (Obviously, you probably do want to see those things in this example!!  But again,  it is a good exercise to think about WHY you need a join table, as opposed to simply doing a query/find to report data. 

                 

                Usually when I am doing a registration join, that table may also store the final grade or a pass/fail field for that student in that course.  Multiple grades for a course are usually related to the student through the course/registration table, so a separate join table for grade list is not needed.

                 

                I have never used a join table for phone numbers (yet).  Usually one person has many phone numbers, and it's possible for one phone number to have many users, but most people don't create a join table for that - just a related file to store multiple phone numbers for one person (one-to-many relationship - no join).  While there may be a couple people with the same phone number, it isn't like there are hundreds of people with the same phone number (or if there are, then many it's a "company" table that joins them together).  And it's unlikely that you would need to report or print a phone list using data in a join table, so it's an unnecessary complication.  On the other hand, if your project is publishing the white or yellow pages, then maybe... 

                 

                Even purists are never pure, Jeremy.  You need to design the best structure for the data and the uses you have.  While it is good to know what the "rules" are, it is better to understand when and when NOT to apply them in order to have the best performing, most usable database for your needs.

                 

                Hope that helps!

                 

                Karen

                • 5. Re: Student Information from the Past
                  jbrown

                  Karen,

                  You have some good advice. I realized that my use of the join table was wrong, as far as using it to describe what i'm doing.

                   

                  I've got a very decent system going, learned from 3 years of strife, reading, and just figuring it out. I'm looking to see if there are ways to optimize the solution.

                   

                  Thanks for the good ideas.

                  jb