Timetabling of ESOL Classes - Second Attempt

[Alan L]

Hi

I’m trying to help my wife who is a teacher of English as Second Language (ESOL) teacher.
After initial English language testing, she places her students in ESOL Groups (Group 1, Group 2 …) of between 1-5 members, depending on their level of English.
She then produces an ESOL timetable to bring her student out of their Homeroom classes in their ESOL Group (Group 1, Group 2 …) for ESOL lessons. Students can be placed in up to five sessions (1^st Session, 2^nd Session…), depending on their level of English. This is done on a large hardcopy of the weekly timetable with lots and lots of pieces of paper with student’s names on them.

Problem:
I’ve set up the attached spreadsheet to show the different layers of information that my wife needs to consider when developing the timetable for her ESOL Groups.



Tab 1: ESOL Student Data (ID, First Name, Last Name, Preferred Name, Agent, Date of Birth, Gender, Year Level, Homeroom Number, Homeroom Teacher, House)
Tab 2: ESOL Group Data (Group 1, Group 2, Group 3 …). Students can be in up to five different sessions.
Tab 3: Timetable Template (Timetable Blocks: A1a … E3)
Tab 4: House & Tech Timetable (Timetable Block, House)
Tab 5: PE Data (Timetable Block, Students)
Tab 6: PE Timetable
Tab 7: Academy Data (Timetable Block, Academy, Students)
Tab 8: Academy Timetable - Classes
Tab 9: Academy Timetable – Students
Tab 10: Specials Timetable – Classes (Assembly, Meeting)
Tab 11: Timetable Solution



Key points she needs to consider:

1. Regular classes operate on a timetable of four blocks per day, Monday to Friday (A1a to E3). See Tab 3.
2. Blocks from Monday to Thursday are assigned to each of six Houses; Batten, Cooper, Hillary, Ngata, Sheppard and Upton. See Tab 4.
3. Students attend their Technology (Tech) classes with their Homeroom Class as a House. See Tab 1 and Tab 4.
4. Students must attend Physical Education with their homeroom class. See Tab 5 and Tab 6.
5. Some ESOL students get accepted into one of the following Academies (Year 7 History, Year 8 History, Year 7 Science, Engineering, Robotics, Year 7 Writing, Year 8 Writing, Year 7 Mathematics, Year 8 Science, Sports, ICT, Philosophy, Technology, Mathematics, Kapa Haka), which operate on Friday during certain blocks (E1a, E1b, E2 and E3).
6. All students attend Assembly Block 1 (A1a) each Monday. See Tab 10.
7. My wife needs two free Blocks with no ESOL Groups for her planning. This is put in after the timetable is set.
8. My wife does not work during Block 4 (E3) on Friday.
9. Therefore, the total number of blocks that can be used for ESOL groups is 15 (20 – 5 (for Assembly, Meeting, Planning (x2) and Not Available)).
10. I’ve produced a Timetable Solution (see Tab 11) of what I want the outcome to look like.

Solution:

I want to help my wife by producing a programme using Access database that will enable her to enter the appropriate data (Student’s Name, Reading Group(s), House/Technology Block, Physical Education classes, Academies and Special Blocks) into a database Form and it would provide possibilities of Block combinations that work for all her students.
I have very little knowledge of databases and so have been watching lots of You Tube clips build up my basic level of knowledge and skills.

I have set up the Tables shown in the Relationship diagram attached.

Can someone please help me?
I will need help in developing appropriate links between the tables so that I can produce a query of the timetable solution.

[ranman256]

Your Excel designs must alter slightly for use in a database. You want atomic structures.
your GroupsData table design should be :
StudentID,Session#, Group

your excel layouts look good for humans, and can be reproduced from reports, but the db data is stored differently.
(see the basic 'school' relationship I use below. Alter at will)

[orange]

Alan L,

Further to ranman's advice, here is a link to some free,generic data models from Bary Williams.
See the list of models under section 31- Students.

These are generic and meet the general requirements of typical "business"/applications.
You can adapt (add, delete,ignore parts that don't apply to you) these models or parts thereof or use them to confirm your design.

You may also find this link to info on database planning and design useful. There are several articles/videos in different formats.

Good luck with your project.

[Alan L]

Thank you Ranman256. I have spent the day learning about relational database schema and atomic values as you mentioned.

I see that my next step involves producing a relationship diagram similar to the one you've provided me.

Thank you.

[Alan L]

Thank you Orange. I have spent the day learning about relational database schema and atomic values.

I've been to the website you provided the link for and accessed the models in section 31, as well as the video tutorials.

I'm currently going through them and finding elements that are similar to what I'm trying to do.

I see that my next step involves producing a relationship diagram.

Thank you.