Updated: July 12, 2024 - updated table stuctures in DB, and changed this doc to match. Nov 14, 2024 - updated eval, dates_homeroom, utf8mb4 types for all tables. The systems based on tables below: Attendance attend - the student absences. tattend - the teacher attendance entries Student Demographics, etc. student - student demographics (name, parents, emergency contacts, etc) studentwd - withdrawn students. Table is clone of student. studentall - a view, not a table, which is a join of both student and studentwd. student_medical - student medical conditions student_needs - a table to store student needs assessments. transfer - enrollment / withdrawal of student from school. eyedata - early years assessments of young students. (PK/K?) Preregistration - planning for the upcoming year prereg - a clone of student; preregistrations (typically incoming P3,PK,K students) prereg_staff, prereg_staff_multi - clones of new year staff, staff_multi and their homerooms, grade prereg_waitlist - a list of students waiting to register, once there is an opening. preset - a table to store student homeroom, grade, etc. for homeroom assignments in the coming year. The following 2 tables are for high school course offerings in the upcoming year preplan_coursemaster - courses offered in upcoming year. preplan_courseperiods - numbers of periods offered per term Student Nominal Roll - information sent to federal First Nations dept (ISC / CIRNAC ) student_inac - student information for submission to ISC. student_nomrollwd - withdrawn students from student_inac. Course Enrollment / Report Cards subject - course master with course codes, name, terms, other course information. eval - course enrollments storing term marks for students. sasked_courses - courses offered in Saskatchewan schools. sasked_completedcourses - courses completed by students by date and their marks,credits. Used for local transcripts and course offering planning. Staff - information about staff, leaves, pay staff - main staff table with names, userid, password for teacher site, addresses, certification staff_multi - staff grade, homeroom, position - may have multiple records per staff member. staffwd - withdrawn staff. staff_transfer - records for dates of enrollment/withdrawal of staff staff_absent - record of staff absences, reasons. A simple approach. staff_secondary - store staff liason workers with secondary access to attendance, etc. The following 2 tables are used for a leave/payment system staff_leave - yearly values for allowed leaves (EDO, sick days, etc.) staff_payjrl - record of absences in different categories from values in staff_leave table. Discipline System disc_event - a discipline event linking to the other 2 tables. disc_ident - students involved in the discipline event disc_action - actions following from the event for students. Principal Walkthrough - quick, lightweight staff evaluation with a large variety of types pwalk_mst - walkthrough master table - stores title and description of a particular walkthrough. pwalk_tpl - template of values in the walkthrough master for each master type. pwalk_eval - evaluation of staff using a particular walkthrough (title,description) pwalk_score - staff scores in a particular evaluation in each of the template areas for that walk. Timetable schedat - timetable term, period, day (in cycle) and course; period_map - maps grades to particular periods and times. - used by the teacher daybook period_data - records of periods and their times. - teacher daybook. Configuration conf_system - a configuration table storing information used by all scripts. Used to create configuration files stored in etc table, and loaded by scripts. meta - a metadata table storing information for form entry for students,etc. including default values. dates - dates closed during school year. dates_homeroom - elementary homerooms closed since closure variable for K,PK grades. dates_periods - periods,grades closed when only partial closure. dates table is for fully closed. oaupdate - table storing information of OA updates for display on main admin page. sessions - table storing login sessions on the teacher site. announce - annoucements from admin site to teacher and/or parent/student sites. Transcripts - student completed course history and credits. tscriptident - identity of student in transcript. Multiyear data. Permanent. tscriptdata - course completions and marks. Used for transcript reports. Multiyear, Permanent. Translation - to alternate languages. xlat_lang - language of translation, the encoding used, english phrase and translated phrase. xlat_phrase - area and file used by phrases to enable rewriting of scripts. xlat_hint - the phrase and it's usage in scripts. MSS (MySchoolSask) System - 3 tables that import data from Fujitsu Aspen (called MySchoolSask in Sask) These 3 tables import data from three 'Blueprint' exports (csv format) that are setup and downloaded nightly by cron scripts. They import student, transcript and courses information. They are: mss_student - student information mss_transcript - multi-year transcript information mss_currcourse - current courses (including transcript information also). Audit System audit - table to track changes to data including author, date/time. Teacher System contact_log - stores contacts with parents by staff. homevisit - visits by staff to homes. plan_homework - homework assigned by a teacher and visible on parent/student site. Gradebook tables gbtest - gradebook assignments. gbscore - gradebook student scores linked gbtest records. Daybook tables - allow teachers to set their own timetables, and record teaching activity. dbktimetable - teacher personal timetable. dbkactivity - teaching of courses or other non-course activities dbkdata - results of any daybook activities. Fee System fees_predefined - a stored repeating fee structure to simplify entry. fees_jrl - a journal of fee charges and payments IEP (Individual Education Plan) for special needs students. These are tables used by a school for a local IEP program. OA has a separate system for multiple schools not in this document. iep_student - the student in the program iep_team - staff and others responsible for the student in the program. iep_objectives - large table of possible objectives/outcomes. iep_evalmst - list of outcomes (from objectives table) for particular student. iep_eval - evaluation of the student from evalmst for term. Reading and Math Systems read_leval_dra2 - reading goals for DRA (Diagnostic Reading Assessment) 2 Kits. read_leval_dra3 - reading goals for DRA (Diagnostic Reading Assessment) 3 Kits. Schools can be set for either one. read_test - the tests of students. read_test_score - the scores associated with a single test (multiple scores linked to single test) cree_scores - indigenous language testing. Will work with any FN language. lint_program - Literacy Intervention program (LLI) - assess student literacy improvement. lint_student - student in LLI program. mathca_outcomes - math outcomes for particular grades based on Sask Curriculum mathca_scores - student progress in math outcomes. ssp_exceptions - (ssp - Student Success Program ) exceptions - reasons why students missed testing for any reading or math test. Miscellaneous Tables ext_moodle - a table of student course enrollments suitable for use with an external online program like Moodle. school_event - a table recording school events (ie. Science Fair, Land Based Education, etc) that are used to generate monthly reports for FN Band council, etc. #---------------------------------- Description of all Open Admin tables All autoincrement 'id' fields are used to find the records and/or make them unique. The table storing announcements that appear on admin, teacher or parent sites. -- Table structure for table `announce` CREATE TABLE `announce` ( `id` int(11) NOT NULL AUTO_INCREMENT, `adesc` text DEFAULT NULL, - text descripion of the announcement. `atopic` varchar(255) DEFAULT NULL, - the topic of the `adate` date DEFAULT NULL, - date of announcement announcement. `topstay` char(1) DEFAULT NULL, `atype` varchar(16) DEFAULT NULL, - type which controls where displayed. Either the teacher site, parent site or both. It always displays on the admin site. `author` varchar(32) DEFAULT NULL - who made the annoucement. Likely blank since not auto filled. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The main attendance table for students absences/late. The tattend table is used to make sure teachers have actually done their attendance. When "perfect attendance" is chosen, only a teacher record is added to tattend, while nothing is added to the attendance table (ie. absences) -- Table structure for table `attend` CREATE TABLE `attend` ( `attid` int(11) NOT NULL AUTO_INCREMENT, `studentid` int(11) DEFAULT NULL, - actually the student number. same as studnum in other tables. `absdate` date DEFAULT NULL - date of absence/late. `reason` char(60) DEFAULT NULL, - text reason for the absence; from configured list `period` char(6) DEFAULT NULL, - period of the absence. `subjsec` char(32) DEFAULT NULL, - the course code (subject code - section; ie. 8017-1). For homerooms we use a leading 'HR:' followed by the homeroom name. `late` smallint(5) unsigned DEFAULT NULL - number of minutes late. All the index keys. PRIMARY KEY (`attid`), KEY `attend_absdate` (`absdate`), KEY `attend_studentid` (`studentid`), KEY `attend_subjsec` (`subjsec`) ) ENGINE=MyISAM AUTO_INCREMENT=6611 DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; A table to track the changes made to data. There is an audit library to enable this tracking to be added to any OA scripts. Currently we only track school enrollment/withdrawals. This table is likely to be updated with a new version as we update this to better track and report changes. -- Table structure for table `audit` CREATE TABLE `audit` ( `audid` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, `ipaddr` varchar(20) DEFAULT NULL, `scriptname` varchar(64) DEFAULT NULL, `tablename` varchar(64) DEFAULT NULL, `tableid` int(11) DEFAULT NULL, `startval` text DEFAULT NULL, `endval` text DEFAULT NULL, `tstamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`audid`) ) ENGINE=MyISAM AUTO_INCREMENT=293 DEFAULT CHARSET=utf8mb4; The main configuration system table. This is used in turn to generate an 'admin.conf' file which is stored in each 'etc' directory for each school. Some scripts may also load sections of the configuration separately, since the admin.conf file contains all variables and values. -- Table structure for table `conf_system` CREATE TABLE `conf_system` ( `id` int(11) NOT NULL AUTO_INCREMENT, `filename` varchar(64) DEFAULT NULL, - names for the different configuration files. `sectionname` varchar(64) DEFAULT NULL, - section of each file. `sequenceval` int(11) DEFAULT NULL, - sequence or order of each variable. `dataname` varchar(64) DEFAULT NULL, - name of the variable. `datatype` varchar(32) DEFAULT NULL, - type of the variable: array, hash, scalar, scalaronoff `datavalue` text DEFAULT NULL, - the value of the variable. `hashvar` varchar(64) DEFAULT NULL, - a hash variable storing config info. `version` varchar(16) DEFAULT NULL, - unused. `description` text DEFAULT NULL, - text description of the variable. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=347 DEFAULT CHARSET=utf8mb4; A table to track teacher contact with parents. -- Table structure for table `contactlog` CREATE TABLE `contactlog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - student number of student `cdate` date DEFAULT NULL, - contact date `category` varchar(255) DEFAULT NULL, - category of the contract (free form) `description` text DEFAULT NULL, - description of the contact `author` varchar(40) DEFAULT NULL, - teacher userid; maps to the teacher in staff table. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table to store FN language (not just Cree) scores of students over multiple years, grades. -- Table structure for table `cree_scores` CREATE TABLE `cree_scores` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - student number `treatynum` varchar(16) DEFAULT NULL, - treaty number for additional confirmation of student `tdate` date DEFAULT NULL, - test date `tauthor` varchar(40) DEFAULT NULL, - test author `tgrade` varchar(8) DEFAULT NULL, - student grade when test done. `tage` varchar(16) DEFAULT NULL, - age of student when test done. `tstamp` datetime DEFAULT NULL, - date/time of test entry (hopefully closed to test date) `prepost` varchar(16) DEFAULT NULL, - pretest or posttest. Typically only report on posttest. `scoremonth` smallint(6) DEFAULT NULL, - month of the school year. `score` smallint(6) DEFAULT NULL, - student score (out of 10). `schoolyear` char(4) DEFAULT NULL, - school year in 2023 (ie. 2022-23) format. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=791 DEFAULT CHARSET=utf8mb4; The dates during the school year when school is closed. Since we only track attendance absences/lates and not presences, we have to know any full days or partial days closed to accurately report on attendance. There are several configuration variables also used in conjunction with the dates table. Example: schoolyear (2023-2024), schoolstart(2022-09-05), schoolend (2023-06-30). These are set when the school year is configurated. There are other values in the dates section of the main (admin) configuration. All dates are stored in ISO-8601 format (yyyy-mm-dd). -- Table structure for table `dates` CREATE TABLE `dates` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, - date of the full day school closure `type` varchar(60) DEFAULT NULL, - type is stat holiday, etc. `desc1` varchar(60) DEFAULT NULL, - description of the closure `desc2` varchar(60) DEFAULT NULL, - unused. `dayincycle` char(1) DEFAULT NULL, - yes/no 1/0 value. One if this day closed is a day in cycle, and thus this day is 'lost' (skipped over). Zero if this day closed doesn't cause a loss of a day in the school cycle. `dayfraction` decimal(4,3) DEFAULT NULL, - either 0 or 1. 1 is full day close; 0 if partial closure and the other table dates_periods stores the grades and periods closed for partial closure. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; This table is used the PK/K homerooms that only come school on alternate days or alternate mornings/afternoons. It is difficult to track these closures since they are somewhat 'random' and may start later in the school year also. This table indicates closures for these early years grades, if necessary. This is being expanded to other grades. -- Table structure for table `dates_homeroom` CREATE TABLE `dates_homeroom` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, - date of the closure `period` tinyint(4) DEFAULT NULL, - period of the closure (typically only 2 periods per day) `homeroom` varchar(32) DEFAULT NULL, - name of the homeroom (ie. 2A or 2/3 etc) Removed 2024. `closuretype` varchar(32) PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table to record partial day closures. -- Table structure for table `dates_periods` CREATE TABLE `dates_periods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, - date of the partial closure `grades` char(8) DEFAULT NULL, - grades this is closed for. This has a format to store multiple grades. Like '2-5,9-12'. This may be changed so that there is a only a single grade value rather than a value for many grades. There are library functions to convert to and from this format into a hash/array variable. This will likely be changed since too complex and move to a single grade value in each record. `period` tinyint(4) DEFAULT NULL, - period of the closure for the grade / grades above. These values are based on the periods per day (PPD) value for each grade, set in attendance configuration. Thus if PPD was 2 for a grade 4 class and period 1 was closed, the school would be closed in the morning for that grade. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4; A new development table for online daybook. An activity would be a section of a curriculum and would link to the daybook data table. (dbkdata) -- Table structure for table `dbkactivity` CREATE TABLE `dbkactivity` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(32) DEFAULT NULL, `title` varchar(32) DEFAULT NULL, `description` text DEFAULT NULL, `startterm` tinyint(3) unsigned DEFAULT NULL, `endterm` tinyint(3) unsigned DEFAULT NULL, `acttype` varchar(32) DEFAULT NULL, `subjsec` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Another new development table for an online daybook. -- Table structure for table `dbkdata` CREATE TABLE `dbkdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, - userid of teacher. `date` date DEFAULT NULL, - date of the data. `subjsec` varchar(32) DEFAULT NULL, - course of the data `period` smallint(6) DEFAULT NULL, - period of the data `topic` varchar(255) DEFAULT NULL, - topic of data `notes` text DEFAULT NULL, - text notes/description `homework` varchar(255) DEFAULT NULL, - assigned homework. `hwdate` date DEFAULT NULL, - date homework due. `category` varchar(255) DEFAULT NULL, - category of the data in curriculum. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A new development table for the online daybook, to allow teachers to set their own timetable. Typically only high school teachers have scheduled periods. This will allow elementary/middle years teachers to set their timetable. -- Table structure for table `dbktimetable` CREATE TABLE `dbktimetable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, - teacher userid `term` tinyint(3) unsigned DEFAULT NULL, - teacher term. `day` tinyint(3) unsigned DEFAULT NULL, - day in cycle. `period` tinyint(3) unsigned DEFAULT NULL, - period in cycle `courseact` varchar(32) DEFAULT NULL, - course name/code or activity. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A discipline system table holding the actions to be done when an infraction is done. -- Table structure for table `disc_action` CREATE TABLE `disc_action` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - student number, of course `eventid` int(11) DEFAULT NULL, - link to event id of the disc_event table `action` text DEFAULT NULL, - description of the action to be done; from a list stored in the g_DiscAction variable in main configuration in discipline section. `date` date DEFAULT NULL, - date of the action to be done. `comment` text DEFAULT NULL, - comment on this action PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4; The main discipline table that stores the infraction done. This table is then linked to the action table (consequences, disc_action) and the students involved (disc_ident table). -- Table structure for table `disc_event` CREATE TABLE `disc_event` ( `id` int(11) NOT NULL AUTO_INCREMENT, `location` varchar(255) DEFAULT NULL, - where infraction happened. `date` date DEFAULT NULL, - date of infraction. not datetime since easier to query. `time` time DEFAULT NULL, - time of infraction. simplifies reporting/query `author` varchar(40) DEFAULT NULL, - typically the teacher reporting on the event. `infraction` varchar(255) DEFAULT NULL, - infraction category; stored in the configuration variable g_DiscInfraction in discipline section of main configuration. `description` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4; The discipline table linked to the main event table (disc_event) that stores the students linked to a discpline event. -- Table structure for table `disc_ident` CREATE TABLE `disc_ident` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `eventid` int(11) DEFAULT NULL, - id of event in the disc_event table. `private` char(1) DEFAULT NULL, - var to hide value from other staff, in case of teacher child, etc. `demerit` tinyint(3) unsigned DEFAULT NULL, - demerit value if using demerit system `comment` text DEFAULT NULL, - simply a description of child involvement, if necessary. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4; This table stores student marks/evaluations. It is the table storing course enrollments, also. One and the same. There is a record for each student in each course for each term (time period with report cards). -- Table structure for table `eval` CREATE TABLE `eval` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjcode` varchar(32) DEFAULT NULL, - the course/section code. subjsec in other tables. (8017-1) `studnum` varchar(32) DEFAULT NULL, - student number # Removed 2024. The `teacher` field. Teacher userid is in the course master for this course (subject table). `term` int(11) DEFAULT NULL, - the term for this course evaluation. # Removed 2024. `program` field unused. `comment` text DEFAULT NULL, - teacher comment for this student,course,term. `a1` varchar(12) DEFAULT NULL, - evaluation categories (20) set in the course master (subject table) `a2` varchar(12) DEFAULT NULL, `a3` varchar(12) DEFAULT NULL, `a4` varchar(12) DEFAULT NULL, `a5` varchar(12) DEFAULT NULL, `a6` varchar(12) DEFAULT NULL, `a7` varchar(12) DEFAULT NULL, `a8` varchar(12) DEFAULT NULL, `a9` varchar(12) DEFAULT NULL, `a10` varchar(12) DEFAULT NULL, `a11` varchar(12) DEFAULT NULL, `a12` varchar(12) DEFAULT NULL, `a13` varchar(12) DEFAULT NULL, `a14` varchar(12) DEFAULT NULL, `a15` varchar(12) DEFAULT NULL, `a16` varchar(12) DEFAULT NULL, `a17` varchar(12) DEFAULT NULL, `a18` varchar(12) DEFAULT NULL, `a19` varchar(12) DEFAULT NULL, `a20` varchar(12) DEFAULT NULL, PRIMARY KEY (`id`), KEY `eval_subjcode` (`subjcode`), KEY `eval_studnum` (`studnum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; An externally used table by moodle for courses. -- Table structure for table `ext_moodle` CREATE TABLE `ext_moodle` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjsec` varchar(32) DEFAULT NULL, - subjsec is course-section (8017-1) `subjcode` varchar(32) DEFAULT NULL, - course code only (8017) `studnum` int(11) DEFAULT NULL, - local student number `role` varchar(64) DEFAULT NULL, - role; can't remember! PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1774 DEFAULT CHARSET=utf8mb4; Early Year's Evaluation results. For entering students in PK/K grades to look for any development issues. -- Table structure for table `eyedata` CREATE TABLE `eyedata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `provnum` varchar(12) DEFAULT NULL, - provincial student number `birthdate` date DEFAULT NULL, - birthdate `tdate` date DEFAULT NULL, - testing date `tgrade` varchar(8) DEFAULT NULL, - testing grade `season` varchar(16) DEFAULT NULL, - season of year (spring,fall,summer) `evaltype` varchar(4) DEFAULT NULL, - special ed stuff `prepost` varchar(16) DEFAULT NULL, - typically always a post test. `score_ase` varchar(8) DEFAULT NULL, - requested special ed fields. not sure as a developer. `score_soc` varchar(8) DEFAULT NULL, `score_cog` varchar(8) DEFAULT NULL, `score_lang` varchar(8) DEFAULT NULL, `score_pdgross` varchar(8) DEFAULT NULL, `score_pdfine` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=230 DEFAULT CHARSET=utf8mb4; A Fee System table. A journal storing the charges/payments transactions. -- Table structure for table `fees_jrl` CREATE TABLE `fees_jrl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number. `trans_date` date DEFAULT NULL, - transaction date `trans_type` varchar(8) DEFAULT NULL, - transaction type: charge or payment. `name` varchar(255) DEFAULT NULL, - category of transaction. `description` text DEFAULT NULL, - description of transaction, if necessary `subtotal` decimal(10,2) DEFAULT NULL, - value of transaction. `tax1` decimal(10,2) DEFAULT NULL, - next fields are name and value of taxes. `tax1_name` varchar(16) DEFAULT NULL, `tax2` decimal(10,2) DEFAULT NULL, `tax2_name` varchar(16) DEFAULT NULL, `tax3` decimal(10,2) DEFAULT NULL, `tax3_name` varchar(16) DEFAULT NULL, `tax4` decimal(10,2) DEFAULT NULL, `tax4_name` varchar(16) DEFAULT NULL, `total` decimal(10,2) DEFAULT NULL, - value of transaction. `paid_id` int(11) DEFAULT NULL, - id of a matching charge this is a payment for. `receipt` int(11) DEFAULT NULL, - receipt number. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A Fee System table. This holds predefined charges to speed up the process of entering transaction (typicall charges). -- Table structure for table `fees_predefined` CREATE TABLE `fees_predefined` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, - name of xaction matches name in fees_jrl table. `description` varchar(255) DEFAULT NULL, - matches description in fees_jrl table. `amount` double DEFAULT NULL, - value of xaction. `discount` double DEFAULT NULL, - any applied discount. `tax1_flag` char(1) DEFAULT NULL, - taxes to be applied. `tax2_flag` char(1) DEFAULT NULL, `tax3_flag` char(1) DEFAULT NULL, `tax4_flag` char(1) DEFAULT NULL, `group_name` varchar(32) DEFAULT NULL, - category of predefined xactions `group_value` varchar(255) DEFAULT NULL, - group name desc for predefined xactions (transactions) PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4; A Gradebook table holding scores in evaluations in gradebook. Linked to the gbtest table. -- Table structure for table `gbscore` CREATE TABLE `gbscore` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `studnum` mediumint(8) unsigned DEFAULT NULL, - local student number. `testid` int(10) unsigned DEFAULT NULL, - link to the id of the test in gbtest table. `score` varchar(16) DEFAULT NULL, - raw score value. `comment` varchar(255) DEFAULT NULL, - comment on the score PRIMARY KEY (`id`), UNIQUE KEY `studtestidx` (`studnum`,`testid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A Gradebook table holding the test/evaluation information. The weight and group is used to calculate its contribution to the group it's in and then from the schema we get the group contribution to the overall student average. We only store raw scores in the gbscore table, and using the score value in this table (gbtest) (really maxscore), we can calculate the percent value for this test. -- Table structure for table `gbtest` CREATE TABLE `gbtest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `subjsec` varchar(32) DEFAULT NULL, - course code-section (8017-1) `name` varchar(32) DEFAULT NULL, - text name of the eval `description` varchar(255) DEFAULT NULL, - description of the eval. `tdate` date DEFAULT NULL, - date of test; orders the tests. `score` varchar(8) DEFAULT NULL, - maximum score for the test. `weight` mediumint(8) unsigned DEFAULT NULL, - the weighting value for this test, typically 500. `grp` varchar(32) DEFAULT NULL, - the group of evals this test belongs to (ie. tests, homework, etc.) PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4; A table to track homevisits by staff members. -- Table structure for table `homevisit` CREATE TABLE `homevisit` ( `id` int(11) NOT NULL AUTO_INCREMENT, `author` varchar(64) DEFAULT NULL, - staff member userid. `date` date DEFAULT NULL, - date of visit `studnum` int(11) DEFAULT NULL, - local student number. `reason` varchar(255) DEFAULT NULL, - reason for the visit. Configured in var g_HomeVisitReason `description` text DEFAULT NULL, - description of the visit. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; This a local school level IEP (Individual Education Program). We have a separate stand alone IEP for all our associated schools. If not associated, they can then use this school level system. This table is stored evaluations (up to 32) based on values in the iep_evalmst table. Similar to the eval table for normal report card marks. -- Table structure for table `iep_eval` CREATE TABLE `iep_eval` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `subnum` int(11) DEFAULT NULL, - local "course" (such as Gross Motor Skills, Fine Motor skills, etc. `obj1` varchar(255) DEFAULT NULL, `obj2` varchar(255) DEFAULT NULL, `obj3` varchar(255) DEFAULT NULL, `obj4` varchar(255) DEFAULT NULL, `obj5` varchar(255) DEFAULT NULL, `obj6` varchar(255) DEFAULT NULL, `obj7` varchar(255) DEFAULT NULL, `obj8` varchar(255) DEFAULT NULL, `obj9` varchar(255) DEFAULT NULL, `obj10` varchar(255) DEFAULT NULL, `obj11` varchar(255) DEFAULT NULL, `obj12` varchar(255) DEFAULT NULL, `obj13` varchar(255) DEFAULT NULL, `obj14` varchar(255) DEFAULT NULL, `obj15` varchar(255) DEFAULT NULL, `obj16` varchar(255) DEFAULT NULL, `obj17` varchar(255) DEFAULT NULL, `obj18` varchar(255) DEFAULT NULL, `obj19` varchar(255) DEFAULT NULL, `obj20` varchar(255) DEFAULT NULL, `obj21` varchar(255) DEFAULT NULL, `obj22` varchar(255) DEFAULT NULL, `obj23` varchar(255) DEFAULT NULL, `obj24` varchar(255) DEFAULT NULL, `obj25` varchar(255) DEFAULT NULL, `obj26` varchar(255) DEFAULT NULL, `obj27` varchar(255) DEFAULT NULL, `obj28` varchar(255) DEFAULT NULL, `obj29` varchar(255) DEFAULT NULL, `obj30` varchar(255) DEFAULT NULL, `obj31` varchar(255) DEFAULT NULL, `obj32` varchar(255) DEFAULT NULL, `term` int(11) DEFAULT NULL, - term of the evaluation. `comment` text DEFAULT NULL, - comment by the teacher. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; Evaluation Master table similar to the subject table (ie. course master). Contains the objectives that are evaluated. (ie. Stays on Task, etc.) -- Table structure for table `iep_evalmst` CREATE TABLE `iep_evalmst` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `subnum` int(11) DEFAULT NULL, `obj1` text DEFAULT NULL, `obj2` text DEFAULT NULL, `obj3` text DEFAULT NULL, `obj4` text DEFAULT NULL, `obj5` text DEFAULT NULL, `obj6` text DEFAULT NULL, `obj7` text DEFAULT NULL, `obj8` text DEFAULT NULL, `obj9` text DEFAULT NULL, `obj10` text DEFAULT NULL, `obj11` text DEFAULT NULL, `obj12` text DEFAULT NULL, `obj13` text DEFAULT NULL, `obj14` text DEFAULT NULL, `obj15` text DEFAULT NULL, `obj16` text DEFAULT NULL, `obj17` text DEFAULT NULL, `obj18` text DEFAULT NULL, `obj19` text DEFAULT NULL, `obj20` text DEFAULT NULL, `obj21` text DEFAULT NULL, `obj22` text DEFAULT NULL, `obj23` text DEFAULT NULL, `obj24` text DEFAULT NULL, `obj25` text DEFAULT NULL, `obj26` text DEFAULT NULL, `obj27` text DEFAULT NULL, `obj28` text DEFAULT NULL, `obj29` text DEFAULT NULL, `obj30` text DEFAULT NULL, `obj31` text DEFAULT NULL, `obj32` text DEFAULT NULL, `strategy` text DEFAULT NULL, `responsible` text DEFAULT NULL, `longtermobjective` text DEFAULT NULL, `impactarea` text DEFAULT NULL, `targetskill` text DEFAULT NULL, `currperform` text DEFAULT NULL, `evalmethod` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=107 DEFAULT CHARSET=utf8mb4; IEP Objectives based on category number, and category (from our main IEP system) -- Table structure for table `iep_objectives` CREATE TABLE `iep_objectives` ( `id` int(11) NOT NULL AUTO_INCREMENT, `catnum` int(11) DEFAULT NULL, `category` varchar(255) DEFAULT NULL, `idnum` int(11) DEFAULT NULL, - from main iep system. `description` text DEFAULT NULL, - local created course values `author` varchar(40) DEFAULT NULL, - author of the local created course `cdate` date DEFAULT NULL, - creation date PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3033 DEFAULT CHARSET=utf8mb4; The student information (additional to his/her demographics info in student table). -- Table structure for table `iep_student` CREATE TABLE `iep_student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(40) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `birthdate` date DEFAULT NULL, `studnum` int(11) DEFAULT NULL, - local student number `provnum` varchar(12) DEFAULT NULL, - provincial number `desdate` date DEFAULT NULL, - designation date `ddpflvl` varchar(12) DEFAULT NULL, - ddpf level `designation` varchar(60) DEFAULT NULL, - designation `grade` varchar(6) DEFAULT NULL, `school` varchar(60) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `medical` text DEFAULT NULL, `medication` text DEFAULT NULL, `equip` text DEFAULT NULL, - equipment `adapt` text DEFAULT NULL, - any adaptations `history` text DEFAULT NULL, `strengths` text DEFAULT NULL, `challenges` text DEFAULT NULL, `assessments` text DEFAULT NULL, `additional` text DEFAULT NULL, `schoolyear` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; The members of the team assigned to the student. -- Table structure for table `iep_team` CREATE TABLE `iep_team` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `userid` char(128) DEFAULT NULL, - userid of team member; `jobtitle` char(40) DEFAULT NULL, - job title: classroom Teacher, Special Ed, etc. `phone` char(20) DEFAULT NULL, - phone number `email` char(40) DEFAULT NULL, - contact email `sign` char(1) DEFAULT NULL, - signing authority on the student's IEP document. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The LINT program (LLI - Literacy Intervention) is a pull out program for low readers. It assesses literacy improvement -- Table structure for table `lint_program` CREATE TABLE `lint_program` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, - userid of the teacher. `groupdesc` varchar(40) DEFAULT NULL, - description of the program `startdate` date DEFAULT NULL, - start date `enddate` date DEFAULT NULL, - end date `programhours` varchar(16) DEFAULT NULL, - hours of the program to gauge efficiency. `periodlength` varchar(4) DEFAULT NULL, - how long each period of instruction is. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4; -- Table structure for table `lint_student` CREATE TABLE `lint_student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `progid` int(11) DEFAULT NULL, - link to id of the program in lint_program `startdate` date DEFAULT NULL, - start date for this student. `enddate` date DEFAULT NULL, - end date for this student `programhours` varchar(16) DEFAULT NULL, - hours this student was in the program. `comment` text DEFAULT NULL, - comment `author` varchar(40) DEFAULT NULL, - author of the comments `attendance` varchar(4) DEFAULT NULL, - attendance in the program `initialkit` varchar(32) DEFAULT NULL, - reading kit used `initiallesson` varchar(8) DEFAULT NULL, - starting location in the kit `withdrawtype` varchar(32) DEFAULT NULL, - type of withdrawal if any; reason why PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=193 DEFAULT CHARSET=utf8mb4; Common Math Program - expected outcomes; provincial math curriculum -- Table structure for table `mathca_outcomes` CREATE TABLE `mathca_outcomes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `oid` varchar(8) DEFAULT NULL, - object ID `odesc` varchar(255) DEFAULT NULL, - object description `grade` varchar(8) DEFAULT NULL, - grade level for this outcome PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=293 DEFAULT CHARSET=utf8mb4; Math Scores in outcomes, defined above in table mathca_outcomes -- Table structure for table `mathca_scores` CREATE TABLE `mathca_scores` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `treatynum` varchar(16) DEFAULT NULL, - treaty number `tdate` date DEFAULT NULL, - test date `tauthor` varchar(40) DEFAULT NULL, - test author (teacher userid) `tgrade` varchar(8) DEFAULT NULL, - test grade (student grade during test) `tage` varchar(16) DEFAULT NULL, - student age `tstamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `prepost` varchar(16) DEFAULT NULL, - pretest or post test `outcome` varchar(16) DEFAULT NULL, - outcome defined above in mathca_outcomes `score` changed summer, 2024 from smallint(6) to a varchar(6) to allow for decimal values in the 1-4 scores on the math test. `schoolyear` char(4) DEFAULT NULL, - school year end ( 2023-2024 = 2024) PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A metadata table for a variety of other tables. (student, staff, staff_multi,student_inac, announce) There is a meta entry library to use for form entry that uses the meta table. -- Table structure for table `meta` CREATE TABLE `meta` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dbaseid` varchar(64) DEFAULT NULL, - redundant; unused. all say 'school' `tableid` varchar(64) DEFAULT NULL, - table name `fieldid` varchar(64) DEFAULT NULL, - field name `datatype` varchar(16) DEFAULT NULL, - type of field (char, number, etc.) `arrayidx` smallint(5) unsigned DEFAULT NULL, - size of variable `size` smallint(5) unsigned DEFAULT NULL, - size of variable `fieldname` varchar(255) DEFAULT NULL, - descriptive name used in forms for this field `defaultvalue` text DEFAULT NULL, - any default value(s) set for this field `formtype` varchar(255) DEFAULT NULL, - type for form entry: text, selecthash, select, checkbox, textarea, readonly `viewsize` varchar(255) DEFAULT NULL, - size of a form entry window for this field `required` char(1) DEFAULT NULL, - set a field as required in form entry `description` text DEFAULT NULL, - descriptive text to go with entry of values into this field. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; MySchoolSask Temporary tables (updated nightly) - storing information from the Fujitsu Aspen system. The 'mssid' value is the pupilid in Aspen, which is the local student number for this application. It is analogous to the 'studnum' field used in OA. # Current courses. The ones of interest are blank in date, mark, credit fields, since they will be current courses without any of those values yet. However, they may be earlier year courses that were failed. CREATE TABLE `mss_currcourse` ( `id` int(11) NOT NULL AUTO_INCREMENT, - unique id for each record in the table. `mssid` varchar(12) DEFAULT NULL, - Aspen student id. `coursecode` varchar(12) DEFAULT NULL, - Sask Ed course code, 4 digit. `integrationid` varchar(32) DEFAULT NULL, - a value for a group of records. Utility unknown. `date` date DEFAULT NULL, - date for end of course. `mark` varchar(8) DEFAULT NULL, - mark for the class, 1-100 `credit` varchar(4) DEFAULT NULL, - credit for the class, 1 PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; # Student Information from MSS. Legal may be blank if other fields are also legal. CREATE TABLE `mss_student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mssid` varchar(12) DEFAULT NULL, `provnum` varchar(12) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `lastname` varchar(40) DEFAULT NULL, `birthdate` date DEFAULT NULL, `grade` varchar(8) DEFAULT NULL, `legal_lastname` varchar(40) DEFAULT NULL, `legal_firstname` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; # Information to incorporate into the sasked_completedcourses, if new/updated data. CREATE TABLE `mss_transcript` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mssid` varchar(12) DEFAULT NULL, `coursecode` varchar(12) DEFAULT NULL, - four digit sasked course code `integrationid` varchar(32) DEFAULT NULL, - not unique `date` date DEFAULT NULL, - end date of course `mark` varchar(8) DEFAULT NULL, `credit` varchar(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; A table that stores any updates to Open Admin. Values are set using a script in the powertools on the central site, and will display on the main page of admin site on all schools. -- Table structure for table `oaupdate` CREATE TABLE `oaupdate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(127) DEFAULT NULL, - title of the update `description` text DEFAULT NULL, - description of update `date` date DEFAULT NULL, - date of update PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=195 DEFAULT CHARSET=utf8mb4; Table defining start and end times of a period for teacher daybook. Other period info is stored in the configuration system. -- Table structure for table `period_data` CREATE TABLE `period_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `timeset` smallint(5) unsigned DEFAULT NULL, `period` tinyint(3) unsigned DEFAULT NULL, `starttime` time DEFAULT NULL, `endtime` time DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- Table structure for table `period_map` CREATE TABLE `period_map` ( `id` int(11) NOT NULL AUTO_INCREMENT, `timeset` smallint(5) unsigned DEFAULT NULL, `timetype` varchar(32) DEFAULT NULL, `timeval` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Stores homework set by the teacher. visible on parent site so they can see what is assigned. -- Table structure for table `plan_homework` CREATE TABLE `plan_homework` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjsec` varchar(32) DEFAULT NULL, - course of homework `teacher` varchar(40) DEFAULT NULL, - teacher of the course (redundant) `duedatetime` datetime DEFAULT NULL, - due date/time of the homework `currentdatetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `topic` varchar(255) DEFAULT NULL, - topic of homework `description` text DEFAULT NULL, - description of the assignment PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Preplanning tables for planning course offerings in the upcoming year. A course master table for upcoming year. What courses are offered and who is teaching what. -- Table structure for table `preplan_coursemaster` CREATE TABLE `preplan_coursemaster` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjcode` varchar(32) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `grade` varchar(8) DEFAULT NULL, `teacher` varchar(255) DEFAULT NULL, `section` varchar(32) DEFAULT NULL, `subjsec` varchar(32) DEFAULT NULL, `startterm` tinyint(3) unsigned DEFAULT NULL, `endterm` tinyint(3) unsigned DEFAULT NULL, `offeredto` varchar(8) DEFAULT NULL, `backedwith` varchar(32) DEFAULT NULL, `semester` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- Table structure for table `preplan_courseperiods` CREATE TABLE `preplan_courseperiods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjsec` varchar(32) DEFAULT NULL, `semester` varchar(8) DEFAULT NULL, `periods` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table to store student preregistrations for the upcoming year. Typically Kindergarten students. This table matches the structure of student table (current students) and studentwd (withdrawn students). See the student table for descriptions. -- Table structure for table `prereg` CREATE TABLE `prereg` ( `studid` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(40) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `initial` varchar(40) DEFAULT NULL, `alias_lastname` varchar(40) DEFAULT NULL, `alias_firstname` varchar(40) DEFAULT NULL, `legal_lastname` varchar(40) DEFAULT NULL, `legal_firstname` varchar(40) DEFAULT NULL, `studnum` int(11) DEFAULT NULL, `grade` varchar(8) DEFAULT NULL, `homeroom` varchar(8) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `birthdate` date DEFAULT NULL, `house` varchar(20) DEFAULT NULL, `healthid` varchar(12) DEFAULT NULL, `entry` varchar(255) DEFAULT NULL, `ethnic` varchar(20) DEFAULT NULL, `single` char(1) DEFAULT NULL, `internet` char(1) DEFAULT NULL, `youngest` char(1) DEFAULT NULL, `owing` decimal(9,2) DEFAULT NULL, `treaty` varchar(20) DEFAULT NULL, `medical` mediumtext DEFAULT NULL, `studrel` varchar(25) DEFAULT NULL, `momrel` varchar(25) DEFAULT NULL, `dadrel` varchar(25) DEFAULT NULL, `parish` varchar(255) DEFAULT NULL, `commun` char(1) DEFAULT NULL, `recon` char(1) DEFAULT NULL, `confirm` char(1) DEFAULT NULL, `maiden` varchar(40) DEFAULT NULL, `relation1` varchar(20) DEFAULT NULL, `contact1` varchar(255) DEFAULT NULL, `name1` varchar(255) DEFAULT NULL, `hphone1` varchar(64) DEFAULT NULL, `wphone1` varchar(64) DEFAULT NULL, `cell1` varchar(64) DEFAULT NULL, `fax1` varchar(64) DEFAULT NULL, `address1` varchar(40) DEFAULT NULL, `city1` varchar(40) DEFAULT NULL, `pcode1` varchar(10) DEFAULT NULL, `workplace1` varchar(60) DEFAULT NULL, `relation2` varchar(20) DEFAULT NULL, `contact2` varchar(255) DEFAULT NULL, `name2` varchar(255) DEFAULT NULL, `hphone2` varchar(64) DEFAULT NULL, `wphone2` varchar(64) DEFAULT NULL, `cell2` varchar(64) DEFAULT NULL, `fax2` varchar(64) DEFAULT NULL, `address2` varchar(40) DEFAULT NULL, `city2` varchar(40) DEFAULT NULL, `pcode2` varchar(10) DEFAULT NULL, `workplace2` varchar(60) DEFAULT NULL, `emrelation` varchar(20) DEFAULT NULL, `emname` varchar(255) DEFAULT NULL, `emhphone` varchar(64) DEFAULT NULL, `emwphone` varchar(64) DEFAULT NULL, `emcell` varchar(64) DEFAULT NULL, `doctor` varchar(70) DEFAULT NULL, `docphone` varchar(15) DEFAULT NULL, `busroute` varchar(30) DEFAULT NULL, `provnum` varchar(12) DEFAULT NULL, `band` varchar(64) DEFAULT NULL, `bandnum` varchar(32) DEFAULT NULL, `baptism` char(1) DEFAULT NULL, `contact` varchar(255) DEFAULT NULL, `family` varchar(32) DEFAULT NULL, `pic` varchar(255) DEFAULT NULL, `password` varchar(32) DEFAULT NULL, `field1` text DEFAULT NULL, `field2` text DEFAULT NULL, `field3` text DEFAULT NULL, `field4` text DEFAULT NULL, `field5` text DEFAULT NULL, `field6` text DEFAULT NULL, `field7` text DEFAULT NULL, `field8` text DEFAULT NULL, `prov1` varchar(32) DEFAULT NULL, `prov2` varchar(32) DEFAULT NULL, `reserveres` varchar(64) DEFAULT NULL, `program` varchar(64) DEFAULT NULL, `utag` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `trans_type` varchar(255) DEFAULT NULL, `trans_name` varchar(255) DEFAULT NULL, `trans_am_route` varchar(64) DEFAULT NULL, `trans_pickup_time` varchar(16) DEFAULT NULL, `trans_pm_route` varchar(64) DEFAULT NULL, `trans_dropoff_time` varchar(16) DEFAULT NULL, `trans_bus_stop_location` varchar(64) DEFAULT NULL, `group_after_school_prog` char(1) DEFAULT NULL, `lunchstatus` varchar(255) DEFAULT NULL, `par1_email` varchar(64) DEFAULT NULL, `par2_email` varchar(64) DEFAULT NULL, `name_prefix` varchar(64) DEFAULT NULL, `name_suffix` varchar(64) DEFAULT NULL, `group_alt1` varchar(64) DEFAULT NULL, `group_alt2` varchar(64) DEFAULT NULL, `group_alt3` varchar(64) DEFAULT NULL, `billet` varchar(255) DEFAULT NULL, `childcare_name` varchar(255) DEFAULT NULL, `childcare_address` varchar(255) DEFAULT NULL, `childcare_phone1` varchar(255) DEFAULT NULL, `childcare_phone2` varchar(255) DEFAULT NULL, `internet_type` varchar(255) DEFAULT NULL, `par1_firstname` varchar(255) DEFAULT NULL, `par1_lastname` varchar(255) DEFAULT NULL, `par2_firstname` varchar(255) DEFAULT NULL, `par2_lastname` varchar(255) DEFAULT NULL, `graddate` date DEFAULT NULL, `address_land` varchar(32) DEFAULT NULL, `tuition_status` varchar(64) DEFAULT NULL, `tuition_duration` varchar(64) DEFAULT NULL, `tuition_program` varchar(128) DEFAULT NULL, `tuition_collect` tinyint(4) DEFAULT NULL, `tuition_country` varchar(8) DEFAULT NULL, `immersion_type` varchar(32) DEFAULT NULL, `cumfolder` tinyint(4) DEFAULT NULL, `address1mailing` varchar(128) DEFAULT NULL, `address2mailing` varchar(128) DEFAULT NULL, `parent_lockout` varchar(8) DEFAULT NULL, PRIMARY KEY (`studid`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4; -- Table structure for table `prereg_staff` A table for planning staff for the upcoming year. A clone of the staff table. CREATE TABLE `prereg_staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sal` varchar(6) DEFAULT NULL, `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `middlename` varchar(40) DEFAULT NULL, `userid` varchar(40) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `emailwork` varchar(255) DEFAULT NULL, `emailpersonal` varchar(255) DEFAULT NULL, `tdate` date DEFAULT NULL, `doatt` char(1) DEFAULT NULL, `certification1` varchar(255) DEFAULT NULL, `home_phone` varchar(64) DEFAULT NULL, `cell_phone` varchar(64) DEFAULT NULL, `street` varchar(64) DEFAULT NULL, `city` varchar(32) DEFAULT NULL, `prov` varchar(32) DEFAULT NULL, `pcode` varchar(32) DEFAULT NULL, `emergency_contact_name` varchar(255) DEFAULT NULL, `emergency_contact_phone` varchar(255) DEFAULT NULL, `driver_license` varchar(64) DEFAULT NULL, `driver_class` varchar(64) DEFAULT NULL, `vehicle_reg` varchar(64) DEFAULT NULL, `vehicle_plate` varchar(64) DEFAULT NULL, `citizenship` varchar(64) DEFAULT NULL, `passport` varchar(64) DEFAULT NULL, `alt_street` varchar(64) DEFAULT NULL, `alt_city` varchar(32) DEFAULT NULL, `alt_prov` varchar(32) DEFAULT NULL, `alt_country` varchar(64) DEFAULT NULL, `alt_pcode` varchar(32) DEFAULT NULL, `alt_phone` varchar(64) DEFAULT NULL, `alt_email` varchar(64) DEFAULT NULL, `certification2` varchar(255) DEFAULT NULL, `certification3` varchar(255) DEFAULT NULL, `certification4` varchar(255) DEFAULT NULL, `certification5` varchar(255) DEFAULT NULL, `certification6` varchar(255) DEFAULT NULL, `certification7` varchar(255) DEFAULT NULL, `certification8` varchar(255) DEFAULT NULL, `teachingcertificatestatus` varchar(16) DEFAULT NULL, `teachingcertificatejurisdiction` varchar(16) DEFAULT NULL, `ancestry` varchar(16) DEFAULT NULL, `indianstatus` varchar(16) DEFAULT NULL, `gender` varchar(16) DEFAULT NULL, `birthdate` date DEFAULT NULL, `fulltimeequivalent` varchar(16) DEFAULT NULL, `occupations` text DEFAULT NULL, `nrskip` char(1) DEFAULT NULL, `extrafield1` varchar(255) DEFAULT NULL, `extrafield2` varchar(255) DEFAULT NULL, `extrafield3` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table for planning staff for upcoming school year. Homerooms, etc. A clone of the staff_multi table. -- Table structure for table `prereg_staff_multi` CREATE TABLE `prereg_staff_multi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(32) DEFAULT NULL, `field_name` varchar(255) DEFAULT NULL, `field_value` varchar(255) DEFAULT NULL, `field_additional` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table storing students on a waiting list. -- Table structure for table `prereg_waitlist` CREATE TABLE `prereg_waitlist` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, `enroldate` date DEFAULT NULL, `waitnumber` int(11) DEFAULT NULL, `description` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table part of the preplanning system planning for the upcoming school year. This will set what homeroom, grade the student will be placed in. -- Table structure for table `preset` CREATE TABLE `preset` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lastname` char(40) DEFAULT NULL, `firstname` char(40) DEFAULT NULL, `initial` char(2) DEFAULT NULL, `studnum` int(11) DEFAULT NULL, `grade` char(2) DEFAULT NULL, `homeroom` char(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A Principal Walkthrough evaluation table. Walkthroughs are used for teacher evaluation but are simply very short periods (ie. 5 minutes) when principal is in classrooms to get the 'pulse' of the classroom. The master table with a variety of walkthrough evaluation types of things to watch for in classrooms. Created by Byron Merkosky. -- Table structure for table `pwalk_mst` CREATE TABLE `pwalk_mst` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, `category` varchar(64) DEFAULT NULL, `identcode` varchar(64) DEFAULT NULL, `cdate` date DEFAULT NULL, `sequence` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4; Template table linked to the master for a variety of evaluations by category/desc. -- Table structure for table `pwalk_tpl` CREATE TABLE `pwalk_tpl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mstid` int(11) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `category` varchar(64) DEFAULT NULL, `sequence` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=293 DEFAULT CHARSET=utf8mb4; The evaluation table for a walkthrough for a teacher. -- Table structure for table `pwalk_eval` CREATE TABLE `pwalk_eval` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, `authorid` varchar(40) DEFAULT NULL, `tuserid` varchar(40) DEFAULT NULL, `tlastname` varchar(60) DEFAULT NULL, `tfirstname` varchar(40) DEFAULT NULL, `tcertnum` varchar(32) DEFAULT NULL, `subject` varchar(60) DEFAULT NULL, `adatetime` datetime DEFAULT NULL, `comment` text DEFAULT NULL, `continueedit` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; Scoring of the teacher in the walkthrough -- Table structure for table `pwalk_score` CREATE TABLE `pwalk_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mstid` int(11) DEFAULT NULL, - link to the master type of the walkthrough. `description` varchar(255) DEFAULT NULL, - desc found in template table for this walkthrough `category` varchar(64) DEFAULT NULL, - category that matches the template value for this master. `sequence` int(11) DEFAULT NULL, `score` tinyint(3) unsigned DEFAULT NULL, `strengths` text DEFAULT NULL, `weaknesses` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4; The following tables are used by the reading system. The first 2 (read_level_dra2, read_level_dra2) store the information required by system for each reading level including a category, a name, and a sequence or ordering within that category -- Table structure for table `read_level_dra2` CREATE TABLE `read_level_dra2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `readlevel` varchar(4) DEFAULT NULL, `category` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `seq` int(11) DEFAULT NULL, `bktype` varchar(16) DEFAULT NULL, - fiction or nonfiction `help1` text DEFAULT NULL, - helpful text to resources `help2` text DEFAULT NULL, - more helpful text `help3` text DEFAULT NULL, - currently unused PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=413 DEFAULT CHARSET=utf8mb4; -- Table structure for table `read_level_dra3` CREATE TABLE `read_level_dra3` ( - same as the dra2 table. `id` int(11) NOT NULL AUTO_INCREMENT, `readlevel` varchar(4) DEFAULT NULL, `category` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `seq` int(11) DEFAULT NULL, `bktype` varchar(16) DEFAULT NULL, `help1` text DEFAULT NULL, `help2` text DEFAULT NULL, `help3` text DEFAULT NULL - unused as above PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=609 DEFAULT CHARSET=utf8mb4; The table storing key reading test information, such as the student, reading level, and date, etc. -- Table structure for table `read_test` CREATE TABLE `read_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number `readlevel` varchar(4) DEFAULT NULL, - reading level `tdate` date DEFAULT NULL, - test date `tauthor` varchar(40) DEFAULT NULL, - test teacher userid `tgrade` varchar(8) DEFAULT NULL, - test grade of student `tstamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `season` varchar(16) DEFAULT NULL, - season (fall, spring or summer) `bktype` varchar(16) DEFAULT NULL, - fiction or nonfiction `dratype` tinyint(3) unsigned DEFAULT NULL, - dra level; either a 2 or a 3. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2220 DEFAULT CHARSET=utf8mb4; Student reading test scores (more than 1) linked to a reading test id -- Table structure for table `read_test_score` CREATE TABLE `read_test_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `testid` int(11) DEFAULT NULL, - id of the read_test table record. `category` text DEFAULT NULL, - the category,name, seq (from read_level_draX table) `name` text DEFAULT NULL, - `seq` int(11) DEFAULT NULL, - `score` char(4) DEFAULT NULL, - the score value PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=26425 DEFAULT CHARSET=utf8mb4; This table stores the results of all courses passed with credit from grades 10-12. It is used to show the student progress to senior matriculation (ie. graduation from high school) We currently pull this data nightly from Sask Education. -- Table structure for table `sasked_completedcourses` CREATE TABLE `sasked_completedcourses` ( `id` int(11) NOT NULL AUTO_INCREMENT, `provnum` int(11) DEFAULT NULL, `birthdate` date DEFAULT NULL, `schoolid` int(11) DEFAULT NULL, `schoolname` varchar(255) DEFAULT NULL, `courseid` varchar(16) DEFAULT NULL, `coursetitle` varchar(255) DEFAULT NULL, `coursetype` varchar(32) DEFAULT NULL, `schoolyear` varchar(16) DEFAULT NULL, `courseenddate` date DEFAULT NULL, `finalmark` varchar(4) DEFAULT NULL, `creditsearned` varchar(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4; a list of all Saskatchewan Education courses. -- Table structure for table `sasked_courses` CREATE TABLE `sasked_courses` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `code` varchar(16) DEFAULT NULL, `startdate` date DEFAULT NULL, `enddate` date DEFAULT NULL, `ctype` varchar(32) DEFAULT NULL, `grade` varchar(4) DEFAULT NULL, `category` varchar(32) DEFAULT NULL, `prereg` varchar(255) DEFAULT NULL, `cgroup` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=17968 DEFAULT CHARSET=utf8mb4; A table storing all student/teacher timetable information. -- Table structure for table `schedat` CREATE TABLE `schedat` ( `id` int(11) NOT NULL AUTO_INCREMENT, `day` tinyint(4) DEFAULT NULL, - day in cycle. `period` tinyint(4) DEFAULT NULL, `subjsec` varchar(32) DEFAULT NULL, `term` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table storing any school events that are then used to create a monthly report for school activies that could then be included in newsletters, etc. -- Table structure for table `schoolevent` CREATE TABLE `schoolevent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `communityevents` text DEFAULT NULL, `staffupdates` text DEFAULT NULL, `dataupdates` text DEFAULT NULL, `resourcespending` text DEFAULT NULL, `facilityupdates` text DEFAULT NULL, `otherupdates` text DEFAULT NULL, `upcomingevents` text DEFAULT NULL, `landedu` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; A table storing teacher login sessions on the teacher site. -- Table structure for table `sessions` CREATE TABLE `sessions` ( `id` varchar(32) NOT NULL DEFAULT '', `a_session` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Stores ssp (Student Success Program) exceptions when a student missed an evaluation in reading or math and the reason whey. -- Table structure for table `ssp_exceptions` CREATE TABLE `ssp_exceptions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, `treatynum` varchar(16) DEFAULT NULL, `ssptype` varchar(16) DEFAULT NULL, `tdate` date DEFAULT NULL, `tauthor` varchar(40) DEFAULT NULL, `tgrade` varchar(8) DEFAULT NULL, `tage` varchar(16) DEFAULT NULL, `tseason` varchar(16) DEFAULT NULL, `tstamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `reasoncode` varchar(32) DEFAULT NULL, `reasonother` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The main staff table storing all active staff and their names, userid, etc. -- Table structure for table `staff` CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sal` varchar(6) DEFAULT NULL, - salutation (Mr. Ms, Mrs.) `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `middlename` varchar(40) DEFAULT NULL, `userid` varchar(40) DEFAULT NULL, - userid. Core user identifier like student studnum. `password` varchar(255) DEFAULT NULL, - password for teacher site access `emailwork` varchar(255) DEFAULT NULL, `emailpersonal` varchar(255) DEFAULT NULL, `tdate` date DEFAULT NULL, `doatt` char(1) DEFAULT NULL, - does attendance. Not really used now. `certification1` varchar(255) DEFAULT NULL, - primary teacher certification number. `home_phone` varchar(64) DEFAULT NULL, - phone, address info below `cell_phone` varchar(64) DEFAULT NULL, `street` varchar(64) DEFAULT NULL, `city` varchar(32) DEFAULT NULL, `prov` varchar(32) DEFAULT NULL, `pcode` varchar(32) DEFAULT NULL, `emergency_contact_name` varchar(255) DEFAULT NULL, `emergency_contact_phone` varchar(255) DEFAULT NULL, `driver_license` varchar(64) DEFAULT NULL, `driver_class` varchar(64) DEFAULT NULL, - `vehicle_reg` varchar(64) DEFAULT NULL, `vehicle_plate` varchar(64) DEFAULT NULL, `citizenship` varchar(64) DEFAULT NULL, `passport` varchar(64) DEFAULT NULL, - passport number `alt_street` varchar(64) DEFAULT NULL, - alternate address, if only during weekdays, etc. `alt_city` varchar(32) DEFAULT NULL, `alt_prov` varchar(32) DEFAULT NULL, `alt_country` varchar(64) DEFAULT NULL, `alt_pcode` varchar(32) DEFAULT NULL, `alt_phone` varchar(64) DEFAULT NULL, `alt_email` varchar(64) DEFAULT NULL, `certification2` varchar(255) DEFAULT NULL, - alternate certifications `certification3` varchar(255) DEFAULT NULL, `certification4` varchar(255) DEFAULT NULL, `certification5` varchar(255) DEFAULT NULL, `certification6` varchar(255) DEFAULT NULL, `certification7` varchar(255) DEFAULT NULL, `certification8` varchar(255) DEFAULT NULL, `teachingcertificatestatus` varchar(16) DEFAULT NULL, - status of certification: temporary, pending `teachingcertificatejurisdiction` varchar(16) DEFAULT NULL, - provincial, state jurisdiction `ancestry` varchar(16) DEFAULT NULL, `indianstatus` varchar(16) DEFAULT NULL, - status FN, metis, etc. `gender` varchar(16) DEFAULT NULL, `birthdate` date DEFAULT NULL, `fulltimeequivalent` varchar(16) DEFAULT NULL, - full time teacher, half time, etc. `occupations` text DEFAULT NULL, `nrskip` char(1) DEFAULT NULL, - nominal roll skip; for non-teaching staff `extrafield1` varchar(255) DEFAULT NULL, - additional fields, controlled by meta table. `extrafield2` varchar(255) DEFAULT NULL, `extrafield3` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Another staff table for teachers with multiple records for homeroom, staff position, grade -- Table structure for table `staff_multi` CREATE TABLE `staff_multi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(32) DEFAULT NULL, `field_name` varchar(255) DEFAULT NULL, - field name: homeroom, position, etc. `field_value` varchar(255) DEFAULT NULL, - field value `field_additional` varchar(255) DEFAULT NULL, - unused so far. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=542 DEFAULT CHARSET=utf8mb4; A table tracking staff absences. -- Table structure for table `staff_absent` CREATE TABLE `staff_absent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, - staff userid. `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `adate` date DEFAULT NULL, - absent date `reason` varchar(32) DEFAULT NULL, - reason for absence; configured list from g_StaffAbsReason `daypart` varchar(32) DEFAULT NULL, - part of the day missed; AM/PM/All day `other` varchar(255) DEFAULT NULL, - other reason `late` varchar(32) DEFAULT NULL, - minutes late for work `comment` text DEFAULT NULL, - text comment. PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2931 DEFAULT CHARSET=utf8mb4; A more complex system for tracking staff leaves and hours for payment purposes (other than the staff_absent table). Configuration in pay_LeaveAreas for predefined leave categories. (ie. Sick, Bereavement, Family, etc) This table stores the settings for the year. -- Table structure for table `staff_leave` CREATE TABLE `staff_leave` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(32) DEFAULT NULL, - staff member. `category` varchar(64) DEFAULT NULL, - category such as sick, family, etc. `hours` varchar(8) DEFAULT NULL, - hours for each category at year start PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; This table stores the staff absences by date, category, etc. -- Table structure for table `staff_payjrl` CREATE TABLE `staff_payjrl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(32) DEFAULT NULL, - staff userid `date` date DEFAULT NULL, - date of absence `category` varchar(64) DEFAULT NULL, - category as above in staff_leave `hours` varchar(8) DEFAULT NULL, - hours absence. `comment` varchar(255) DEFAULT NULL, - comment on the absence. PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Table to grant secondary access to a liason worker for attendance. Sakewew currently. -- Table structure for table `staff_secondary` CREATE TABLE `staff_secondary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `userid` varchar(40) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `position` varchar(64) DEFAULT NULL, `access` varchar(255) DEFAULT NULL, `band` varchar(64) DEFAULT NULL, `bandnum` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table to store staff additions/withdrawals since we need some sort of history of staff changes. The staffwd table below is a clone of the staff table, and when staff members leave (like students), they are moved into the staffwd table. -- Table structure for table `staff_transfer` CREATE TABLE `staff_transfer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, `date` date DEFAULT NULL, `type` varchar(32) DEFAULT NULL, `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `certificatenumber` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table clone of the staff table and holds staff who have left the school -- Table structure for table `staffwd` CREATE TABLE `staffwd` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sal` varchar(6) DEFAULT NULL, `lastname` varchar(60) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `middlename` varchar(40) DEFAULT NULL, `userid` varchar(40) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `emailwork` varchar(255) DEFAULT NULL, `emailpersonal` varchar(255) DEFAULT NULL, `tdate` date DEFAULT NULL, `doatt` char(1) DEFAULT NULL, `certification1` varchar(255) DEFAULT NULL, `home_phone` varchar(64) DEFAULT NULL, `cell_phone` varchar(64) DEFAULT NULL, `street` varchar(64) DEFAULT NULL, `city` varchar(32) DEFAULT NULL, `prov` varchar(32) DEFAULT NULL, `pcode` varchar(32) DEFAULT NULL, `emergency_contact_name` varchar(255) DEFAULT NULL, `emergency_contact_phone` varchar(255) DEFAULT NULL, `driver_license` varchar(64) DEFAULT NULL, `driver_class` varchar(64) DEFAULT NULL, `vehicle_reg` varchar(64) DEFAULT NULL, `vehicle_plate` varchar(64) DEFAULT NULL, `citizenship` varchar(64) DEFAULT NULL, `passport` varchar(64) DEFAULT NULL, `alt_street` varchar(64) DEFAULT NULL, `alt_city` varchar(32) DEFAULT NULL, `alt_prov` varchar(32) DEFAULT NULL, `alt_country` varchar(64) DEFAULT NULL, `alt_pcode` varchar(32) DEFAULT NULL, `alt_phone` varchar(64) DEFAULT NULL, `alt_email` varchar(64) DEFAULT NULL, `certification2` varchar(255) DEFAULT NULL, `certification3` varchar(255) DEFAULT NULL, `certification4` varchar(255) DEFAULT NULL, `certification5` varchar(255) DEFAULT NULL, `certification6` varchar(255) DEFAULT NULL, `certification7` varchar(255) DEFAULT NULL, `certification8` varchar(255) DEFAULT NULL, `teachingcertificatestatus` varchar(16) DEFAULT NULL, `teachingcertificatejurisdiction` varchar(16) DEFAULT NULL, `ancestry` varchar(16) DEFAULT NULL, `indianstatus` varchar(16) DEFAULT NULL, `gender` varchar(16) DEFAULT NULL, `birthdate` date DEFAULT NULL, `fulltimeequivalent` varchar(16) DEFAULT NULL, `occupations` text DEFAULT NULL, `nrskip` char(1) DEFAULT NULL, `extrafield1` varchar(255) DEFAULT NULL, `extrafield2` varchar(255) DEFAULT NULL, `extrafield3` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The table holding student information. -- Table structure for table `student` CREATE TABLE `student` ( `studid` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(40) DEFAULT NULL, - normal name, if no alias, same as legal name `firstname` varchar(40) DEFAULT NULL, `initial` varchar(40) DEFAULT NULL, `alias_lastname` varchar(40) DEFAULT NULL, - any aliases for the student. `alias_firstname` varchar(40) DEFAULT NULL, `legal_lastname` varchar(40) DEFAULT NULL, - legal name used to provincial authorities, etc. `legal_firstname` varchar(40) DEFAULT NULL, `studnum` int(11) DEFAULT NULL, - local student number; most important value since ties together attendance, course enrollment, etc. `grade` varchar(8) DEFAULT NULL, - grade of student; P3,PK, K, 1-12. `homeroom` varchar(8) DEFAULT NULL, - homeroom (ie. 2A, 2/3, etc) `sex` char(1) DEFAULT NULL, - gender `birthdate` date DEFAULT NULL, birthdate in yyyy-mm-dd format. `house` varchar(20) DEFAULT NULL, - house league `healthid` varchar(12) DEFAULT NULL, - provincial health id `entry` varchar(255) DEFAULT NULL, `ethnic` varchar(20) DEFAULT NULL, - ethnic value `single` char(1) DEFAULT NULL, `internet` char(1) DEFAULT NULL, `youngest` char(1) DEFAULT NULL, `owing` decimal(9,2) DEFAULT NULL, `treaty` varchar(20) DEFAULT NULL, `medical` mediumtext DEFAULT NULL, `studrel` varchar(25) DEFAULT NULL, - student religiion `momrel` varchar(25) DEFAULT NULL, - parents religion `dadrel` varchar(25) DEFAULT NULL, `parish` varchar(255) DEFAULT NULL, `commun` char(1) DEFAULT NULL, - had communion `recon` char(1) DEFAULT NULL, - had reconciliation `confirm` char(1) DEFAULT NULL, - confirmatoin `maiden` varchar(40) DEFAULT NULL, - mother maiden name `relation1` varchar(20) DEFAULT NULL, - relation to student (ie. father, guardian, grandparent, etc. `contact1` varchar(255) DEFAULT NULL, - any restrictions on this parent contact. `hphone1` varchar(64) DEFAULT NULL, - home phone of parent 1 `wphone1` varchar(64) DEFAULT NULL, - work phone `cell1` varchar(64) DEFAULT NULL, - cell phone `fax1` varchar(64) DEFAULT NULL, - fax phone `address1` varchar(40) DEFAULT NULL, - address of parent 1 `city1` varchar(40) DEFAULT NULL, `pcode1` varchar(10) DEFAULT NULL, `workplace1` varchar(60) DEFAULT NULL, - workplace of parent 1 `relation2` varchar(20) DEFAULT NULL, - relation to student of parent 2 `contact2` varchar(255) DEFAULT NULL, - rest below same as parent 1 `hphone2` varchar(64) DEFAULT NULL, `wphone2` varchar(64) DEFAULT NULL, `cell2` varchar(64) DEFAULT NULL, `fax2` varchar(64) DEFAULT NULL, `address2` varchar(40) DEFAULT NULL, `city2` varchar(40) DEFAULT NULL, `pcode2` varchar(10) DEFAULT NULL, `workplace2` varchar(60) DEFAULT NULL, `emrelation` varchar(20) DEFAULT NULL, - emergency contact relationship (grandparent, neighbour) `emname` varchar(255) DEFAULT NULL, - emergency name `emhphone` varchar(64) DEFAULT NULL, - em home phone `emwphone` varchar(64) DEFAULT NULL, - em work phone `emcell` varchar(64) DEFAULT NULL, - em cell `doctor` varchar(70) DEFAULT NULL, - doctor name `docphone` varchar(15) DEFAULT NULL, - doctor phone `busroute` varchar(30) DEFAULT NULL, - bus route / name of bus driver `provnum` varchar(12) DEFAULT NULL, - provincial student number `band` varchar(64) DEFAULT NULL, - band name `bandnum` varchar(32) DEFAULT NULL, - band number; number for the FN. `baptism` char(1) DEFAULT NULL, - part of religion fields above; baptised `contact` varchar(255) DEFAULT NULL, - any contact limitations. `pic` varchar(255) DEFAULT NULL, - picture present; non-null value will trigger a picture. Picture files are named using the local student number (1234.jpg) `password` varchar(32) DEFAULT NULL, - password to allow parents/students access to parent site view `field1` text DEFAULT NULL, - extra fields; descriptors, values can be changed in meta table. Only `field2` text DEFAULT NULL, up to 12 used in the student.tpl (template) when doing student edit/add. `field3` text DEFAULT NULL, `field4` text DEFAULT NULL, `field5` text DEFAULT NULL, `field6` text DEFAULT NULL, `field7` text DEFAULT NULL, `field8` text DEFAULT NULL, `field9` text DEFAULT NULL, `field10` text DEFAULT NULL, `field11` text DEFAULT NULL, `field12` text DEFAULT NULL, `field13` text DEFAULT NULL, `field14` text DEFAULT NULL, `field15` text DEFAULT NULL, `field16` text DEFAULT NULL, `prov1` varchar(32) DEFAULT NULL, - missing province values for parent 1,2 `prov2` varchar(32) DEFAULT NULL, `reserveres` varchar(64) DEFAULT NULL, - resident on reserve `program` varchar(64) DEFAULT NULL, - program of teaching. (normal program, adult student, spec ed) `email` varchar(255) DEFAULT NULL, - email of student Transportation fields (designed for NYC schools) - unused; only bus route above required. `trans_type` varchar(255) DEFAULT NULL, `trans_name` varchar(255) DEFAULT NULL, `trans_am_route` varchar(64) DEFAULT NULL, `trans_pickup_time` varchar(16) DEFAULT NULL, `trans_pm_route` varchar(64) DEFAULT NULL, `trans_dropoff_time` varchar(16) DEFAULT NULL, `trans_bus_stop_location` varchar(64) DEFAULT NULL, `group_after_school_prog` char(1) DEFAULT NULL, `lunchstatus` varchar(255) DEFAULT NULL, - whether student stays for lunch. Parent Email addresses `par1_email` varchar(64) DEFAULT NULL, `par2_email` varchar(64) DEFAULT NULL, Student Name prefix/suffix `name_prefix` varchar(64) DEFAULT NULL, `name_suffix` varchar(64) DEFAULT NULL, Name of family to billet students with in event of bad weather, etc. `billet` varchar(255) DEFAULT NULL, # Baby sitter/ child care info `childcare_name` varchar(255) DEFAULT NULL, `childcare_address` varchar(255) DEFAULT NULL, `childcare_phone1` varchar(255) DEFAULT NULL, `childcare_phone2` varchar(255) DEFAULT NULL, If student is to be locked out of Internet access. `internet_type` varchar(255) DEFAULT NULL, # update to parent names from a single field. `par1_firstname` varchar(255) DEFAULT NULL, `par1_lastname` varchar(255) DEFAULT NULL, `par2_firstname` varchar(255) DEFAULT NULL, `par2_lastname` varchar(255) DEFAULT NULL, `graddate` date DEFAULT NULL, - graduation date; allows grouping into cohorts (typ Gr 10) `address_land` varchar(32) DEFAULT NULL, - land address `tuition_status` varchar(64) DEFAULT NULL, - tuition agreements. `tuition_duration` varchar(64) DEFAULT NULL, `tuition_program` varchar(128) DEFAULT NULL, `tuition_collect` tinyint(4) DEFAULT NULL, `tuition_country` varchar(8) DEFAULT NULL, `immersion_type` varchar(32) DEFAULT NULL, - if an immersion student (alt language) `cumfolder` tinyint(4) DEFAULT NULL, - cumulative folder location (does the school have it?) `address1mailing` varchar(128) DEFAULT NULL, - mailing address for parents. `address2mailing` varchar(128) DEFAULT NULL, `parent_lockout` varchar(8) DEFAULT NULL, - parent lockout of parent site due to non-payment of fees. `mssid` - ID of student in the MSS/Aspen database - same as the pupilid in MSS. PRIMARY KEY (`studid`), KEY `student_name` (`lastname`,`firstname`), KEY `student_grade` (`grade`), KEY `student_homeroom` (`homeroom`), KEY `student_studnum` (`studnum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; This table is a clone of the student table. Withdrawn students are moved to this table from the student table when withdrawn from school -- Table structure for table `studentwd` CREATE TABLE `studentwd` ( `studid` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(40) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `initial` varchar(40) DEFAULT NULL, .... `immersion_type` varchar(32) DEFAULT NULL, `cumfolder` tinyint(4) DEFAULT NULL, `address1mailing` varchar(128) DEFAULT NULL, `address2mailing` varchar(128) DEFAULT NULL, `parent_lockout` varchar(8) DEFAULT NULL, `mssid` varchar(12), PRIMARY KEY (`studid`), KEY `studentwd_name` (`lastname`,`firstname`), KEY `studentwd_grade` (`grade`), KEY `studentwd_homeroom` (`homeroom`), KEY `studentwd_studnum` (`studnum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A separate table to store student medical information to give more comprehensive reports (ie. who has severe allergies) -- Table structure for table `student_medical` CREATE TABLE `student_medical` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, `category` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Stores information about the student for Nominal Roll reporting to Federal Gov't. -- Table structure for table `student_inac` CREATE TABLE `student_inac` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` varchar(32) DEFAULT NULL, `fte` varchar(255) DEFAULT NULL, `residence` varchar(255) DEFAULT NULL, `accommodation` varchar(255) DEFAULT NULL, `transportation_daily` varchar(255) DEFAULT NULL, `transportation_other` varchar(255) DEFAULT NULL, `language_instruction` varchar(255) DEFAULT NULL, `financial_edu` varchar(16) DEFAULT NULL, `residence_band` varchar(16) DEFAULT NULL, `residence_reserve` varchar(16) DEFAULT NULL, `schoolprogram` varchar(16) DEFAULT NULL, `programdelivery` varchar(16) DEFAULT NULL, `serviceprovision` varchar(16) DEFAULT NULL, `programcompletedprevyear` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Students withdrawn from the above student inac table. -- Table structure for table `student_nomrollwd` CREATE TABLE `student_nomrollwd` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` varchar(32) DEFAULT NULL, `fte` varchar(255) DEFAULT NULL, `residence` varchar(255) DEFAULT NULL, `accommodation` varchar(255) DEFAULT NULL, `transportation_daily` varchar(255) DEFAULT NULL, `transportation_other` varchar(255) DEFAULT NULL, `language_instruction` varchar(255) DEFAULT NULL, `financial_edu` varchar(16) DEFAULT NULL, `residence_band` varchar(16) DEFAULT NULL, `residence_reserve` varchar(16) DEFAULT NULL, `schoolprogram` varchar(16) DEFAULT NULL, `programdelivery` varchar(16) DEFAULT NULL, `serviceprovision` varchar(16) DEFAULT NULL, `programcompletedprevyear` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Table storing Needs assessments for students. -- Table structure for table `student_needs` CREATE TABLE `student_needs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `author` varchar(40) DEFAULT NULL, `date` date DEFAULT NULL, `tstamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `studnum` int(11) DEFAULT NULL, `treatynum` int(11) DEFAULT NULL, `lastname` varchar(40) DEFAULT NULL, `firstname` varchar(40) DEFAULT NULL, `birthdate` date DEFAULT NULL, `grade` varchar(8) DEFAULT NULL, `hrteacher` varchar(255) DEFAULT NULL, `strengths` text DEFAULT NULL, `challenges` text DEFAULT NULL, `support` text DEFAULT NULL, `recommendations` text DEFAULT NULL, `rec_other` text DEFAULT NULL, `rec_classroom` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; This table is the course master. It mates with the eval table which is the table storing report card evaluations and is the course enrollment table. -- Table structure for table `subject` CREATE TABLE `subject` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subjcode` varchar(32) DEFAULT NULL, - course code `description` varchar(255) DEFAULT NULL, - course name `grade` varchar(8) DEFAULT NULL, - course grade based on curriculum (not student course enrollment) `teacher` varchar(255) DEFAULT NULL, - userid of teacher (from staff table) `sequence` int(11) DEFAULT NULL, - ordering on the report card `section` varchar(32) DEFAULT NULL, `subjsec` varchar(32) DEFAULT NULL, `q1` varchar(255) DEFAULT NULL, `q2` varchar(255) DEFAULT NULL, `q3` varchar(255) DEFAULT NULL, `q4` varchar(255) DEFAULT NULL, `q5` varchar(255) DEFAULT NULL, `q6` varchar(255) DEFAULT NULL, `q7` varchar(255) DEFAULT NULL, `q8` varchar(255) DEFAULT NULL, `q9` varchar(255) DEFAULT NULL, `q10` varchar(255) DEFAULT NULL, `q11` varchar(255) DEFAULT NULL, `q12` varchar(255) DEFAULT NULL, `q13` varchar(255) DEFAULT NULL, `q14` varchar(255) DEFAULT NULL, `q15` varchar(255) DEFAULT NULL, `q16` varchar(255) DEFAULT NULL, `q17` varchar(255) DEFAULT NULL, `q18` varchar(255) DEFAULT NULL, `q19` varchar(255) DEFAULT NULL, `q20` varchar(255) DEFAULT NULL, `visible` char(1) DEFAULT NULL, `startrptperiod` tinyint(3) unsigned DEFAULT NULL - start term `endrptperiod` tinyint(3) unsigned DEFAULT NULL, - end term `markscheme` text DEFAULT NULL, - mark scheme in the grade book. `faculty` varchar(255) DEFAULT NULL, `location` varchar(255) DEFAULT NULL, `smdesc` varchar(8) DEFAULT NULL, - shortened description for reports `instmode` varchar(64) DEFAULT NULL, - instructional mode: school or distance `exammix` varchar(64) DEFAULT NULL, - school or dept (department of ed) `credit` varchar(8) DEFAULT NULL, - credits; blank, normally 1 Nov/24 - removed - `difficulty` varchar(8) DEFAULT NULL, `area` varchar(255) DEFAULT NULL, `calcavg` char(1) DEFAULT NULL, `teacher2` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`), KEY `subject_subjsec` (`subjsec`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; A table storing teacher attendance entries to make sure they actually did attendance. Since we only record absences not presences, if a teacher didn't do attendance, all students in his/her homeroom/course would have perfect attendance. This table goes along with the attend table which stores student absences. Conversely this table stores teacher "presences"... they have done their attendance. -- Table structure for table `tattend` CREATE TABLE `tattend` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(40) DEFAULT NULL, - teacher userid `attdate` date DEFAULT NULL, - date attendance entry is for `currdate` datetime DEFAULT NULL, - date attendance was done (as opposed to attdate) `subjects` varchar(255) DEFAULT NULL, - course/homeroom (homeroom has leading 'HR:') `periods` varchar(255) DEFAULT NULL, - period of attendance (only stores single numeric value) PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=972 DEFAULT CHARSET=utf8mb4; This table records student enrollments/registrations in the school. Both withdrawal and enrollment. -- Table structure for table `transfer` CREATE TABLE `transfer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, - local student number. `date` date DEFAULT NULL, - date of enrol/withdraw `type` varchar(10) DEFAULT NULL, - enrol or withdraw `description` varchar(255) DEFAULT NULL, - description; what school they came from/went to `entrytype` varchar(8) DEFAULT NULL, - provincial entry code if enrol `exittype` varchar(8) DEFAULT NULL, - provincial exit code if withdraw `prov` varchar(8) DEFAULT NULL, - province of movement to/from `country` varchar(8) DEFAULT NULL, - country `utag` char(1) DEFAULT NULL, - now unused `lastname` varchar(40) DEFAULT NULL, - name information etc in case of missing studentwd data. `firstname` varchar(40) DEFAULT NULL, `middlename` varchar(40) DEFAULT NULL, `birthdate` date DEFAULT NULL, `provnum` varchar(12) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The following tables store information for student transcripts (ie. multiple years of final marks and credits in courses) Table with data posted from the report cards into this table and stored for many years to allow the printing of student transcripts. -- Table structure for table `tscriptdata` CREATE TABLE `tscriptdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, `subjectcode` char(32) DEFAULT NULL, `subjecttext` char(255) DEFAULT NULL, `subjectarea` char(255) DEFAULT NULL, `score_mark` char(32) DEFAULT NULL, `score_letter` char(4) DEFAULT NULL, `score_diff` char(4) DEFAULT NULL, `schoolyear` char(16) DEFAULT NULL, `crdate` date DEFAULT NULL, `credit` char(16) DEFAULT NULL, `term` int(11) DEFAULT NULL, `term_desc` char(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Table to identify the student in the transcript table for permanent storage since the student may be removed from both student and studentwd in the longer term (ie. multiple years later). -- Table structure for table `tscriptident` CREATE TABLE `tscriptident` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studnum` int(11) DEFAULT NULL, `lastname` char(40) DEFAULT NULL, `firstname` char(40) DEFAULT NULL, `middlename` char(40) DEFAULT NULL, `birthdate` date DEFAULT NULL, `studentid` char(32) DEFAULT NULL, `graddate` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; The following tables are used to allow for translation of Open Admin (OA) into other languages. Since there has been little of this done, many newer scripts do no have complete lexicon (lex) sections, so some script update may be needed. Description of the usage of each phrase. -- Table structure for table `xlat_hint` CREATE TABLE `xlat_hint` ( `id` int(11) NOT NULL AUTO_INCREMENT, `phrase` text DEFAULT NULL, `description` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Contains the actual english phrase and it's translation including language and encoding. -- Table structure for table `xlat_lang` CREATE TABLE `xlat_lang` ( `id` int(11) NOT NULL AUTO_INCREMENT, `language` varchar(64) DEFAULT NULL, `encoding` varchar(64) DEFAULT NULL, `author` varchar(64) DEFAULT NULL, `phrase` text DEFAULT NULL, `translation` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Area and Files where phrase is used for rewriting of scripts. -- Table structure for table `xlat_phrase` CREATE TABLE `xlat_phrase` ( `id` int(11) NOT NULL AUTO_INCREMENT, `area` varchar(64) DEFAULT NULL, `file` varchar(64) DEFAULT NULL, `phrase` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- View structure for view `studentall` A database 'view' created from student and studentwd table so as to contain all students in both tables. There is a script in the updatesDB directory on server and also in the utility/installdebian directory of the download.