A complete school management database schema designed for K-12 institutions and academies. Includes student and teacher profiles, class and subject management, grade recording with weighted scoring, and daily attendance tracking. Suitable for school ERP systems, academy management software, or custom school administration tools.
A subject and a class are not the same thing, and that distinction carries the schema. subjects is the curriculum entry — Mathematics, three credit hours. classes is one teacher teaching it in one semester in one room, with its own capacity. Grades and attendance key on the class, never on the subject, because what a student attended was a specific section.
The timetable is its own table because a class meets more than once a week. class_schedules holds a row per weekly slot with a day_of_week, a start and end time and its own room — so Monday in the lab and Wednesday in the lecture hall are two rows rather than an unrepresentable conflict.
attendance is one row per student per class per date. It is by far the largest table here, and it is a deliberate cost: nothing less than a row per session can answer which specific lesson a student missed, which is the question the whole attendance system exists for.
Attaching grades to a subject would be shorter and would merge every section of Mathematics into one. The class is what a timetable, a teacher assignment and a room all belong to, and two sections of the same subject are genuinely different courses to the students in them. The cost is an extra join on every academic query and the need to decide what happens to a class at the end of a semester.
The letter could be derived from the score on every read, and it would then change retroactively whenever the grading scale is adjusted. Storing grade_letter freezes the judgement made under the scale in force at the time, which is what a transcript requires. The duplication has to be written together, or the two columns will disagree about the same exam.
The table holds a name, a relationship and contact details keyed to one student. That covers the only thing most school systems need — knowing who to call. It does not support a parent logging in to see two children, since each guardian row belongs to a single student. A parent portal is the point at which this becomes a parents table with a join.
One table serves both the whole school and a single class: null means everyone. The alternative is two tables with identical columns and a union on every read of the noticeboard. The nullable key keeps it to one query, at the price of a filter that must be written correctly or a class notice reaches the entire school.