I was told this community were great for support, yet 33 people have read my post and nobody has offered any advice. Sad.
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.
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.
sorry, in the Lesson Table section I said Lesson ID field, but I meant Student ID field.
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.
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.
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.
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.
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.