9 Replies Latest reply on Oct 7, 2009 3:20 AM by comment_1

    School Database - Class versus Individual: HELP!



      School Database - Class versus Individual: HELP!



      Before you read this: This post is backwards, start at the bottom.  For some reason, when I press the return key then it goes to the line above, not below (only in this forum). 


      Concern: How can I  implement this? I tried putting in a class table, but if I change the relationship from Student-Lesson to Class-Lesson then I mess up 6 months of data.


      Main Question: How can I build something like this? Data Tunnel? Script? Anchor-Buoy relationships?  I really have no idea.  I have not yet used anything beyond direct relationships and have not tried scripts or calculations. Can someone point me in the right direction?


      Desired Design: I need a layout which is for the Class.  The lesson table is related so when I record a lesson it goes into each individual file.  Then there would be something like a portal on the lesson sheet with the student names where I could record if they attended or not and I could give them a score of 1 to 5 on their performance.  In the class layout I would also need a portal listing who are class members and when they enrolled.


      Problem: To input lesson data for a class of 6 students I have to use Duplicate Record 6 times. Wouldn't it be more efficient if lessons were related to class groups? I made a class table but if I link Class to Lesson then every time all students are recorded, but what if someone is sick that day? Also, how can I input individual performance data?


      Current Database: three tables - Student information table, lesson record table and fee payment table. Student table is related to Lesson table, payment table is related to student table.  Student table contains bio-data on students and also enrolment and proficiency information.  It also has 2 portals showing lessons the student has done and payments the student has made.


      Experience: about 6 months 

      Version: FileMaker Pro 10  

      OS: Mac Leopard

        • 1. Re: School Database - Class versus Individual: HELP!
             I was told this community were great for support, yet 33 people have read my post and nobody has offered any advice.  Sad. 
          • 2. Re: School Database - Class versus Individual: HELP!

            First, I dont know about others, but I had a hard time following your post. Second, reading it backwards did not help either. If you are having problems with the forum for some reason, try typing it out in a text editor first and then copy paste.


            You mention class, lessons, and talk briefly by attendance but you dont mention your business rules. How do these entities relate?



            From first glance it seems as though you would want: students, enrollment, class, lesson, attendance, payment.

            • 3. Re: School Database - Class versus Individual: HELP!

              Mr Vodka,

              what do you mean by business rules?

              Anyway, I have 3 main tables and now have made 2 extra ones relating to finances.

              1. Student Table. This has records with data on students such as name, contact details, enrollment date, level, method of payment, etc. This table has 2 portals, one listing the topic and date of lessons the student has taken, the other draws data from the payments table to list the date and amounts the student has paid.

              2. Lesson Table. This has records detailing individual lessons. For example, today I teach a class of 4 kids, in the record I record the date, topic, materials used, and give a score of 1 to 5 for performance and participation. I use the Lesson ID autofield to draw in data from the student table regarding the students level, type of lesson they take, and what class name they are in. If their are 4 students then I would duplicate the record 3 times, changing the name of the student each time to get each students data into the record.

              3. Payment Table. This records each time a student pays fees. It takes data on the student name and kind of lesson, and level of the student from the Student Table, and then I only have to enter the amount they pay and how they pay.

              4. Resource Table. This is a table of resources I use, such as flashcards, books, software and audiovisual. They Lesson Table has a field, Resource ID, by which it gets the name of an item used in the lesson from a dropdown menu.

              5. Expenses Table. This is a table recording expenses including fields for category, description, amount, notes, payment method, etc. It is now an unrelated field but in the future when I work out calculations I want to make another table for bookkeeping which would draw upon data from this table and the Payments table to give me financial reports.

              So that is what I am using now.

              What I was thinking about was how I could make a class table and link it to the student table and lesson table. Then I would only need one entry which would record the lesson to all the student individual files instead of having to duplicate. It would need something like a portal to show the names of the students in the class, and some way of recording who was absent or present.

              I do not know how to make this work in a way in which I could also have individual data on student performance in the lesson but at the same time being able to record lessons as a class.

              Hope this is a little clearer.

              • 4. Re: School Database - Class versus Individual: HELP!
                   sorry, in the Lesson Table section I said Lesson ID field, but I meant Student ID field.
                • 5. Re: School Database - Class versus Individual: HELP!

                  What I mean by business rules is what you define as a class, lesson, etc. For example, a class can consist of many lessons, or in other cases a lesson can consist of many classes. Its all perspective.


                  As I mentioned earlier, while taking a guess I am assuming that you have "courses" that each have a bunch of lessons. A student enrolls in a class but can attend all, some, or no lessons if they want to. Am I correct here?


                  If so, you can have a portal that displays all the students for each course. With each new lesson ( which is a child record for the overall course ) you can choose to quickly take attendance or rate them.


                  See if this demo can help you. It is similar as it uses a custom checklist for each type of housing inspection.



                  • 6. Re: School Database - Class versus Individual: HELP!
                       A class for me is a group of students  who take lessons.  A lesson is one 50 minute long learning session.  My dilemna is I attached each lesson to one student.  One student takes many lessons. Then in the student record there is a portal showing all lessons the student had taken.  I was looking for another way of doing it where students are related to the class group they are in. One group to many students.  Then that one class would also be related to lessons. So on Tuesday at 4:30 for example class A2 students took a lesson.  The topic was fruit.  I put class A2 as the object of the Tuesday 4:30 lesson and then there would be a record of that class having done that lesson.  Then I would want the students who are members of the class group to have that lesson's data in a portal on their student file, but if they are all listed as members then they would all automatically be linked to the lesson when in fact some of them may have been absent at that lesson. 
                    • 7. Re: School Database - Class versus Individual: HELP!

                      abrazor wrote:
                      I was looking for another way of doing it where students are related to the class group they are in. One group to many students. 

                      That sounds more reasonable, except in most schools a student can take several classes (i.e. courses), so there would be a join table of Enrollments between Students and Courses. The individual lessons would be a child of Courses, and Attendance a join between Students and Lessons.

                      • 8. Re: School Database - Class versus Individual: HELP!
                           Thank you for the comment.  However, I am running an English school in Japan so there is not COURSES, everyone is studying the same thing.  There is one curriculum and students are grouped in the school according to level. Students take lessons usually at the same time each week, once a week.  The main purpose of my database is to store contact data on the students, record LESSONS they take, link the lessons with another table which lists materials used in lessons, and also to record when and how much they pay for lessons through a PAYMENTS table. I am sorry, I am not so good at this kind of thing, can you tell me what you mean by CHILD OF? I think of tables as being ONE-TO-ONE  or ONE-TO-MANY. For example, the relationship now between LESSONS and STUDENTS is One Student TO Many Lessons. I guess most people using this program have a better understanding of the technical side than me.
                        • 9. Re: School Database - Class versus Individual: HELP!

                          abrazor wrote:
                          I think of tables as being ONE-TO-ONE  or ONE-TO-MANY.

                          Well, there is also MANY-TO-MANY - though in practical implementation it's best to break it up into two ONE-TO-MANY. The terms parent and child are used to to describe a ONE-TO-MANY relationship: one parent has many children.


                          The relationship between Students and Lessons is a good example of a MANY-TO-MANY relationship: each lesson has many students, and each student attends many lessons. That's why you need an Attendance table in-between them - such table being a child of two parents, so to speak, or more commonly a join table.