next up previous contents
Next: The Attendance System Up: Open Administration for Schools Previous: Getting Started with OpenAdmin


Student Demographics

The student demographics information is stored in tables called student for current students and studentwd for withdrawn students. Pre-registered students are stored in a separate table called prereg. All three tables have identical structure.

When a student is enrolled, his/her information is stored in the student table. If they withdraw, this information is simply transferred to the withdrawn student table (studentwd) from the student table . If he/she returns and re-enrols, the information is transferred back into the student table (from the withdrawn table). No student information is erased in this process. Withdrawn students may be permanently deleted using a button on the Start/End of Year (EOY) page.

The preregistration table (prereg) holds student information for students coming into the school in the next school year. This is typically used for Kindergarten enrollment to see what the student numbers will be like for the next year, in order to plan for staffing, etc.

These are fairly large tables and contain the following information. They can also have additional fields added to track other information important to the school (more on this later).

Basic Student Information

First Name (given name), Last name (family name) and Middle Name identify the student. The names should be entered with initial capitals (as all proper nouns), but not all capitalized. This makes the name harder to read and may upset report printing due to the larger size. There are also additional prefixes and suffix fields as well as a 'known as' field (ie. common name)

The Local Student Number is a unique number that identifies the student within the school or school division. It is simply a small number that is stored in a file and increased by one for each new student enrolled. For small schools, a good starting number might be 100 and reset the number when 999 is reached. For larger schools a four or 5 digit number might be chosen. There is no requirement that the number remain a certain number of digits; it's simply an identifying number and smaller numbers are better (easier to type).

Within a school division, students may move from school to school. If the local student number is unique within the division, then she/he may be enrolled within the new school by simply entering the local student number from the previous school. All demographic information will then be automatically imported. It is highly recommended that student numbers be unique within the division, since it solves many identification problems, particularly in shared systems like the IEP (Special Education) system.

In order to ensure uniqueness, different schools are usually assigned large blocks of numbers for student numbers (ie 10 000 - 19 999 for school 1, 20 000 to 30 000 for school 2, etc. OA doesn't currently automate the wrap around process for schools. (ie. once 19999 is reached the next student doesn't automatically become 10 000.)

The next available number can be seen from the Start/End of year page using the View Configuration Files button.

The local student number is not visible while doing data entry during student enrollment. It is assigned as soon as the student information is stored. This field is not editable since it is used to link together large amounts of information about that student including attendance, marks, discipline, etc.

The Provincial student number is a government number given to each student. In Saskatchewan, it links the local system to the government system by means of an Internet data transfer (found on the export page). This digit is 9 digit number in Saskatchewan, and contains a parity digit to check for data entry errors. This may be checked by using the Check Prov# Parity on the bottom of the Export page. It will be useful in other jurisdictions to store the State / City assigned educational identity number.

The Birthdate, like all dates in OA, follow the ISO standard of yyyy-mm-dd. The pop-up Calendar app generates this date format. Birthdates are also used to identify students, so correct entry is important.

The homeroom field and grade field are particularly important to locate and group students. In a multigraded classroom, every student would have the same homeroom field (ie. 567 or Rich... the grades or teachers name or some combination) but a different grade value. The smallest grouping would be a grade grouping in this classroom. John is a student in a 3/4 split grade. John is in grade 3 and the homeroom is 34JW (taught by Janet Wilson). Tyler is also a student in that room. He is in grade 4, but still have a homeroom of 34JW. Jane Wilson in the staff table has 2 records (with the same userid and password), with both a homeroom of '34JW' but one record has grade 3 and the other has grade 4. This will link Janet to all of her students. She will appear as their homeroom teacher on the report card (if this function is turned on in the report card).

In a large school with many grade 2 classrooms, the smallest grouping would be the homeroom, with all grade 2's having the same grade field. Freddy would be in Grade 2 in the homeroom 2RR, while other grade 2's are in the 2FS homeroom, etc.

Notice that the homeroom field can contain up to 8 characters and the only rule is that the teacher and students fields match. This homeroom teacher information appears on the report card, also. If a teacher teaches a multi-graded classroom, two (or more) entries may be made in the staff information... one for each grade. They will be identical in all other fields. This way each grade will have a homeroom teacher listed. You may not combine several grades in the grade field.

High school teachers and subject specialists normally won't have any values in their grade and homeroom fields, but they are matched to students by the student subject enrollment. As a result, high school teachers will not normally have a grade field or homeroom field filled.

The Grade field has the grade information, and should not store other information. Don't confuse this field with the homeroom. For grades lower than 1, use K (capital) for kindergarten and PK for prekindergarten. For preschool use P3 to indicate 2.5 to 3.5 year olds. Otherwise we use numbers from 1 to 12. Other jurisdictions may have alternate values for early childhood education.

The House field is used for grouping students into larger groups than classrooms for intramural sports programs, etc.

The Health ID is a government identifier associated with health care identification. It is normally entered without any separators (like hyphens or spaces). For Saskatchewan, the 9 digit health number has a check digit which verifies whether any digits have been transposed, etc. Use the Check Health# Parity button on the bottom right of the Export page.

The Password field is a password that the student (and his/her parents) may use to view the student's gradebook and report card marks and attendance on the Parent Site (ie. Parent Portal). It is autogenerated, but may be changed. It must pass the password policy of the school.

The Email field stores the student's email address so that teachers (and administrators) may contact the student.

The Program field is used in Saskatchewan to identify the program of instruction. The default value for an English program is 10. Alternate languages have different values as do certain special education programs.

The Ethnic Category field gives the cultural and ethnic background of the student. In Saskatchewan, these map to Aboriginal status and would include Non Native, Status Native, Non-status Native, Metis, etc.

The CIS Number or Treaty Number or Status Number is a federal government number uniqiuely identifying a status native student to the federal government, as found on the CIS card (Certificate of Indian Status) (or it Treaty Card) from the IRS (Indian Registration System) of INAC (Indian Northern Affairs Canada).

The Band Name and three digit Band Number identify the band to which the student belongs.

The Reserve Resident identifies students living on First Nations reserves. This is a Y/N (Yes/No) field. In Saskatchewan it will be sent as part of the demographics information to Sask Education for native schools.

The Single Parent, Youngest, and Internet fields are single character fields that contain a 'Y' if true. It identifies a single parent family, a student allowed on the Internet, or the youngest child in the family.

The youngest student in each family is typically given information to go to the home (ie. Newsletters, etc) since it will be placed in the student backpack by the teacher. The enrollment reports list the number of youngest in family in each classroom so the secretary knows how many to make and how many go to each classroom.

The Mail Contact field is a place where you may place information that would normally be found on a letter going home above the address. It could say, for example, Parent(s) of John Smith or it Fred and Wilma Flintstone, etc.

The Medical Notes section is an unlimited text field for describing any medical conditions that a student might have. Values in this field can be searched for with the medical report. It would typically contain allergy information, any medication the student is taking on a permanent basis, and any medical conditions that teachers and administrators should be aware of.

The final fields in this section are related to the religion of the student and his/her parents, as well as mother's maiden name. The last four fields are again Y/blank fields indicating whether student has been baptised, etc. In a public school these fields may be hidden by your network administrator by changing a value in the stylesheet.

Parent/Guardian Information

There are two sections... one for each parent. Parent 1 is considered to contain the primary contact information for the student, such as home phone number, address, etc. Thus single parents should have values filled in under Parent 1. For two parent families, it doesn't matter whether the mother or the father is the first parent filled in, although the contact information (ie. address, city, postal code, etc) must be filled in for parent 1. The relationship field identifies the nature of parent 1 and parent 2 (ie. father, mother, guardian, etc).

The contact field is a field that would describe any limitations on student contact with that parent (normally the secondary parent). This would be the case with separated parents and some sort of court order in effect. Otherwise these fields are blank.

The rest of the parent information is quite self explanatory. The parent's names field should have both the firstname and the lastname fields filled in.

Other Information

Additional fields storing information include:

There are some other fields that have other uses and are not normally included in forms for editing. These include:

Customization and Metadata

Currently, both the student information and the staff information can be customized to capture any additional data that is required by the school. The adding, displaying and editing of data in these tables is controlled by two things:

The Student Demographics Information

Any school or division may add additional fields of information to the student table to allow for other information that a school may need in different jurisdictions. You can also repurpose other fields so that you use them to store different information, such as using the provincial number to store another local or state educational identifier. I would avoid using some of the first 10 or 15 fields since a few reports are hard wired to use particular fields.

Note: Just because you have added in these new fields, doesn't mean that they will then automatically show up in a report. A new report would have to be constructed (hopefully template based) to show this specific information. Upcoming versions of OA will have the ability to create and reuse custom report formats.

When these fields are added to the 3 student tables (student, studentwd, prereg), the layout of the template that is used for student enrollment and editing of student information must also be updated to reflect these additional fields. This is located in the templates directory of OA and is called it student.tpl. Open this file with a normal text editor and add and change values as required.

The student view report (top right on main page), which also use templates, can also be updated to add these new fields to the report. This template is called studentview.tpl. The student roster reports don't use templates, yet.

As well as adding new fields, the type of entry method, defaults, etc. can also be adjusted by secretaries or administrators, if desired. This is changed by using the Meta Edit button on the Start/End of year page. The Meta Update button is used after you have changed the student or staff table (with additional or fewer fields) to synchronize the fields in these tables with matching records in the meta table (to control these fields).

The metaedit program allows your to change information about how each of the fields in the student and staff table is handled. Once you have the program running you should select the table to edit. You will then have the following choices for each field:

This information is used both when adding new students, but also when editing student information.

Student Enrollment and Withdrawal

There are 3 basic operations when dealing with student enrollment:

  1. Enrolling New Students that have not been enrolled in your school (or possibly your division) before.

    You enrol a student in your school by clicking on the Enrol/Withdraw button (right side of main page) while leaving the student number box blank. The student does not yet have a local student number. This button is located on the right side of the main page in the Student Enrollment/Withdrawal section.

    The next screen asks for information about the reason for enrollment. This is set in a configuration file and as distributed is specific for Saskatchewan. It can easily be changed to fit your jurisdiction. Other fields include the province and country where this student has come from. The description field will allow the entry of other information such as the status of Cum folders, mailing address for old school to request cum folders, etc. This information is stored in the transfer (ie. enrol/withdraw) record for the student, and can be edited at a later date as well. Most secretaries use this space to store cum folder information when student is withdrawn. (and the cum folder is sent to his/her next school).

    The final screen lists the student information fields to be filled in as discussed above. Once this information is saved with all required fields filled in, the student number is saved along with the record.

    The system will automatically assign a local student number to the student. If there are multiple schools in a division, this number should be unique within the division. This will allow the student to transfer schools and also be uniquely described within the IEP system (Special Ed). The student number is assigned based on a value stored in a small file. It is increased by one as each student number is assigned. You can view the next available value by using the View Configuration Files button on the Start/End of Year page.

    If you are entering several family members with the same parent information, you can simply click the back button on your browser and change the student name, birthdate, etc. while leaving the parent, emergency contacts, etc. identical. This will allow easy entry of entire families of students.

    If the student was already enrolled in another school in your division, and is transferring to your school, you can easily enrol them. First find their student number at their previous school. Your schools must be using division unique school numbers. You can look this up by clicking on Withdrawn Students - Division and view the list of withdrawn students throughout the division. Once you have his/her student number, simply enter this into the student number field above the Enrol/Withdraw button in the Student Enrollment section of the main page. Their student information will be copied into a local student record and automatically enrolled in your school.

    You can view the enrollment record (called Transfers in OA) by clicking on the View/Edit Transfers button in the same section as above. When sorted by Date (the default) your latest enrollment record should be at the top. Click on the name to edit the record (and put in description information abouve schools, cums, etc.)

    The Saskatchewan SDS script of OA now synchronize student data with the provincial data system.

  2. Withdrawing Enrolled Students that have moved to other schools, possibly in your division.

    In order to withdraw students, you simply enter their local student number into the student number input box and click the Enrol/Withdraw button. This is located in the Student Enrollment area on the right side of the main page, as mentioned previously. The next screen will indicate a student withdrawal with withdrawal reasons to select, etc. These reasons are initially set for Saskatchewan, but may be changed in the global configuration (global.conf) file.

    When the student is withdrawn, his/her information is not erased. It is simply moved to another table called studentwd. You can see (and edit) the withdrawn student information by clicking the Edit Student Records button. This is located on the right side of the main screen in the View/Edit Students section.

    Before the student is withdrawn, you should make sure that you print all attendance reports, report cards, etc. that will be required. His/her attendance information, report card marks, gradebook marks, discipline records still exist (and are not transferred anywhere). However, most reports will not display this information for withdrawn students (unless there are options to allow this).

    If a student is withdrawn before this is done, then she/he may be re-enrolled and then withdrawn again....However, you must immediately remove the extra transfer records that have been created by doing this. Click the View/Edit Transfers button and delete the two records (one re-enrol and one withdraw) that you created while doing this.

    If a student has withdrawn from school several years previously and will never return (graduation, too old, etc.), you can permanently remove most information about him/her from the local system by using the Permanent Student Deletion button on the Start/End of Year page. This will delete them from the withdrawn student table, and also remove any attendance, discipline, report card and gradebook information. The transfer table information (enrollment/withdrawal records) as well as subject enrollments will be kept beyond this date. Other information is deleted. Transcript information will also not be deleted. You must make sure that you have posted all student report card information into the transcript system before erasing it.

  3. Re-enrolling Students that have returned to your school and were previously enrolled in your school.

    You enter the currently withdrawn student's student number into the student number box and click the Enrol/Withdrawn button. The withdrawn student's number can be found by simply clicking the Edit Student Records button and then finding the student's name in the withdrawn student list. His/her local student number will be in brackets beside the name.

    Once you give reasons, etc. for re-enrollment, his/her student information will be moved from the withdrawn student table (studentwd) to the student table. A new enrollment record will also be added to the transfer table.

In summary, when you enter the student number of a student into the student number field of the enrol/withdraw script (rather than leaving it blank for a new student), it searches both the current student table (student) and the withdrawn student table (studentwd). If the student exists in the student table, he/she must be withdrawing. If the student exists in the withdrawn table, he/she must be returning to the school. If the student number does not exist in either table, it must be a new student. A new student entry screen is displayed to allow entry of student information. If a blank number is entered, the system picks a new number for that student.


When enrolling students for the next school year, we add them to a separate preregistration table so that they will not show up in the normal year end student reports This is done so that, before school end, we can, if necessary:

You preregister a student by choosing a blank student number, as usual for new students, but check the box Pre-registration Entry. Once the student information is entered, it will save the student record not in the normal student table but in the pre-registration table. This will be confirmed by the button at the bottom of the new student entry saying Save Record to Preregistration Table and a confirmation to that effect once this had been done.

Once regular school reports, etc. have been run for the year, these students may then be added to the normal student table as part of the preregistration process (outlined on the Start/End of Year page).

Outlining this process, there are basically two methods:

  1. The simpler approach is used when there is no need to create projected enrollment reports from this system or teaching assignments and classroom enrollments before the end of the current school year.

    The method is to:

    1. Promote current students to the next grade (using the Grade Update script on the Start/End of Year page).
    2. Copy the preregistered students into the student table (using the copy script on the Start/End of Year page).
    3. Use the Homeroom Update script to assign all students to new homerooms.

  2. The more complex approach is required when it is desired to assign homerooms, etc. for the next school year before the end of the current school year. This necessitates the creation of a temporary student and staff table to allow updates, and homeroom assignments to be done to these students and allow reports to be printed for these rooms (and teachers). The entire process is outlined on the Start/End of Year page (along with the appropriate scripting).

After either method is complete, please delete all students from the preregistration table so that more students can be added to this table at the end of the following year. Use the script provided at the bottom of the Start/End of Year page to do this.

If you see any students in the preregistration table near the start of the school year, when doing Edit Student Records, this has not been done (and should be).

Student Transfers

Whenever a student enters or withdraws from school, a record of this is stored along with the date, reasons, and description. The reasons are set in a master configuration file for all schools in a division (although single stand-alone schools must set this as well) called global.conf. The default reasons are set for Saskatchewan, but any values required can be set in this file.

The View/Edit Transfers script shows the student transfers and allows changes to some fields (or deletions in the event of mistakes of enrollment / withdrawal). This information is also displayed in the Attendance Profiles report (along with attendance records). The entry and exit codes are those set in the configuration file.

The Update tag is no longer used.

The description field can be used to store information about where/when Cumulative Folders were sent or received, etc. It is a 255 character field. The Prov / Country field is used to indicate the location that the student went to or came from (and is associated with the entry/exit codes). The Type is either withdraw or enrol (entering or leaving).

The Withdrawn buttons are used to view the local withdrawn students or the withdrawn students in the entire division (to find out the student number of the student for enrollment in your school). The local withdrawn students can be erased once any possible use for this demographic data has passed. (using the Permanent Student Deletion button on the Start/End of Year (Eoy) page. The advantage of this approach is the ability to enrol a student in your school that has transferred from another school in your division by simply putting their local student number into the Enrol/Withdraw input box.

Student Reports

These are reports that are located on the main administration page and print out information from the student table in various formats. The Adobe Acrobat PDF reports are designed to be printed while the HTML web pages are designed for on-screen viewing. This is certainly not mandatory; web pages can be printed and pdf files can simply be viewed. The pdf reports give us better control over page breaks and other page based layout.

The problem with the printed reports is remaining current. They may easily become out of date. The online web reports do not have this problem since they are always using the most up to date information from the student table.

Several of these same reports are also available to teachers on their teacher site.

There are basically three blocks of reports on the main admin page:

  1. Student Rosters - upper right.
  2. Student Reports - lower left.
  3. Enrollment Reports - lower right.

The student rosters include:

  1. Full Student Roster - a complete listing of each student's data in a format suitable for printing. (PDF format). It can be printed sorted by student lastname or by homeroom. There are 3 records per page, normally (but depends on template and script settings).

  2. Short Student Roster - a short form containing the most important student information in a printing format (PDF format). The number of students per page is selectable, in case of particularly long student records. It is printed sorted by homeroom.

  3. Short Student Roster, Selected - This prints the same information as the short roster report above, but allows you to select certain students for inclusion on the list. This might be useful for small groupings of students in alternative educational settings, etc.

  4. Short Student Roster, Web - Another short student roster, but designed for onscreen viewing, with sorting by lastname as well as homeroom.

  5. Mini Student Roster - An even smaller student roster report for screen viewing (ie. web based). This is quite useful for checking simple student values.

  6. Student Confirmation - A printed report designed for parents to allow them to confirm student and other information in the database.

  7. Custom Classlist - A printed PDF report with a grid to allow teachers to enter values for evaluation or other purposes. There are several options to control the groupings, the size of the cells in the table, and also allow column headings with rotated text.

  8. Custom Stafflist - A printed PDF report analogous to the Custom Classlist. It provides options to print a grid report on all or selected staff.

  9. Print Mailing Labels - A report printer to print student information in a variety of formats on standard mailing label stock. The main entry screen shows the available formats, and then allows selection of sortorder, student grouping and font size. Multiple groups (ie. 10,11,12 by simply entering them separated by a space 10 11 12) may be selected. The following screen allows a per student selection to print. (since, in many cases, only a few are needed).

The various Student Reports include:

  1. Birthday Lists - These reports sort birthdays suitable for office or homeroom ordering them by month and day throughout the year. These are both web reports but can be printed and posted.

  2. House Report - A pdf report listing membership in school houses for intra-murals athletics, etc.

  3. Medical Report - This reports information in the medical field of each student. Both PDF and Web versions are available. You can also search for values in this field to give a more customized version (ie Fluoride use, allergies, etc.).

  4. Phoning Report - A report showing all phone numbers for a student, similar to the attendance phoning report.

  5. Religion Reports - The Religion by Grade report gives the breakdown by grade and summary statistics at the end of the report. Web based. The Sacramental report gives parish based information including parent information. This is mainly designed to be used by religious coordinators in the schools. The Religion Details by Name lists all of the religion fields in each student record (confirmation, baptism, etc.).

  6. Reserve Report - A report showing aboriginal students in the school, grouped by their band affiliation.

  7. Track Report - This report creates a grouping of students based on their ages (suitable for splitting into age groups for track). It includes name, birthdate, house, gender, homeroom and age. The aging date can be selected to whatever you want.

  8. Youngest Report - A report showing all of the youngest in the family (a field in each student record). This is also visible as a Y in the student roster reports. This will allow the secretary (and teachers) to know how many forms are required by each classroom to go home, etc.

The School Enrollments report include:

  1. Enrollment by Class - shows summary enrollments for the school broken down by class.

  2. Enrollment by Age - shows enrollments by... age. The aging date is normally 'today', but may be set to any date value. (ie. to start of school year, calendar year, etc.)

  3. Enrollments by Ethnic Categories - this shows grade ethnic compositions or total school composition.

  4. Monthly Transfers is a PDF report normally used to track enrollment changes for central office.

  5. Monthly Enrollment Change is a web based report that shows changes based on a particular month's enrollment changes.

Student Search, View and Edit

The Student Search button can quickly find a student record using either the student's lastname, lastname and firstname (separated by a comma), or just the student's initials (or less often, student number). The search is case insensitive since MySQL (the database engine we are using to search) is compiled this way in most cases. This function may also be used to view the student's school picture if uploaded into OA. The functions to do this are on the Export Page.

It will display the student records that match the search and display most fields of interest. By clicking the Edit button, you can edit the student record. The fields displayed are controlled by a template, so additional fields can be added and unused fields removed by your system administrator.

The View Students button, like the search above, allows you to look at student records. In this case, by grade or class and sorted by lastname or birthdate. This is controlled by template also, and can be changed.

The Edit Student Records button, as the name suggests, allows you to make changes to the students demographics information, such as changes to phone numbers, addresses, etc. The screen layout is identical to the one used to add students. You may edit information on current students, withdrawn students, and preregistered students.

next up previous contents
Next: The Attendance System Up: Open Administration for Schools Previous: Getting Started with OpenAdmin