Database Schema

Overview

The School Management Platform uses a MySQL relational database to store all application data. The schema is designed with normalization principles to ensure data integrity and eliminate redundancy.

Database Connection

The application uses PDO (PHP Data Objects) for database connectivity:

// Database singleton pattern (referenced in models)
$this->db = Database::getInstance()->getConnection();

PDO provides a consistent interface for database access and supports prepared statements for SQL injection prevention.

Entity-Relationship Diagram

Core Tables

users Table

Stores authentication and basic information for all system users.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique user identifier
`name`	VARCHAR(255)	NOT NULL	User’s full name
`email`	VARCHAR(255)	UNIQUE, NOT NULL	Login email address
`password`	VARCHAR(255)	NOT NULL	Hashed password
`role`	ENUM(‘admin’, ‘teacher’, ‘student’)	NOT NULL	User role for access control

Example Query:

app/models/User.php:14

public function findByEmail($email)
{
    $stmt = $this->db->prepare("SELECT * FROM users WHERE email = ?");
    $stmt->execute([$email]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

Passwords are hashed using password_hash() with the PASSWORD_DEFAULT algorithm before storage. Never store plain text passwords.

students Table

Extends user information for students (inheritance pattern).

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, FOREIGN KEY → users(id)	References user record

Example Query:

app/models/Student.php:14

public function getAll()
{
    $sql = "SELECT users.id, users.name, users.email 
            FROM users 
            JOIN students ON users.id = students.id
            ORDER BY users.name";
    
    return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

teachers Table

Extends user information for teachers (inheritance pattern).

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, FOREIGN KEY → users(id)	References user record

Example Query:

app/models/Teacher.php:14

public function all()
{
    return $this->db->query("
        SELECT users.id, users.name, users.email
        FROM teachers
        JOIN users ON teachers.id = users.id
    ")->fetchAll(PDO::FETCH_ASSOC);
}

subjects Table

Stores academic subjects (e.g., Mathematics, Physics, English).

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique subject identifier
`name`	VARCHAR(255)	UNIQUE, NOT NULL	Subject name

Example Query:

app/models/Subject.php:14

public function all()
{
    return $this->db->query("SELECT * FROM subjects")
                    ->fetchAll(PDO::FETCH_ASSOC);
}

courses Table

Represents a specific instance of a subject taught by a teacher.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique course identifier
`subject_id`	INT	FOREIGN KEY → subjects(id)	Subject being taught
`teacher_id`	INT	FOREIGN KEY → teachers(id)	Teacher assigned

A course is the combination of a subject and a teacher. Multiple courses can exist for the same subject if taught by different teachers.

Example Query:

app/models/Course.php:14

public function getAll()
{
    return $this->db->query("
        SELECT courses.id, subjects.name AS subject_name, users.name AS teacher_name
        FROM courses
        JOIN subjects ON courses.subject_id = subjects.id
        JOIN teachers ON courses.teacher_id = teachers.id
        JOIN users ON teachers.id = users.id
    ")->fetchAll(PDO::FETCH_ASSOC);
}

enrollments Table

Links students to courses (many-to-many relationship).

Column	Type	Constraints	Description
`student_id`	INT	PRIMARY KEY, FOREIGN KEY → students(id)	Enrolled student
`course_id`	INT	PRIMARY KEY, FOREIGN KEY → courses(id)	Course enrolled in

Composite Primary Key: (student_id, course_id) Example Query:

app/models/Enrollment.php:14

public function getAll()
{
    $sql = "SELECT 
                students.id AS student_id,
                users.name AS student_name,
                subjects.name AS subject_name,
                courses.id AS course_id
            FROM enrollments
            JOIN students ON enrollments.student_id = students.id
            JOIN users ON students.id = users.id
            JOIN courses ON enrollments.course_id = courses.id
            JOIN subjects ON courses.subject_id = subjects.id";
    
    return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

Enrollment Check:

app/models/Enrollment.php:55

public function isAlreadyEnrolled($student_id, $course_id)
{
    $stmt = $this->db->prepare(
        "SELECT COUNT(*) FROM enrollments 
         WHERE student_id = ? AND course_id = ?"
    );
    $stmt->execute([$student_id, $course_id]);
    return $stmt->fetchColumn() > 0;
}

grades Table

Stores student grades for enrolled courses.

Column	Type	Constraints	Description
`student_id`	INT	PRIMARY KEY, FOREIGN KEY → students(id)	Student receiving grade
`course_id`	INT	PRIMARY KEY, FOREIGN KEY → courses(id)	Course graded in
`grade`	DECIMAL(5,2)	NULL allowed	Numeric grade (e.g., 85.50)

Composite Primary Key: (student_id, course_id)

The REPLACE INTO statement is used to insert or update grades, ensuring only one grade exists per student-course combination.

Example Query:

app/models/Grade.php:29

public function save($student_id, $course_id, $grade)
{
    $stmt = $this->db->prepare("
        REPLACE INTO grades (student_id, course_id, grade)
        VALUES (?, ?, ?)
    ");
    $stmt->execute([$student_id, $course_id, $grade]);
}

Student Bulletin Query:

app/models/Grade.php:51

public function getBulletinData($student_id)
{
    $sql = "
        SELECT 
            subjects.name AS subject,
            users.name AS teacher,
            grades.grade
        FROM grades
        JOIN courses ON grades.course_id = courses.id
        JOIN subjects ON courses.subject_id = subjects.id
        JOIN users ON courses.teacher_id = users.id
        WHERE grades.student_id = ?
    ";
    
    $stmt = $this->db->prepare($sql);
    $stmt->execute([$student_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

timetable Table

Schedules courses with day and time information.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique timetable entry
`course_id`	INT	FOREIGN KEY → courses(id)	Scheduled course
`day`	ENUM(‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’)	NOT NULL	Day of week
`start_time`	TIME	NOT NULL	Class start time
`end_time`	TIME	NOT NULL	Class end time

Example Query:

app/models/Timetable.php:14

public function getAll()
{
    $sql = "
        SELECT 
            timetable.id,
            subjects.name AS subject,
            users.name AS teacher,
            timetable.day,
            timetable.start_time,
            timetable.end_time
        FROM timetable
        JOIN courses ON courses.id = timetable.course_id
        JOIN subjects ON subjects.id = courses.subject_id
        JOIN teachers ON teachers.id = courses.teacher_id
        JOIN users ON users.id = teachers.id
        ORDER BY timetable.day, timetable.start_time
    ";
    
    return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

Teacher Conflict Detection:

app/models/Timetable.php:44

public function teacherHasConflict($teacher_id, $day, $start, $end)
{
    $sql = "
        SELECT COUNT(*) 
        FROM timetable t
        JOIN courses c ON c.id = t.course_id
        WHERE c.teacher_id = ?
          AND t.day = ?
          AND (? < t.end_time AND ? > t.start_time)
    ";
    
    $stmt = $this->db->prepare($sql);
    $stmt->execute([$teacher_id, $day, $start, $end]);
    
    return $stmt->fetchColumn() > 0;
}

Table Relationships

User Inheritance Pattern

The schema implements Single Table Inheritance for users:

users table contains common fields (id, name, email, password, role)
students and teachers tables reference users.id as their primary key
This allows polymorphic queries and role-based access control

Many-to-Many Relationships

Students ↔ Courses (via enrollments)

-- Get all courses for a student
SELECT courses.*, subjects.name
FROM enrollments
JOIN courses ON enrollments.course_id = courses.id
JOIN subjects ON courses.subject_id = subjects.id
WHERE enrollments.student_id = ?

Students ↔ Grades ↔ Courses

-- Get all grades for a student with subject info
SELECT subjects.name AS subject, grades.grade
FROM grades
JOIN courses ON grades.course_id = courses.id
JOIN subjects ON courses.subject_id = subjects.id
WHERE grades.student_id = ?

One-to-Many Relationships

Teachers → Courses

app/models/Course.php:36

public function getByTeacher($teacher_id)
{
    $stmt = $this->db->prepare("
        SELECT courses.id, subjects.name
        FROM courses
        JOIN subjects ON courses.subject_id = subjects.id
        WHERE courses.teacher_id = ?
    ");
    $stmt->execute([$teacher_id]);
    return $stmt->fetchAll();
}

Courses → Timetable Entries

SELECT * FROM timetable WHERE course_id = ?

Common Queries

Student Timetable

app/models/Timetable.php:69

public function getForStudent($student_id)
{
    $sql = "
        SELECT 
            subjects.name AS subject,
            users.name AS teacher,
            timetable.day,
            timetable.start_time,
            timetable.end_time
        FROM timetable
        JOIN courses ON courses.id = timetable.course_id
        JOIN enrollments ON enrollments.course_id = courses.id
        JOIN subjects ON subjects.id = courses.subject_id
        JOIN teachers ON teachers.id = courses.teacher_id
        JOIN users ON users.id = teachers.id
        WHERE enrollments.student_id = ?
        ORDER BY timetable.day, timetable.start_time
    ";
    
    $stmt = $this->db->prepare($sql);
    $stmt->execute([$student_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Teacher Timetable

app/models/Timetable.php:94

public function getForTeacher($teacher_id)
{
    $sql = "
        SELECT 
            subjects.name AS subject,
            timetable.day,
            timetable.start_time,
            timetable.end_time
        FROM timetable
        JOIN courses ON courses.id = timetable.course_id
        JOIN subjects ON subjects.id = courses.subject_id
        WHERE courses.teacher_id = ?
        ORDER BY timetable.day, timetable.start_time
    ";
    
    $stmt = $this->db->prepare($sql);
    $stmt->execute([$teacher_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Students Enrolled in a Course

app/models/Grade.php:13

public function getStudentsByCourse($course_id)
{
    $stmt = $this->db->prepare("
        SELECT students.id, users.name, grades.grade
        FROM enrollments
        JOIN students ON enrollments.student_id = students.id
        JOIN users ON students.id = users.id
        LEFT JOIN grades 
          ON grades.student_id = students.id 
         AND grades.course_id = enrollments.course_id
        WHERE enrollments.course_id = ?
    ");
    $stmt->execute([$course_id]);
    return $stmt->fetchAll();
}

Database Security

Prepared Statements

All queries use PDO prepared statements to prevent SQL injection

Password Hashing

Passwords are hashed with password_hash() using bcrypt algorithm

Foreign Keys

Referential integrity enforced through foreign key constraints

Unique Constraints

Email addresses and subject names are unique to prevent duplicates

Database Normalization

The schema follows Third Normal Form (3NF):

1NF: Atomic Values

All columns contain atomic (indivisible) values, no repeating groups

2NF: No Partial Dependencies

All non-key attributes depend on the entire primary key

3NF: No Transitive Dependencies

Non-key attributes don’t depend on other non-key attributes

Benefits:

Eliminates data redundancy
Ensures data integrity
Simplifies data maintenance
Improves query performance

Sample Data Flow

Here’s how data flows through related tables:

Next Steps

MVC Structure

Learn how models interact with the MVC pattern

Security Features

Explore database security measures in detail

Getting Started

User Guides

Core Features

Architecture

Overview

Database Connection

Entity-Relationship Diagram

Core Tables

users Table

students Table

teachers Table

subjects Table

courses Table

enrollments Table

grades Table

timetable Table

Table Relationships

User Inheritance Pattern

Many-to-Many Relationships

One-to-Many Relationships

Common Queries

Student Timetable

Teacher Timetable

Students Enrolled in a Course

Database Security

Prepared Statements

Password Hashing

Foreign Keys

Unique Constraints

Database Normalization

Sample Data Flow

Next Steps

MVC Structure

Security Features

Build docs developers (and LLMs) love

Getting Started

User Guides

Core Features

Architecture

​Overview

​Database Connection

​Entity-Relationship Diagram

​Core Tables

​users Table

​students Table

​teachers Table

​subjects Table

​courses Table

​enrollments Table

​grades Table

​timetable Table

​Table Relationships

​User Inheritance Pattern

​Many-to-Many Relationships

​One-to-Many Relationships

​Common Queries

​Student Timetable

​Teacher Timetable

​Students Enrolled in a Course

​Database Security

Prepared Statements

Password Hashing

Foreign Keys

Unique Constraints

​Database Normalization

​Sample Data Flow

​Next Steps

MVC Structure

Security Features

Build docs developers (and LLMs) love

Overview

Database Connection

Entity-Relationship Diagram

Core Tables

users Table

students Table

teachers Table

subjects Table

courses Table

enrollments Table

grades Table

timetable Table

Table Relationships

User Inheritance Pattern

Many-to-Many Relationships

One-to-Many Relationships

Common Queries

Student Timetable

Teacher Timetable

Students Enrolled in a Course

Database Security

Database Normalization

Sample Data Flow

Next Steps