4 Replies Latest reply on Jan 27, 2011 8:59 AM by philmodjunk

    Newbie - Relationship help for Academic setting

    JodieK

      Title

      Newbie - Relationship help for Academic setting

      Post

      Help! I'm trying to create a school database of families, students and classes for our school that will generate: a school directory listed by class, mailing labels for each class, and various rosters.  My problem is that several students are from split families, which means they have two addresses to be listed both in the directory and on mailing labels. I've been struggling for a way to design it so that  everything works. And of course most families have more than one child at the school. My tables so far are: 1) Students, with information particular to that child (grade, class, gender, allergies, etc.) 2) Families (fields include address(es), phones, both parent names and workplaces, emergency contact info, etc.. And a ClassRoster table with only the fields: teacher, and familyID. ANY suggestions on fields and relationships would help. I've been tearing my hair out, because as soon as I can get one relationship to work another fails. So far I have a studentFamiliyID which links the student to one or both family records which he is linked to (and these show up fine in a portal on an Enrollment Form), but I can't get this to generate two mailing labels for him for his class. AND I'm on a deadline to finish this.. I've looked at the sample Contact Management database, but nothing seems to have what I need, for cases when someone has TWO addresses but most people do not. 

        • 1. Re: Newbie - Relationship help for Academic setting
          davidanders
          Designing for worst case senario. Design for "John" and "Smith" OR "Mr" and "John" and "X." and "Smith" and "Jr" and "DDS" Design for one address or two addresses or multiple? Why do I NOT get full formatting options at random times????
          • 2. Re: Newbie - Relationship help for Academic setting
            Sorbsbuster

            "these show up fine in a portal on an Enrollment Form, but I can't get  this to generate two mailing labels for him for his class."

            I'll guess that you are trying to run the mailing labels from the table with the Students in it (or the Enrolments Table).  But if you think about it, if you have 20 student records (or enrolment records) the most you can get are 20 labels.  But if you run it from the Families Table, where those 20 students may have 25 families, then you can print up to 25 labels.

            If you want to print the Family Address Labels for Class 7A, don't look in the Students Table (or Enrollments) for '7A' - you will find the 10 students enrolled in that class.  You want to create links from the Families Table back to the Enrolment Table and pick up from there all the Families who have a student enrolled in 7A.  That will pick up the 12 families that are connected to the 10 students in the class.

            No?

            • 3. Re: Newbie - Relationship help for Academic setting
              philmodjunk

              Since students can be a member of more than one family and a family can have more than one student, you have a many to many relationship. Thus, a join table is in order to link a given student to one or more families and a given family to one or more students:

              Students----<Student_Family>----Families

              Students::StudentID = Student_Family::StudentID
              Families::FamilyID = Student_Family::FamilyID

              A portal to Student_Family on a students layout can include fields (such as an address) from Families to list all the Families to which that student belongs. A portal to Student_Family on a Families layout can include fields from students (Such as their names) to list all the students that are a member of that family. A layout based on Student_Family can be used for your address labels, with name fields added from Students and address fields added from Families.

              You'll likely find that this technique is useful in other parts of your system as well. You may need to assign students to multiple class periods, for example.

              • 4. Re: Newbie - Relationship help for Academic setting
                philmodjunk

                DavidAnders asked: Why do I NOT get full formatting options at random times????

                Next time this happens, click the HTML link to check the actual source text being created by your input. I've found that this site will at infrequent, random intervals, suddently start to insert massive quantities of spurious HTML tags. When this happens, your ability to edit and format your post gets messed up. You can use the HTML editor to delete these tags. I got tired of doing that, so I added a widget in the Forum Tools tab on my Known Bugs List database where I can paste the HTML from a messed up post and the auto-enter calculation strips most of the spurious tags so that I can then copy and paste the cleaned up HTML back into the HTML editor. (It's the field labeled "MCE Tag Stripper".)