Timetable Management

Overview

The Timetable Management system allows administrators to schedule courses by assigning specific days and time slots. It includes intelligent conflict detection to prevent double-booking teachers and provides personalized timetable views for students and teachers.

Schedule Creation

Assign courses to specific days and time slots

Conflict Detection

Prevent teacher double-booking automatically

Student Timetables

Show only enrolled courses in student schedules

Teacher Timetables

Display teaching schedule for instructors

Timetable Architecture

Database Schema

CREATE TABLE timetable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT NOT NULL,
    day VARCHAR(20) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

Schema Design:

course_id: Links to course (which includes teacher and subject)
day: Day of the week (e.g., “Monday”, “Tuesday”)
start_time: Class start time (e.g., “08:00:00”)
end_time: Class end time (e.g., “10:00:00”)
CASCADE: Deleting a course removes its timetable entries

What is a Timetable Entry?

A timetable entry represents a scheduled class session:

Course: “Mathematics taught by Mr. Smith”
Day: “Monday”
Time: “08:00 - 10:00”

Multiple entries can exist for the same course on different days/times.

Admin Operations

Viewing All Timetable Entries

Administrators see the complete schedule:

// TimetableController.php - index()
public function index()
{
    Auth::admin(); // Only admins can view/manage all entries
    $timetables = $this->model->getAll();
    require __DIR__ . '/../views/timetable/index.php';
}

// Timetable.php - getAll()
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);
}

Query Breakdown

JOINs Explained:

timetable → courses: Get course information
courses → subjects: Get subject name (e.g., “Mathematics”)
courses → teachers: Link to teacher records
teachers → users: Get teacher name (e.g., “Mr. Smith”)

Result includes:

Entry ID (for deletion)
Subject name
Teacher name
Day of week
Start and end times

Ordering:

Sorted by day, then by start time (chronological schedule)

Creating Timetable Entries with Conflict Detection

The system prevents scheduling conflicts:

// TimetableController.php - create()
public function create()
{
    Auth::admin();

    $courseModel = new Course();
    $courses = $courseModel->getForTimetable();

    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $course_id = $_POST['course_id'];
        $day = $_POST['day'];
        $start = $_POST['start_time'];
        $end = $_POST['end_time'];

        // Get teacher from selected course
        $course = $courseModel->find($course_id);
        $teacher_id = $course['teacher_id'];

        // Check for scheduling conflicts
        if ($this->model->teacherHasConflict($teacher_id, $day, $start, $end)) {
            $_SESSION['error'] = "❌ This teacher already has another course during this time.";
            header("Location: /timetable/create");
            exit;
        }

        // No conflict → create entry
        $this->model->create($course_id, $day, $start, $end);

        header("Location: /timetable");
        exit;
    }

    require __DIR__ . '/../views/timetable/create.php';
}

// Timetable.php - create()
public function create($course_id, $day, $start, $end)
{
    $stmt = $this->db->prepare(
        "INSERT INTO timetable (course_id, day, start_time, end_time)
         VALUES (?, ?, ?, ?)"
    );

    return $stmt->execute([$course_id, $day, $start, $end]);
}

Creation Workflow:

Admin selects course from dropdown (e.g., “Mathematics - Mr. Smith”)
Admin selects day (e.g., “Monday”)
Admin enters start time (e.g., “08:00”)
Admin enters end time (e.g., “10:00”)
System extracts teacher_id from course
System checks if teacher is already scheduled at that time
If conflict exists, show error and reject
If no conflict, create timetable entry

Conflict Detection Algorithm

The conflict detection prevents teacher double-booking:

// Timetable.php - teacherHasConflict()
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;
}

Time Overlap Logic Explained

Condition: (? < t.end_time AND ? > t.start_time)This checks if new time slot overlaps with existing entry.Examples:Scenario 1: Overlap (CONFLICT)

Existing: 08:00 - 10:00
New: 09:00 - 11:00
Check: (09:00 < 10:00) AND (11:00 > 08:00) → TRUE ✅ Conflict!

Scenario 2: Partial Overlap (CONFLICT)

Existing: 08:00 - 10:00
New: 07:00 - 09:00
Check: (07:00 < 10:00) AND (09:00 > 08:00) → TRUE ✅ Conflict!

Scenario 3: Complete Overlap (CONFLICT)

Existing: 08:00 - 10:00
New: 08:30 - 09:30
Check: (08:30 < 10:00) AND (09:30 > 08:00) → TRUE ✅ Conflict!

Scenario 4: No Overlap (ALLOWED)

Existing: 08:00 - 10:00
New: 10:00 - 12:00
Check: (10:00 < 10:00) AND (12:00 > 08:00) → FALSE ❌ No conflict

Scenario 5: Different Day (ALLOWED)

Existing: Monday 08:00 - 10:00
New: Tuesday 08:00 - 10:00
Different day → No conflict (day filter prevents match)

Deleting Timetable Entries

// TimetableController.php - delete()
public function delete()
{
    Auth::admin();
    $this->model->delete($_GET['id']);
    header("Location: /timetable");
}

// Timetable.php - delete()
public function delete($id)
{
    $stmt = $this->db->prepare("DELETE FROM timetable WHERE id = ?");
    return $stmt->execute([$id]);
}

Student Timetable View

Students see only courses they’re enrolled in:

// TimetableController.php - student()
public function student()
{
    Auth::student(); // Only students can access
    $data = $this->model->getForStudent($_SESSION['user']['id']);
    require __DIR__ . '/../views/timetable/student.php';
}

// Timetable.php - getForStudent()
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);
}

Student Query Explanation

Key Difference from Admin View:

Includes JOIN enrollments to filter by student
Only shows courses the student is enrolled in
Does not include timetable entry ID (students can’t delete)

Flow:

Start with timetable entries
Join to courses
Filter by enrollments for this student ← Key filter
Get subject and teacher names
Order by day and time

Result: Student sees personalized schedule with only their classes.

Example Student Timetable:

[
    [
        'subject' => 'Mathematics',
        'teacher' => 'Mr. Smith',
        'day' => 'Monday',
        'start_time' => '08:00:00',
        'end_time' => '10:00:00'
    ],
    [
        'subject' => 'Physics',
        'teacher' => 'Dr. Johnson',
        'day' => 'Monday',
        'start_time' => '10:00:00',
        'end_time' => '12:00:00'
    ],
    [
        'subject' => 'English',
        'teacher' => 'Ms. Williams',
        'day' => 'Tuesday',
        'start_time' => '14:00:00',
        'end_time' => '16:00:00'
    ]
]

Teacher Timetable View

Teachers see their teaching schedule:

// TimetableController.php - teacher()
public function teacher()
{
    Auth::teacher(); // Only teachers can access
    $data = $this->model->getForTeacher($_SESSION['user']['id']);
    require __DIR__ . '/../views/timetable/teacher.php';
}

// Timetable.php - getForTeacher()
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);
}

Teacher Query Explanation

Key Points:

Filters by courses.teacher_id (only courses taught by this teacher)
Does not include teacher name (redundant, it’s the logged-in teacher)
Simpler than student query (no enrollment join needed)
Does not include entry ID (teachers can’t modify timetable)

Result: Teacher sees when and what they’re teaching.

Example Teacher Timetable:

[
    [
        'subject' => 'Mathematics',
        'day' => 'Monday',
        'start_time' => '08:00:00',
        'end_time' => '10:00:00'
    ],
    [
        'subject' => 'Algebra',
        'day' => 'Wednesday',
        'start_time' => '14:00:00',
        'end_time' => '16:00:00'
    ]
]

Complete Timetable Model

// Timetable.php - Full implementation
class Timetable
{
    private $db;

    public function __construct()
    {
        $this->db = Database::getInstance()->getConnection();
    }

    // Admin: Get all timetable entries
    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);
    }

    // Admin: Create timetable entry
    public function create($course_id, $day, $start, $end)
    {
        $stmt = $this->db->prepare(
            "INSERT INTO timetable (course_id, day, start_time, end_time)
             VALUES (?, ?, ?, ?)"
        );
        return $stmt->execute([$course_id, $day, $start, $end]);
    }

    // Admin: Check if teacher has scheduling conflict
    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;
    }

    // Admin: Delete timetable entry
    public function delete($id)
    {
        $stmt = $this->db->prepare("DELETE FROM timetable WHERE id = ?");
        return $stmt->execute([$id]);
    }

    // Student: Get personalized timetable (enrolled courses only)
    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: Get teaching schedule
    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);
    }
}

API Endpoints

Admin Endpoints

Method	Endpoint	Access	Description
GET	`/timetable`	Admin	View all timetable entries
GET	`/timetable/create`	Admin	Show creation form
POST	`/timetable/create`	Admin	Create new entry with conflict check
GET	`/timetable/delete?id={id}`	Admin	Delete entry

User Endpoints

Method	Endpoint	Access	Description
GET	`/timetable/student`	Student	View personal schedule
GET	`/timetable/teacher`	Teacher	View teaching schedule

Use Cases

Use Case 1: Creating Schedule (No Conflict)

Context: Admin schedules Mathematics with Mr. SmithFlow:

Admin navigates to /timetable/create
Selects course: “Mathematics - Mr. Smith”
Selects day: “Monday”
Enters time: “08:00 - 10:00”
System finds course: teacher_id = smith_id
Checks conflict: teacherHasConflict(smith_id, 'Monday', '08:00', '10:00')
Query finds: No existing entries for Smith on Monday 08:00-10:00
Returns: false (no conflict)
Creates entry successfully
Entry appears in:
- Admin timetable view
- Mr. Smith’s teacher timetable
- Enrolled students’ timetables

Use Case 2: Conflict Detection

Context: Admin tries to double-book a teacherFlow:

Existing entry: Mr. Smith, Monday, 08:00-10:00 (Mathematics)
Admin tries to create: Mr. Smith, Monday, 09:00-11:00 (Algebra)
System extracts: teacher_id = smith_id
Checks: teacherHasConflict(smith_id, 'Monday', '09:00', '11:00')
Query finds: Entry from 08:00-10:00
Time overlap check:
- (09:00 < 10:00) → TRUE
- (11:00 > 08:00) → TRUE
- Both TRUE → Conflict exists
Returns: true
Sets error: “Teacher already has another course during this time”
Redirects to create page with error message
Entry is NOT created

Use Case 3: Student Viewing Timetable

Context: John Doe wants to see his class scheduleFlow:

John logs in as student
Navigates to Timetable
System gets: student_id = john_id from session
Calls: getForStudent(john_id)
Query joins enrollments to filter John’s courses
Returns entries for:
- Mathematics (Monday 08:00-10:00)
- Physics (Monday 10:00-12:00)
- English (Tuesday 14:00-16:00)
Displays personalized weekly schedule
John sees when and where to attend classes

Use Case 4: Teacher Viewing Schedule

Context: Mr. Smith wants to check his teaching hoursFlow:

Mr. Smith logs in as teacher
Navigates to Timetable
System gets: teacher_id = smith_id from session
Calls: getForTeacher(smith_id)
Query filters: WHERE courses.teacher_id = smith_id
Returns:
- Mathematics: Monday 08:00-10:00
- Algebra: Wednesday 14:00-16:00
Displays Mr. Smith’s teaching schedule
Sorted by day and time

Data Relationships

┌────────────┐
│  Courses   │
│────────────│
│ id         │
│ subject_id │────► Subjects (name)
│ teacher_id │────► Teachers/Users (name)
└─────┬──────┘
      │
      │ course_id
      ▼
┌────────────┐
│ Timetable  │
│────────────│
│ id         │
│ course_id  │
│ day        │
│ start_time │
│ end_time   │
└────────────┘
      │
      │ Filtered by:
      │
      ├─► Enrollments (for student view)
      └─► Teacher ID (for teacher view)

Best Practices

Conflict Prevention

Always check teacher availability
Use time overlap algorithm
Validate day and time inputs
Show clear error messages

Role-Based Views

Students see enrolled courses only
Teachers see teaching schedule
Admins see complete timetable
Filter data at query level

Data Integrity

Link entries to courses (not directly to teachers/subjects)
Use CASCADE deletion
Maintain referential integrity
Store times in TIME format

User Experience

Sort by day and time
Display subject and teacher names
Show time ranges clearly
Prevent scheduling errors proactively

Key Design Principle: Timetable entries link to courses (not directly to teachers/subjects). This ensures:

Course deletion removes timetable entries automatically
Teacher changes in course reflect in timetable
Single source of truth for course information

Getting Started

User Guides

Core Features

Architecture

Overview

Schedule Creation

Conflict Detection

Student Timetables

Teacher Timetables

Timetable Architecture

Database Schema

What is a Timetable Entry?

Admin Operations

Viewing All Timetable Entries

Creating Timetable Entries with Conflict Detection

Conflict Detection Algorithm

Deleting Timetable Entries

Student Timetable View

Teacher Timetable View

Complete Timetable Model

API Endpoints

Admin Endpoints

User Endpoints

Use Cases

Data Relationships

Best Practices

Conflict Prevention

Role-Based Views

Data Integrity

User Experience

Build docs developers (and LLMs) love

Getting Started

User Guides

Core Features

Architecture

​Overview

Schedule Creation

Conflict Detection

Student Timetables

Teacher Timetables

​Timetable Architecture

​Database Schema

​What is a Timetable Entry?

​Admin Operations

​Viewing All Timetable Entries

​Creating Timetable Entries with Conflict Detection

​Conflict Detection Algorithm

​Deleting Timetable Entries

​Student Timetable View

​Teacher Timetable View

​Complete Timetable Model

​API Endpoints

​Admin Endpoints

​User Endpoints

​Use Cases

​Data Relationships

​Best Practices

Conflict Prevention

Role-Based Views

Data Integrity

User Experience

Build docs developers (and LLMs) love

Overview

Timetable Architecture

Database Schema

What is a Timetable Entry?

Admin Operations

Viewing All Timetable Entries

Creating Timetable Entries with Conflict Detection

Conflict Detection Algorithm

Deleting Timetable Entries

Student Timetable View

Teacher Timetable View

Complete Timetable Model

API Endpoints

Admin Endpoints

User Endpoints

Use Cases

Data Relationships

Best Practices