Figure 1 - Classroom Scheduling Schema
The download of the Radicore framework includes several prototype applications which demonstrate the kind of application which the framework is designed to support. One of these is the CLASSROOM SCHEDULING application, and the purpose of this article is to provide a description of that application so that you can see what it does as well as how it does it. This may prove useful in the event that you should ever want to build your own version of the prototype.
This is the database schema used by this application showing all the entities (tables) and the relationships between them.
Figure 1 - Classroom Scheduling Schema
A subject is a very broad category and may be broken down into a large number of different lessons. A lesson is the only unit which can be scheduled, so "Mathematics, Class 1" is a different lesson to "Mathematics, Class 2". Each lesson is given by a single teacher, so "Mathematics by teacher A" is a different lesson to "Mathematics by Teacher B".
| subject_id | This is the unique identifier for this entry. |
| subject_name | This is the description for this entry. |
Figure 2 - Subjects
| lesson_id | This is a unique identifier which is generated by the system. |
| lesson_name | This is the description for this entry. |
| lesson_short_name | This is a short description which will appear in all schedule screens. |
| year | This is used to restrict the lesson to students of the same year. For example "Maths for year 1" cannot be taken by a student in year 3. |
| teacher_id | This is a foreign key to the TEACHER table. |
| lesson_notes | Some free-format notes about the lesson. |
| subject_id | This is a foreign key to the SUBJECT table. |
Figure 3 - Lessons
A teacher is the person who gives a lesson. A teacher can give many lessons, but each lesson has only one teacher.
| teacher_id | This is a unique identifier which is generated by the system. |
| first_name | This is the teacher's first name. |
| last_name | This is the teacher's last name |
| initials | These are the teacher's initials. |
| title | This is the teacher's title. |
| teacher_notes | These are free-format notes about the teacher. |
Figure 4 - Teachers
A room is the place where a lesson takes place. A room may be allocated to a class so that the class stays in that room for all its lessons, while other rooms may be purpose-built for particular subjects, such as a chemistry laboratory or gymnasium.
A room can only be used for one lesson at a time, so the scheduling system must disallow double booking. A room may also have a maximum capacity, so the scheduling system must prevent this capacity from being exceeded.
| room_id | This is the unique identifier for this entry. |
| room_desc | This is the description for this entry. |
| room_capacity | This is the maximum number of students who can occupy this room. |
| room_notes | These are free-format notes about the room. |
Figure 5 - Rooms
A student is an individual while a class is a group of students who share the same schedule. Junior students (i.e. those in the 1st to 5th years) may always belong in a class while senior students (i.e. those in the 6th year) who have fewer lessons in more specialised subjects may have their lessons scheduled individually.
| class_id | This is the unique identifier for this entry. |
| class_name | This is the description for this entry. |
| year | This gives an indication of the experience and abilities of the class. Juniors start at year 1 and become more senior with each passing year.
When choosing a lesson which is to be taken by the class, only those lessons which are for the same year as the class will be made available in the popup screen. |
| class_notes | These are free-format notes about the class. |
Figure 6 - Classes
| class_id | This is a foreign key to the CLASS table. |
| lesson_id | This is a foreign key to the LESSON table. |
This table links a class to a lesson. All the students within that class are therefore linked to that lesson.
Figure 7 - Class-Lesson
| student_id | This is a unique identifier which is generated by the system. |
| first_name | This is the student's first name. |
| last_name | This is the student's last name |
| initials | These are the student's initials. |
| year | This is only used if CLASS_ID is not specified. It gives an indication of the experience and abilities of the student. Juniors start at year 1 and become more senior with each passing year.
When choosing a lesson which is to be taken by the student, only those lessons which are for the same year as the student will be made available in the popup screen. A student who is assigned to a class cannot be assigned to lessons as an individual. |
| class_id | This is only used if YEAR is not specified. This is a foreign key to the CLASS table. |
| student_notes | These are free-format notes about the student. |
Figure 8 - Students
| student_id | This is a foreign key to the STUDENT table. |
| lesson_id | This is a foreign key to the LESSON table. |
This table links an individual student to a lesson. Notice that it is possible to link a student to a lesson which is also taken by a class.
Figure 9 - Student-Lesson
Some systems attempt to create schedules "automagically", but as this would be complicated and CPU-intensive operation which could never be guaranteed to produce perfect results first time every time this prototype uses a manual approach. This involves choosing a room, then allocating lessons one at a time to a particular day of week and to a particular start and end time. The system will check that the latest addition does not conflict with any previous scheduled events and will set the IS_VALID flag accordingly. Only valid scheduled events, those without any conflicts, will appear in any schedule screens. Invalid events will have the details of all conflicts recorded in the SCHEDULE_ERRORS field.
To speed up the process of checking for conflicts this prototype takes all the raw data and builds a database table containing all the possible conflicts. The validation of a scheduled event can therefore perform a few simple lookups on this table instead of trawling through the raw data. The "Update" button on the List Conflicts screen is used to erase and rebuild the current list of conflicts. Note that this function must be used AFTER all rooms, teachers, lessons, classes and students have been input or amended and BEFORE any attempt is made to schedule any lessons. Also note that as this function is required to read and process virtually every record in the database it will not be quick.
Each record in the CONFLICT table identifies two LESSONS which cannot be run simultaneously for one of the following reasons:
| conflict_id | This is a unique identifier which is generated by the system. |
| conflict_type |
|
| teacher_id | This is a foreign key to the TEACHER table. |
| student_id | This is a foreign key to the STUDENT table. |
| class_id | This is a foreign key to the CLASS table. |
| lesson_id1 | This is a foreign key to the LESSON table. |
| lesson_id2 | This is a foreign key to the LESSON table. |
Figure 10 - Scheduling Conflicts
It is more than likely that while one set of schedules is in general use the administrator will want to create a new set of schedules for the following year. This is achieved by having multiple schedule header records (each with its own schedule_id) and having one of them marked as "active" for general use. By default all users will be restricted to those schedules which were created under this schedule_id, but a user who has access to the Choose Schedule Id screen can select a different schedule_id for use during the current session. In this way an administrator can work on a new set of schedules without affecting any of the existing schedules.
| schedule_id | This is a unique identifier which is generated by the system. |
| schedule_desc | This is a description for this entry. |
| schedule_notes | These are free-format notes about the schedule header. |
| is_active | This indicates if this schedule is currently active or not. |
| start_time | This is the start time of each day's schedule. This must specify minutes of '00', '15', '30' or '45'. |
| end_time | This is the end time of each day's schedule. This must specify minutes of '00', '15', '30' or '45'. |
| last_day_no | Each week starts on a Monday (day 1) and ends on another day, such as Friday (day 5), Saturday (day 6) or Sunday (day 7). The required ending day is set here. |
Figure 11 - Schedule Header
Figure 12 - Choose Schedule Id
This is the table which holds all the scheduled events - an occurrence of LESSON which is linked to a ROOM on a certain day between certain times. As each event is entered it is checked for validity. If it is valid it will appear on the various schedule screens. If it is not valid a list of errors will appear in the schedule_errors column.
| schedule_id | This is a foreign key to the SCHEDULE_HDR table. |
| seq_no | This is a unique identity which is generated by the system. |
| room_id | This is a foreign key to the ROOM table. |
| day_no |
|
| start_time | The start time for this scheduled event. |
| end_time | The end time for this scheduled event. |
| lesson_id | This is a foreign key to the LESSON table.. |
| is_valid | If there are any conflicts this is set to 'NO'. |
| schedule_errors | This contains a list of all conflicts with other scheduled events. |
To create schedule events the user starts by selecting a ROOM on the List Room screen, then presses the "Maintain Schedule" button to bring up the Maintain Schedule Events screen. The user then selects the relevant day using the scrolling links to show what lessons have been scheduled on that day. New events can be added by pressing the "New" button which activates a variation of the Update Schedule Event screen which allows the user to select a LESSON and a start and end time. If the event fails its validation it will still be added to the database, but will be marked as invalid so that it will not appear in any schedule screens. There may be numerous reasons why the validation failed, s all of these reasons will be shown in the schedule_errors column.
The following errors will prevent a record from being inserted or updated:
The following errors will allow a record to be inserted or updated, but will set IS_VALID to 'NO':
Figure 13 - Maintain Schedule Events
Figure 14 - Update Schedule Event
Figure 15 - View Schedule/Timetable
Several variations of this screen are available by selecting different parent entities: