next up previous contents
Next: Password Policy Up: Open Administration for Schools Previous: Introduction


Data Tables / Scripts Overview


Image demographics

The main demographics information is stored in several student tables:

The student and studentwd tables are the tables that hold student information and are identical in structure. The studentwd table holds withdrawn students. When the student withdraws, their record is moved from the student to the studentwd table. In the event of re-enrollment, the record is just moved back into the student table. The studentall table is a virtual table that is the joining of the two student tables. It is effectively a view but in MySQL 4.x, this is called a merge table.

The studnum field uniquely identifies the student. Within a school division blocks of student number are assigned to each school to keep the student number (studnum) unique within the division. For individual stand alone schools, this is not an issue. It starts a 100 by default.

The transfer table stores enrollment change information. Every time a student enrolls or withdraws from the school, one record is added to the table.

The staff table) is used to identify the teachers, TA/EA's (teacher aides or educational assistants), and other teaching staff members. Each person is uniquely identified by their userid field. Each person should have this field entered. The homeroom field will match the homeroom field in the student records. The doatt (Do Attendance) field indicates which teachers do attendance (and are tracked for entry of daily attendance). The certif field holds the teaching certification number for use with the Saskatchewan SDS system. (or other provincial or state based numbering system).

The main administration site has the following scripts in cgi:

When students withdraw, they are moved from one table (student) to another (studentwd) When they re-enrol, they are moved back. A transfer table keeps track of all of these movements and all new enrollments, etc.

The entry system (enrol/withdraw) has the following scripts in cgi/entry:

The start/end of year area has the date management scripts but also student reset scripts and permanent student deletion, password changes, configuration viewer, etc. It has the following scripts in cgi/eoy:

The Attendance System

Image attendance

The Attendance system is stored in a couple of tables:

  1. The attend table stores student attendance. There is one record for every period that a student misses. The student is identified by the studentid (aka studnum fields in other tables) field. This information is used to link together large numbers of tables. The absdate (absent date) and period field identify when the miss occurred. The reason and late fields indicate the reasons for absence to help categorize the records. The subjsec field identifies the subject / section (subjsec) missed for subject based attendance. This field is normally not used in elementary schools. The late field is not used by any functions, yet. The id field (called attid in this table; a design mistake) is used to uniquely identify each record to allow them to be edited or deleted in the event of an entry mistake. This id field is used in most other tables also, for the same reasons.

  2. The tattend table is used to track teacher attendance entry. Every time a teacher enters attendance on the teacher site, a record is stored in this table. This will allow central office to ensure that every teacher has entered his/her attendance (even if perfect attendance) so that absences can be tallied, etc. This is necessary since OA only tracks ``absences'', not ``presences''. In many schools, this is not required.

The attendance system has the following scripts in cgi/attendance:

The Discipline System

Image disciplinesystem

The discipline system uses two tables:

  1. The infraction table which stores the descriptive text for the different kinds of discipline events in the school.

  2. The discipline table stores the discipline infractions for the students. There is one record for each infraction. The userid field is the student number field (studnum). The author field is the userid for a staff member. The private field is used to mark records only visible from the admin site, not the teacher site. The parent field indicates the nature of parental notification about the event. The action field indicates any action take due to the event, and the repeat field is the nature of the consequence in the event of a repeat of this behavior.

The discipline system has the following scripts in cgi/discipline:

The Fees System

Image fees

This system is used to track student fees owing, and notify parents of amounts owing.

There are two tables used to implement this system:

  1. The fees_jrl is the main fees journal table and holds all transactions.

  2. The fees_predefined table is the table that stores predefined charges. It includes the following fields:

    The last two fields indicate to which students this charge would apply (although this can be changed when applying the charge).

All of these scripts are located in the cgi/fees directory and are linked via the fees.html page on the main menu. They include:

The Lunch System

Image lunch

The lunch system has the following scripts (also located in cgi/fees):

The Locker System

Image lockersystem

A system for managing locks and lockers.

The 12 scripts (located in /cgi/locker) include:

  1. - add lockers to the system.
  2. - assign lockers to students.
  3. - unassign lockers.
  4. - edit locker values.
  5. - search for lockers based on criteria.
  6. - view locker values.
  7. - full locker report.

  8. - upload locks into the system (from csv file).
  9. - add locks into the system.
  10. - assign locks to lockers.
  11. - edit lock values.
  12. - view lock values.

These are called from the locker area of the fees page.

The Report Card System

Image reportcardsystem

The Report Card System makes use of a couple of tables:

  1. The subject table - this table stores information about a single course in each record. It lists the course subject, who teaches it, the course code and section number and the starting and ending reporting periods. Each subject-section is identified by a unique subject-section code (called subjsec) which is the subject code, a hyphen, and a section code (ie. 8415-1)

  2. The eval table - (or evaluation table) stores the teacher evaluations for a student in a particular class and section. When a student is enrolled, one record is entered for each reporting period in that class and section. For example, if there are 4 reporting periods (terms) for a particular subject/section, then 4 records are added for each student enrolled in that subject/section.

    This table stores not only the evaluation results but also the subject enrollment. When a student has records here, he/she is enrolled.

The subject table has the following fields:

  1. The teacher is the userid and name of the teacher. This course will be listed under the teacher's name on the staff mark entry pages while entering evaluations. The name part will be removed in the future, since this is a design flaw. It will exactly match the userid from the staff table in the future.

  2. The paa modules are course ``sub-components'' listed by Saskatchewan Learning. They only have meaning in Saskatchewan, and information from this field is not currently used by other parts of the admin system.

  3. The type is an attempt to classify subjects on the basis of the kinds of entry values needed for evaluation. However, as of 2.00, you may leave this field blank.

  4. The sequence is a number that controls the order with which this subject prints on a report card. If, for example, you wanted particular core subjects listed first, they would have lower numbers. It is suggested that one go up by tens (10,20,30) when entering sequence numbers to simplify the process of re-ordering subjects or adding in new ones.

  5. The web visible field controls whether this subject could be visible on the web to authorized individuals (ie. Parents). This would, of course, mean only their own child and not all students taking the class. This is used by the administration software as of OA 1.50.

  6. The faculty field lists which faculty offers this subject. It will help classify subjects for academic requirements. Currently unused.

  7. The location field lists where this subject/section is offered. Currently unused, although this will change in the 2.x series.

  8. The mark scheme is a text block that describes the evaluation process used by this subject. Currently unused.

  9. The 20 objectives fields describe different particular objectives, etc. that a teacher is using to evaluate his/her course. These will be printed on the report cards (normally on the administrative site) and are also present when entering student evaluations (on the teacher site). Normally the first objective field is used for numeric results, and later ones, if used, are more descriptive.

  10. The subject aliases are small numbers that uniquely identify a subject/section and are used only for doing particular types of subject based attendance.

The report card system has the following scripts in cgi/repcard:

The Transcript System

Image transcript

The transcript system is designed to store student course information over multi-year time periods while the student is enrolled in school (and even after he/she has graduated or withdrawn/moved).

It consists of 2 tables:

  1. tscriptident - this table stores the basic identity information about the student. There is only one of these records for each student.

    It includes:

    Other fields could be easily added to this table to store other information that is required about the student over the longer term (ie. perhaps other important number such as SSN (Social Security Number), etc.

  2. tscriptdata - This table stores the actual course information. This information is read (along with the identity information) to create the student transcripts.

There are only a few scripts (3 currently) required to implement this system. As a result, they are all included in the cgi/repcard directory along with the other report card scripts. The scripts themselves should be read to understand their operation.

  1. The Transcript Post script ( - this script copies information from the current year's student evaluation (eval table) into the transcript tables (tscriptdata, tscriptident). It adds a transcript identity record if one doesn't already exist and then adds data records for the subjects selected. It will skip any existing records if they already exist. Existing records can be edited directly.

  2. The Transcript Edit/Delete script ( - this script allows transcript records to be edited and deleted in the transcript data table. The student information table (tscriptident) is not affected since these records may remain even if there are no matching data records.

  3. The Transcript Report script ( - generates the actual PDF transcripts. It generates both an HTML output as well as the PDF output required.

The transcript system has the following scripts:

The PK Report Card System

Image pkrepcard

The pre Kindergarten report card has the following scripts in cgi/repcard_pk:

The Scheduling System

Image schedule

The schedule system has the following scripts in cgi/schedule:

Subject scheduling scripts that edit data for FET timetabler use.

The Transportation System

Scripts for this system are located in /cgi/fees also.

The Export System

The export system has the following scripts in cgi/export:

Image Management

The image management system has the following scripts in cgi/image:

The Preregistration / Waiting List System

Image preregwait

The Preregistration System is used when a school wants to:

  1. Register kindergarten(K) and prekindergarten(PK) students before the end of the current school year.

  2. Want to do homeroom and grade assignments before school end.

It uses 3 tables:

  1. prereg - a clone of the student table. Preregistration entries (ie. K, PK) are stored here and then added to the student table later.

  2. preset - updated values for homeroom and grade for students that will be used to update the student table later.

  3. prestaff - clone of the teacher table. Stores new teaching homeroom assignments, etc. for pre-registration reports. This is used to replace the teacher table in the new year.

The preregistration system has the following scripts in cgi/prereg:

The Staff Management System

Image staffsystem

This system includes the 2 tables above (staff, and staff_multi) linked with the common userid field (which also links the staff to other tables such as the subject table in the report card system, etc.

This system is supported by the meta system and both editing and reports are generated using templates.

The scripts, located in /cgi/staff, are used to add, edit, and report on staffs. The staff report is template based and can load multiple formats.

The staff management system has the following scripts in cgi/staff:

The Date System

The dates table is used to store dates for the school year. They indicate days (Monday - Friday) that school is not in session due to statutory holidays, school holidays, inservice, etc. This is required to correctly calculate student attendance since OA only tracks days that students are away, not present.

These date scripts are located in /cgi/eoy and include:

The Metadata System

Image metadata

The metadata system allows us to provide metadata (data about data) about values in fields of tables. Currently this supports the student tables (student, studentwd, prereg), the staff tables ( staff, staff_multi), and also the student_inac tables (used in Canada for First Nations schools).

The meta table provides information about each field in those tables including, text name (supporting translation), default values, type of edit form used ( plus other edit characteristics ) and whether a required field during entry.

There is a libmeta library function in the lib folder. It provides the metaInputField function to provide entry form values 'around' the staff or student fields (identified by the fieldid).

The entry method in general is:

  1. Read the template into a text variable. Templates are stored in the templates folder at the same level as the cgi, etc, admin folders and have a filename .tpl extension.

  2. Read in the field values for the table of interest from the meta table. This includes the fieldid and the fieldname (which may be a translated value updated by the translation system). These are loaded into a hash to make it easy to lookup a fieldname based on a fieldid in the template.

  3. Replace fieldid's in the template text (identified by $<$*field*$>$ values where 'field' is the fieldid ) with the fieldnames loaded from meta table.

  4. Next load record values (if any) into a hash indexed also by fieldid (which is the name of the field in the table). Then use a regex expression to search the template for any fieldid of the form $<$@field@$>$ and replace those values with the value from the staff or student table. This will allow us to display the names and values of a particular student or staff record.

  5. If there is a desire to edit values, then use the metaInputField function in a form. Use the same search and replace method to return form elements (with embedded value) into the form text.

If this is a addition operation rather than an edit operation, there are no existing values. As a result, there is no need have a read values step and nothing is passed to metaInputField for the value.

Here is some example code from the script for an edit operation. A display only operation does not require the metaInputField loop.

  # Read in Template
  unless (open (FH,''<../../template/inac.tpl'')) {
    print $lex{'Unable to open template file:'},''$!\n'';
    die $lex{'Unable to open template file:'},''$!\n'';
  my $formtext;
  { local $/; $formtext = <FH>; close FH;}

  # Get fieldnames from meta, store in hash.
  my $sth = $dbh->prepare(``select fieldid, fieldname from meta where tableid = ?'');
  $sth->execute( 'student_inac' );
  if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
  my %fieldnames = ();
  while ( my ( $fieldid, $fieldname ) = $sth->fetchrow ) {
    $fieldnames{$fieldid} = $fieldname;

  # Now replace <*fieldid*> with fieldnames; this gives us the text;

  $formtext =~ s{\<\*(.*?)\*\>}
  { exists( $fieldnames{$1} )
       ? $fieldnames{$1}
       : $1

  # Formtext is now ready for multiple use; only contains <@fieldid@> values.
  # now parse for form entry replacement elements  <@name@>
  # Extract fields from template
  my @fields = ();
  while ( $formtext =~ m/\<\@(.*)\@\>/g ){
    push @fields, $1;

  # Get their record, if any.
  $sth->execute( $studnum );
  if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
  my $recref = $sth->fetchrow_hashref;
  my %fieldvals = %{ $recref };

  # get replacement form element values for fields
  foreach my $fieldid ( @fields ) {
      $values{$fieldid} = metaInputField('student_inac',
        $fieldid, $fieldvals{$fieldid}, $dbh, $studnum );

  # now put form elements (including values) back into $formtext variable...
  $formtext =~ s{ \<\@(.*?)\@\> }
     { exists($values{$1})
         ? $values{$1}
         : ``$values{$1}-$1''

The metadata system has the following scripts in cgi/meta:

The Translation System

Image translation

The translation system has the following scripts in cgi/xlat:

External User Management System

The external user management system has the following scripts in cgi/usermanage:

The LDAP scripts manage users on an external ldap server. The ldap area has the following scripts in cgi/ldap:

The SIRS3 (another SIS) import system has the following scripts in cgi/dbfimport:

INAC System

Image inac

This is an additional table and scripts addon to the student demographics system. It provides the data required to print INAC reports such as the nominal roll.

The inac area has the following scripts in cgi/inac:

The Gradebook

Image gradebooksystem

The Gradebook uses two tables:

  1. gbscore - holds individual student scores for a particular test / assessment item.

  2. gbtest - holds information about each test/assessment item for all subjects.

next up previous contents
Next: Password Policy Up: Open Administration for Schools Previous: Introduction