Isara’s new management system

The Isara Foundation runs a learning centre in Nong Khai, Thailand. It’s known as the ILC. Here volunteers from around the world teach English, IT and Art to local people. There are classes for both children and adults, and within those bounds students are separated according to capability, which is judged using a reading system (e.g. phonics -> fables -> free-reading).

The current system

I arrived at the ILC at the beginning of December. I started off just watching and admiring the work of other volunteers but eventually had to take a class on for myself. The then current system of record-keeping and proficiency-recording was paper-based. There were folders for each class and within each folder, booklets for each student.

Each booklet recorded a single student’s reading level, their strengths, their weaknesses and any concerns previous teachers had. These booklets would inevitably become full of volunteers’ notes. So many volunteers… so many notes.

Many of the scribblings were not dated and not particularly tidy. Lesson plans were recorded on paper too, but only as a single bullet point per lesson.

Don’t let me paint a bad picture though. The work being done here is and was always fantastic! The typical volunteer would plan a lesson the day before and record the plan on their own stationary, whether that be a notebook or a scrap of paper. Despite my reservations about it being paper-based the lesson’s still got planned and the students still picked up valuable language skills.

The problem, as I saw it, was merely one of efficiency. The case for a computer-based alternative was, I felt, rock-solid. I begun.

The new system

So, for the last few weeks I’ve been working on a new web application for the Isara Foundation which holds student records, lesson plans, teacher profiles and makes all this data accessible through an intuitive interface. Current volunteers can leave their lesson plans, student notes and class notes on a centralised system. New volunteers come along and don’t need to worry about digging through masses of paper to get the 101 on their assigned class. They can see all the data they could possibly want before even stepping into the classroom:

The new system, which is already being used (I guess it’s beta), has the following features:

• Stores basic data about students. Their names, ages, addresses, etc.
• Stores photos for all students. Great for learning your students’ names!
• Stores teacher profiles and allows teachers to be assigned to classes and vice-versa.
• Allows teachers to leave notes on student profiles (individual strengths/weaknesses/comments) and class profiles (comments about class in general).
• Allows teachers to enter their lesson plans, and look over previous lesson plans.
• All students’ skill levels recorded, which will eventually enable us to draw some interesting statistics about teaching techniques and skill acquisition.
• Makes it possible to receive notifications about student birthdays! (my favourite feature)

In essence, it is a primitive system. Nothing too fancy is going on, but I definitely enjoyed building it!

As some of you will know, I used CodeIgniter for this project, simply for its insistence on MVC and the fact that I was familiar with its API.

I don’t regret my decision to use CI. It has worked perfectly.

Implementation notes

The database schema seemed simple at first, but then came normalisation — and with that, apparent complexity. Here’s the schema! (not including audit tables)

As one is supposed to, I created separate models for every table, except the auditing ones. I created MY_Model which took care of the auditing and added a few useful methods. All of my actual models inherited from this.

The models were originally used for creating, reading, updating and deleting, but I eventually moved over to a structure which delegated to regular models for creating, updating and deleting, but utilised a specialised query model for the reading. I did this because I originally found that I was duplicating a lot of code within models, and decided to abstract the whole querying business to its own model. This has worked remarkably well and has facilitated a much simpler querying syntax and interface.

Within Query_model I can specify what needs to be selected and from where within an associative array:

// ...
'classes' => array(
    'select' => "
        classes.*,
        class_slots.name as slot_name,
        classes.name as class_name,
        class_slots.days as class_days,
        class_slots.time as class_time,
        rooms.id as room_id,
        rooms.name as room_name,
        rooms.photo as room_photo,
        COUNT(DISTINCT students.id) as student_count,
        GROUP_CONCAT(DISTINCT CONVERT(teacher_assignments.teacher_id, CHAR(8)) SEPARATOR ',') AS teacher_ids,
        GROUP_CONCAT(DISTINCT teachers.name SEPARATOR ',') AS teacher_names		
    ",
    'from' => "
        classes
            LEFT JOIN teacher_assignments
                ON classes.id = teacher_assignments.class_id
                AND teacher_assignments.active = TRUE
            LEFT JOIN class_slots
                ON class_slots.id = classes.slot_id
            LEFT JOIN teachers
                ON teachers.id = teacher_assignments.teacher_id
            LEFT JOIN student_assignments
                ON student_assignments.class_id = classes.id
            LEFT JOIN students
                ON students.id = student_assignments.student_id
            LEFT JOIN rooms
                ON classes.assigned_room_id = rooms.id
    ", 
    'where' => "",
    'groupBy' => 'classes.id'
),
// ...

And of course, I store the the fields that one would want to query by in a separate array:

// ...
'classes' => array(
    'teacher_id' => "teachers.id %c AND teacher_assignments.active = TRUE",
    'student_id' => "students.id %c AND student_assignments.active = TRUE",
    'id' => "classes.id %c",
    'room_id' => "rooms.id %c",
    'has_note_id' => "notes.id %c",
    'slot_id' => "class_slots.id %c"
),
// ...

I love this kind of low-level control!

The %c token you see above is replaced with an operator and the value to be searched. Operators are stored like so:

var $operators = array(
    'gt' => '>"%v"',
    'lt' => '<"%v"',
    'gte' => '>="%v"',
    'lte' => '<="%v"',
    'e' => '="%v"',
    'ne' => '!="%v"',
    'c' => 'LIKE "%%v%"',
    '>' => '>"%v"',
    '<' => '<"%v"',
    '>=' => '>="%v"',
    '<=' => '<="%v"',
    '=' => '="%v"',
    '!=' => '!="%v"',
    'contains' => 'LIKE "%%v%"',
);

The first bulk of operators you see there (gt, lte etc.) are utilised within query strings. E.g.

/query?ret=students&k=age&c=gte&v=16
(get all students with an age >= 16)

Don’t worry though! This seemingly low-level access isn’t actually that revealing. In order for a query to execute, the field that you want to query by must be specified, in the query-model, as must the operator. Values are escaped also.

I want to share more about the implementation but it turns out everything else is quite boring and basic. The query-model thing was really the only point worth nothing. That said, I am trying out an action-managing idea which allows delayed database actions such as deletion or flagging. Each action has a UUID and can be activated via a URL (this would also work well for email-activation links). Anyway, maybe next time…

It works!

We’ve been entering data and testing it for about two weeks and today saw a mass adoption by all volunteers at Isara. Hopefully this system will last, at least until a more efficient alternative comes along.

Thanks for reading! Please share your thoughts with me on Twitter. Have a great day!