MySQL Database Master Course Kwa Beginners 2026: Jifunze Kutengeneza Mfumo Mkubwa wa Kisasa kwa SQL, CRUD, Relationships, Roles, Reports na Security

MySQL course kwa Kiswahili, database kwa beginners, SQL CRUD, MySQL project, school management system database, PHP MySQL system, database kubwa
Link muhimu: https://faulink.com

1. Database ya Mfumo Mkubwa ni Nini?

Database ya mfumo mkubwa ni database inayoweza kuhifadhi taarifa nyingi kwa mpangilio, mfano:

Users
Roles
Schools
Classes
Students
Subjects
Marks
Payments
Blog posts
Subscriptions
Reports
Activity logs
Settings

Mfumo mkubwa lazima uwe na:

Security
Relationships
CRUD
Reports
Search
User roles
Payments
Backup
Activity logs
Scalability

Tembelea: https://faulink.com

2. Project Tutakayojenga

Tutaunda database ya mfumo mkubwa uitwao:

FauLink School Management System

Mfumo huu utakuwa na:

Super Admin
School Admin
Teachers
Students
Classes
Subjects
Exams
Marks
Payments
Blog posts
Subscription
Activity logs
Reports
3. Kutengeneza Database
CREATE DATABASE faulink_school_system;
USE faulink_school_system;

Kuangalia database:

SHOW DATABASES;

Link: https://faulink.com

4. Table ya Users

Hii table itahifadhi watumiaji wote.

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
username VARCHAR(100) UNIQUE NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(30),
password VARCHAR(255) NOT NULL,
role ENUM('super_admin','school_admin','teacher','student','accountant') DEFAULT 'student',
account_status ENUM('active','inactive','suspended') DEFAULT 'active',
trial_start_date DATE,
trial_end_date DATE,
subscription_start_date DATE,
subscription_end_date DATE,
is_paid TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Kuangalia structure:

DESC users;
5. Table ya Schools
CREATE TABLE schools (
id INT AUTO_INCREMENT PRIMARY KEY,
school_name VARCHAR(200) NOT NULL,
registration_number VARCHAR(100) UNIQUE,
email VARCHAR(150),
phone VARCHAR(30),
address VARCHAR(255),
region VARCHAR(100),
district VARCHAR(100),
logo VARCHAR(255),
owner_user_id INT,
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (owner_user_id) REFERENCES users(id)
);
6. Table ya Classes
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
class_name VARCHAR(100) NOT NULL,
class_level VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

Mfano:

INSERT INTO classes (school_id, class_name, class_level)
VALUES
(1, 'Form One', 'O-Level'),
(1, 'Form Two', 'O-Level'),
(1, 'Form Three', 'O-Level');
7. Table ya Students
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
class_id INT NOT NULL,
admission_number VARCHAR(100),
student_name VARCHAR(150) NOT NULL,
gender ENUM('Male','Female') NOT NULL,
date_of_birth DATE,
phone VARCHAR(30),
address VARCHAR(255),
guardian_name VARCHAR(150),
guardian_phone VARCHAR(30),
status ENUM('active','graduated','transferred','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);
8. Table ya Teachers
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
user_id INT,
teacher_name VARCHAR(150) NOT NULL,
gender ENUM('Male','Female'),
phone VARCHAR(30),
email VARCHAR(150),
address VARCHAR(255),
qualification VARCHAR(150),
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
);
9. Table ya Subjects
CREATE TABLE subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
subject_name VARCHAR(150) NOT NULL,
subject_code VARCHAR(50),
category VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

Mfano wa subjects:

INSERT INTO subjects (school_id, subject_name, subject_code, category)
VALUES
(1, 'Mathematics', 'MATH', 'Science'),
(1, 'English Language', 'ENG', 'Language'),
(1, 'Kiswahili', 'KIS', 'Language'),
(1, 'Physics', 'PHY', 'Science'),
(1, 'Chemistry', 'CHEM', 'Science'),
(1, 'Biology', 'BIO', 'Science'),
(1, 'History', 'HIST', 'Arts'),
(1, 'Geography', 'GEO', 'Arts'),
(1, 'Civics', 'CIV', 'Arts');

Link: https://faulink.com

10. Teacher Subjects

Table hii inaonyesha mwalimu anafundisha subject gani.

CREATE TABLE teacher_subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
teacher_id INT NOT NULL,
subject_id INT NOT NULL,
class_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);
11. Exams Table
CREATE TABLE exams (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
exam_name VARCHAR(100) NOT NULL,
term ENUM('Term 1','Term 2','Annual') NOT NULL,
exam_year YEAR NOT NULL,
start_date DATE,
end_date DATE,
status ENUM('open','closed') DEFAULT 'open',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

Mfano:

INSERT INTO exams (school_id, exam_name, term, exam_year, start_date, end_date)
VALUES
(1, 'Mid Term Exam', 'Term 1', 2026, '2026-03-01', '2026-03-10');
12. Marks Table

Hii ndiyo table muhimu kwa matokeo.

CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
student_id INT NOT NULL,
class_id INT NOT NULL,
subject_id INT NOT NULL,
exam_id INT NOT NULL,
marks DECIMAL(5,2),
grade VARCHAR(5),
remark VARCHAR(100),
entered_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

UNIQUE(student_id, subject_id, exam_id),

FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
FOREIGN KEY (entered_by) REFERENCES users(id)
);

UNIQUE(student_id, subject_id, exam_id) inazuia mwanafunzi kupewa marks mara mbili kwenye subject na exam moja.

13. CRUD ya Marks
Create Marks
INSERT INTO marks
(school_id, student_id, class_id, subject_id, exam_id, marks, grade, remark, entered_by)
VALUES
(1, 1, 1, 1, 1, 85, 'A', 'Excellent', 2);
Read Marks
SELECT * FROM marks;
Update Marks
UPDATE marks
SET marks = 90,
grade = 'A',
remark = 'Excellent'
WHERE id = 1;
Delete Marks
DELETE FROM marks WHERE id = 1;
14. Query ya Report ya Matokeo
SELECT
students.student_name,
classes.class_name,
subjects.subject_name,
exams.exam_name,
marks.marks,
marks.grade,
marks.remark
FROM marks
JOIN students ON marks.student_id = students.id
JOIN classes ON marks.class_id = classes.id
JOIN subjects ON marks.subject_id = subjects.id
JOIN exams ON marks.exam_id = exams.id
WHERE marks.school_id = 1;
15. Grade Automatic kwa CASE
SELECT
student_id,
marks,
CASE
WHEN marks >= 75 THEN 'A'
WHEN marks >= 65 THEN 'B'
WHEN marks >= 45 THEN 'C'
WHEN marks >= 30 THEN 'D'
ELSE 'F'
END AS grade
FROM marks;
16. Position ya Wanafunzi
SELECT
students.student_name,
SUM(marks.marks) AS total_marks,
AVG(marks.marks) AS average_marks,
RANK() OVER (ORDER BY SUM(marks.marks) DESC) AS position
FROM marks
JOIN students ON marks.student_id = students.id
WHERE marks.exam_id = 1
GROUP BY students.id, students.student_name;
17. Table ya Payments
CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
student_id INT NOT NULL,
payment_type VARCHAR(100) NOT NULL,
amount_required DECIMAL(10,2) DEFAULT 0,
amount_paid DECIMAL(10,2) DEFAULT 0,
balance DECIMAL(10,2) GENERATED ALWAYS AS (amount_required - amount_paid) STORED,
payment_date DATE,
payment_status ENUM('paid','partial','unpaid') DEFAULT 'unpaid',
received_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (received_by) REFERENCES users(id)
);
18. Report ya Malipo
SELECT
students.student_name,
payments.payment_type,
payments.amount_required,
payments.amount_paid,
payments.balance,
payments.payment_status
FROM payments
JOIN students ON payments.student_id = students.id
WHERE payments.school_id = 1;
19. Blog Posts Table
CREATE TABLE blog_posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
content LONGTEXT NOT NULL,
featured_image VARCHAR(255),
category VARCHAR(100),
tags VARCHAR(255),
status ENUM('draft','published') DEFAULT 'draft',
views INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
20. Subscriptions Table
CREATE TABLE subscriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
plan_name VARCHAR(100),
amount DECIMAL(10,2),
start_date DATE,
end_date DATE,
status ENUM('active','expired','cancelled') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
21. Activity Logs Table

Table hii ni muhimu kwenye mfumo mkubwa kwa kufuatilia matendo ya users.

CREATE TABLE activity_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(255) NOT NULL,
table_name VARCHAR(100),
record_id INT,
ip_address VARCHAR(100),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);

Mfano:

INSERT INTO activity_logs
(user_id, action, table_name, record_id, ip_address)
VALUES
(1, 'Added new student', 'students', 5, '127.0.0.1');
22. Settings Table
CREATE TABLE system_settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Mfano:

INSERT INTO system_settings (setting_key, setting_value)
VALUES
('site_name', 'FauLink School Management System'),
('site_url', 'https://faulink.com'),
('currency', 'TZS');
23. DESC / DESCRIBE Muhimu Sana

Kila ukitengeneza table tumia:

DESC users;
DESC schools;
DESC students;
DESC marks;
DESC payments;

Au:

DESCRIBE students;

Hii inakusaidia kuona:

Field
Type
Null
Key
Default
Extra
24. Index kwa Mfumo Mkubwa

Index hufanya search iwe faster.

CREATE INDEX idx_students_school ON students(school_id);
CREATE INDEX idx_students_class ON students(class_id);
CREATE INDEX idx_marks_student ON marks(student_id);
CREATE INDEX idx_marks_exam ON marks(exam_id);
CREATE INDEX idx_payments_student ON payments(student_id);
25. Views kwa Reports

View ni query iliyohifadhiwa.

CREATE VIEW student_result_report AS
SELECT
students.student_name,
classes.class_name,
subjects.subject_name,
exams.exam_name,
marks.marks,
marks.grade,
marks.remark
FROM marks
JOIN students ON marks.student_id = students.id
JOIN classes ON marks.class_id = classes.id
JOIN subjects ON marks.subject_id = subjects.id
JOIN exams ON marks.exam_id = exams.id;

Kutumia view:

SELECT * FROM student_result_report;
26. Stored Procedure ya Kuongeza Student
DELIMITER //

CREATE PROCEDURE AddStudent(
IN p_school_id INT,
IN p_class_id INT,
IN p_admission_number VARCHAR(100),
IN p_student_name VARCHAR(150),
IN p_gender VARCHAR(10),
IN p_guardian_name VARCHAR(150),
IN p_guardian_phone VARCHAR(30)
)
BEGIN
INSERT INTO students
(school_id, class_id, admission_number, student_name, gender, guardian_name, guardian_phone)
VALUES
(p_school_id, p_class_id, p_admission_number, p_student_name, p_gender, p_guardian_name, p_guardian_phone);
END //

DELIMITER ;

Kutumia:

CALL AddStudent(1, 1, 'ADM001', 'Juma Ally', 'Male', 'Ally Juma', '0712345678');
27. Trigger ya Activity Log
DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO activity_logs (user_id, action, table_name, record_id)
VALUES (NULL, 'New student registered', 'students', NEW.id);
END //

DELIMITER ;
28. Full Database Schema ya Mfumo Mkubwa

Kwa kifupi mfumo mkubwa utakuwa na tables hizi:

users
schools
classes
students
teachers
subjects
teacher_subjects
exams
marks
payments
blog_posts
subscriptions
activity_logs
system_settings
29. Full SQL ya Kuanzisha Mfumo
CREATE DATABASE faulink_school_system;
USE faulink_school_system;

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
username VARCHAR(100) UNIQUE NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(30),
password VARCHAR(255) NOT NULL,
role ENUM('super_admin','school_admin','teacher','student','accountant') DEFAULT 'student',
account_status ENUM('active','inactive','suspended') DEFAULT 'active',
trial_start_date DATE,
trial_end_date DATE,
subscription_start_date DATE,
subscription_end_date DATE,
is_paid TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE schools (
id INT AUTO_INCREMENT PRIMARY KEY,
school_name VARCHAR(200) NOT NULL,
registration_number VARCHAR(100) UNIQUE,
email VARCHAR(150),
phone VARCHAR(30),
address VARCHAR(255),
region VARCHAR(100),
district VARCHAR(100),
logo VARCHAR(255),
owner_user_id INT,
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (owner_user_id) REFERENCES users(id)
);

CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
class_name VARCHAR(100) NOT NULL,
class_level VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
class_id INT NOT NULL,
admission_number VARCHAR(100),
student_name VARCHAR(150) NOT NULL,
gender ENUM('Male','Female') NOT NULL,
date_of_birth DATE,
phone VARCHAR(30),
address VARCHAR(255),
guardian_name VARCHAR(150),
guardian_phone VARCHAR(30),
status ENUM('active','graduated','transferred','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
user_id INT,
teacher_name VARCHAR(150) NOT NULL,
gender ENUM('Male','Female'),
phone VARCHAR(30),
email VARCHAR(150),
address VARCHAR(255),
qualification VARCHAR(150),
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
subject_name VARCHAR(150) NOT NULL,
subject_code VARCHAR(50),
category VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE teacher_subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
teacher_id INT NOT NULL,
subject_id INT NOT NULL,
class_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

CREATE TABLE exams (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
exam_name VARCHAR(100) NOT NULL,
term ENUM('Term 1','Term 2','Annual') NOT NULL,
exam_year YEAR NOT NULL,
start_date DATE,
end_date DATE,
status ENUM('open','closed') DEFAULT 'open',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
student_id INT NOT NULL,
class_id INT NOT NULL,
subject_id INT NOT NULL,
exam_id INT NOT NULL,
marks DECIMAL(5,2),
grade VARCHAR(5),
remark VARCHAR(100),
entered_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE(student_id, subject_id, exam_id),
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
FOREIGN KEY (entered_by) REFERENCES users(id)
);

CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
school_id INT NOT NULL,
student_id INT NOT NULL,
payment_type VARCHAR(100) NOT NULL,
amount_required DECIMAL(10,2) DEFAULT 0,
amount_paid DECIMAL(10,2) DEFAULT 0,
balance DECIMAL(10,2) GENERATED ALWAYS AS (amount_required - amount_paid) STORED,
payment_date DATE,
payment_status ENUM('paid','partial','unpaid') DEFAULT 'unpaid',
received_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (received_by) REFERENCES users(id)
);

CREATE TABLE blog_posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
content LONGTEXT NOT NULL,
featured_image VARCHAR(255),
category VARCHAR(100),
tags VARCHAR(255),
status ENUM('draft','published') DEFAULT 'draft',
views INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE subscriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
plan_name VARCHAR(100),
amount DECIMAL(10,2),
start_date DATE,
end_date DATE,
status ENUM('active','expired','cancelled') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE activity_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(255) NOT NULL,
table_name VARCHAR(100),
record_id INT,
ip_address VARCHAR(100),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE system_settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
30. PHP Connection ya Mfumo Mkubwa
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "faulink_school_system";

$conn = mysqli_connect($host, $user, $password, $database);

if (!$conn) {
die("Database connection failed: " . mysqli_connect_error());
}
?>
31. PHP Prepared Statement ya Kuongeza Student
<?php
include "db.php";

if (isset($_POST['save'])) {
$school_id = $_POST['school_id'];
$class_id = $_POST['class_id'];
$admission_number = $_POST['admission_number'];
$student_name = $_POST['student_name'];
$gender = $_POST['gender'];
$guardian_name = $_POST['guardian_name'];
$guardian_phone = $_POST['guardian_phone'];

$stmt = $conn->prepare("
INSERT INTO students
(school_id, class_id, admission_number, student_name, gender, guardian_name, guardian_phone)
VALUES (?, ?, ?, ?, ?, ?, ?)
");

$stmt->bind_param(
"iisssss",
$school_id,
$class_id,
$admission_number,
$student_name,
$gender,
$guardian_name,
$guardian_phone
);

if ($stmt->execute()) {
echo "Student added successfully";
} else {
echo "Error: " . $stmt->error;
}
}
?>
32. Login Security

Password lazima ihifadhiwe kwa hash.

$password = password_hash($_POST['password'], PASSWORD_DEFAULT);

Kuhakikisha login:

if (password_verify($entered_password, $stored_password)) {
$_SESSION['user_id'] = $user['id'];
$_SESSION['role'] = $user['role'];
echo "Login successful";
} else {
echo "Invalid username or password";
}
33. Role Based Access

Mfano:

if ($_SESSION['role'] != 'super_admin') {
die("Access denied");
}

Au:

if ($_SESSION['role'] == 'teacher') {
echo "Teacher dashboard";
} elseif ($_SESSION['role'] == 'school_admin') {
echo "School admin dashboard";
} elseif ($_SESSION['role'] == 'super_admin') {
echo "Super admin dashboard";
}
34. Queries Muhimu za Dashboard

Idadi ya students:

SELECT COUNT(*) AS total_students FROM students WHERE school_id = 1;

Idadi ya teachers:

SELECT COUNT(*) AS total_teachers FROM teachers WHERE school_id = 1;

Idadi ya subjects:

SELECT COUNT(*) AS total_subjects FROM subjects WHERE school_id = 1;

Jumla ya payments:

SELECT SUM(amount_paid) AS total_paid FROM payments WHERE school_id = 1;

Wanafunzi kwa jinsia:

SELECT gender, COUNT(*) AS total
FROM students
WHERE school_id = 1
GROUP BY gender;
35. Backup ya Mfumo
mysqldump -u root -p faulink_school_system > faulink_backup.sql

Restore:

mysql -u root -p faulink_school_system < faulink_backup.sql
36. Best Practices kwa Mfumo Mkubwa
Tumia PRIMARY KEY kila table.
Tumia FOREIGN KEY kuunganisha tables.
Tumia ON DELETE CASCADE kwa data zinazotegemeana.
Tumia UNIQUE kuzuia duplicate.
Tumia INDEX kwa speed.
Tumia password_hash() kwenye PHP.
Tumia prepared statements.
Usitumie SELECT * kwenye reports kubwa.
Tumia created_at na updated_at.
Tumia backup kila siku.
Tumia activity logs.
Tenganisha users kwa roles.
Hakikisha kila school ina school_id.
37. Hitimisho

Ukijifunza database hii, utaweza kutengeneza mifumo mikubwa kama:

School management system
Student results system
Accounting system
Blog system
Subscription system
Admin dashboard
Payment tracking system
Multi-school system

Kwa mafunzo zaidi, projects na mifano ya code tembelea:

https://faulink.com