Database Schema - Primary School Management System

Overview

The Primary School Management System uses a MySQL database with eight core tables that manage students, parents, teachers, classes, gym memberships, salaries, contact forms, and administrative access.

All tables use auto-incrementing primary keys for their ID fields unless otherwise specified.

Tables

Student1

Stores student information and links students to their parents and classes.

Sid

INT

required

Auto-incrementing primary key for student identification

Sname

VARCHAR

Student’s first name

Ssurname

VARCHAR

Student’s last name

Sbirthday

DATE

Student’s date of birth

Parent_ID

INT

Foreign key reference to the Parent table

Class_ID

INT

Foreign key reference to the Class table

Relationships

Parent: Many-to-one relationship via Parent_ID
Class: Many-to-one relationship via Class_ID
Gym: One-to-one relationship (students can have gym memberships)

Example Query

INSERT INTO Student1 (Sname, Ssurname, Sbirthday, Parent_ID, Class_ID) 
VALUES ('John', 'Smith', '2015-03-15', 1, 2);

SELECT Sid, Sname, Ssurname, Sbirthday, Parent_ID, Class_ID 
FROM Student1;

Parent

Stores parent and guardian contact information.

Parent_ID

INT

required

Auto-incrementing primary key for parent identification

Pname

VARCHAR

Parent’s first name

Psurname

VARCHAR

Parent’s last name

Paddress

VARCHAR

Parent’s residential address

Pemail

VARCHAR

Parent’s email address for communication

Relationships

Student1: One-to-many relationship (one parent can have multiple children)

Example Query

INSERT INTO Parent (Pname, Psurname, Paddress, Pemail) 
VALUES ('Michael', 'Smith', '123 Main Street, Manchester', 'michael.smith@email.com');

SELECT Parent_ID, Pname, Psurname, Paddress, Pemail 
FROM Parent;

Teacher

Stores teacher information including contact details and compensation.

Teacher_ID

INT

required

Auto-incrementing primary key for teacher identification

bonus_amount

DECIMAL

Bonus compensation amount for the teacher

teacher_field

VARCHAR

Teacher’s subject specialization or field of expertise

Tname

VARCHAR

Teacher’s first name

Tsurname

VARCHAR

Teacher’s last name

Taddress

VARCHAR

Teacher’s residential address

Tmobile

VARCHAR

Teacher’s mobile phone number

Temail

VARCHAR

Teacher’s email address

Relationships

Class: One-to-many relationship (one teacher can manage multiple classes)
Salary: One-to-many relationship (teachers can have multiple salary records)

Example Query

INSERT INTO Teacher (bonus_amount, teacher_field, Tname, Tsurname, Taddress, Tmobile, Temail) 
VALUES (500.00, 'Mathematics', 'Sarah', 'Johnson', '45 Oak Avenue, London', '07700900123', 'sarah.j@school.edu');

SELECT Teacher_ID, bonus_amount, teacher_field, Tname, Tsurname, Taddress, Tmobile, Temail 
FROM Teacher;

Class

Stores class information including year group and assigned teacher.

Class_ID

INT

required

Auto-incrementing primary key for class identification

classYear

VARCHAR

Year group or class name (e.g., ‘Year 1’, ‘Year 2’)

capacity

INT

Maximum number of students allowed in the class

Teacher_ID

INT

Foreign key reference to the Teacher table

Relationships

Teacher: Many-to-one relationship via Teacher_ID
Student1: One-to-many relationship (one class contains multiple students)

Example Query

INSERT INTO Class (classYear, capacity, Teacher_ID) 
VALUES ('Year 3', 30, 1);

SELECT Class_ID, classYear, capacity, Teacher_ID 
FROM Class;

Gym

Stores gym membership information for students with various membership tiers.

gymMemberID

INT

required

Auto-incrementing primary key for gym membership identification

Sid

INT

Foreign key reference to the Student1 table

gymFullName

VARCHAR

Full name of the gym member

userRegDate

DATE

Registration date for the gym membership

memberType

VARCHAR

Membership tier: ‘bronzeMember’, ‘silverMember’, ‘goldMember’, or ‘diamondMember’

medicalCondition

TEXT

Any medical conditions or health notes for the member

Relationships

Student1: Many-to-one relationship via Sid

Membership Duration

Bronze

30 days membership duration

Silver

60 days membership duration

Gold

90 days membership duration

Diamond

180 days membership duration

Example Query

INSERT INTO Gym (Sid, gymFullName, userRegDate, memberType, medicalCondition) 
VALUES (1, 'John Smith', '2026-01-15', 'goldMember', 'Asthma - uses inhaler');

SELECT Sid, gymMemberID, gymFullName, userRegDate, memberType, medicalCondition 
FROM Gym;

Salary

Stores salary records for teachers including payment amounts and working hours.

Salary_ID

INT

required

Auto-incrementing primary key for salary record identification

Teacher_ID

INT

Foreign key reference to the Teacher table

salary_amount

DECIMAL

Base salary amount for the teacher

workingTimes

VARCHAR

Working hours or schedule information

Relationships

Teacher: Many-to-one relationship via Teacher_ID

Example Query

INSERT INTO Salary (Teacher_ID, salary_amount, workingTimes) 
VALUES (1, 35000.00, 'Full-time: 40 hours/week');

SELECT Salary_ID, Teacher_ID, salary_amount, workingTimes 
FROM Salary;

Contact

Stores contact form submissions and feedback from users.

contact_ID

INT

required

Auto-incrementing primary key for contact record identification

contactName

VARCHAR

Name of the person submitting the contact form

returnContact

VARCHAR

Email address or phone number for follow-up communication

message

TEXT

Message content from the contact form

Example Query

INSERT INTO Contact (contactName, returnContact, message) 
VALUES ('Jane Doe', 'jane.doe@email.com', 'Question about enrollment dates for next term');

SELECT contact_ID, contactName, returnContact, message 
FROM Contact;

admin_login

Stores administrative user credentials for system access.

admin_user

VARCHAR

required

Administrator username for login

admin_pass

VARCHAR

required

Administrator password for authentication

This table stores credentials. In production environments, passwords should be hashed using secure algorithms like bcrypt or Argon2, not stored in plain text.

Example Query

SELECT * FROM admin_login 
WHERE admin_user = 'username' AND admin_pass = 'password';

Database Relationships Diagram

┌─────────────┐
│   Parent    │
└──────┬──────┘
       │ 1:N
       │
┌──────▼──────┐      ┌─────────────┐
│  Student1   ├──────┤    Class    │
└──────┬──────┘ N:1  └──────┬──────┘
       │ 1:1                │ N:1
       │                    │
┌──────▼──────┐      ┌──────▼──────┐
│     Gym     │      │   Teacher   │
└─────────────┘      └──────┬──────┘
                            │ 1:N
                            │
                     ┌──────▼──────┐
                     │   Salary    │
                     └─────────────┘

Standalone Tables:
├── Contact (contact forms)
└── admin_login (authentication)

Common Queries

Get All Students with Parent and Class Information

SELECT 
    s.Sid,
    s.Sname,
    s.Ssurname,
    s.Sbirthday,
    p.Pname AS parent_firstname,
    p.Psurname AS parent_lastname,
    c.classYear
FROM Student1 s
LEFT JOIN Parent p ON s.Parent_ID = p.Parent_ID
LEFT JOIN Class c ON s.Class_ID = c.Class_ID;

Get Teacher with Their Classes and Salary

SELECT 
    t.Teacher_ID,
    t.Tname,
    t.Tsurname,
    t.teacher_field,
    c.classYear,
    s.salary_amount,
    t.bonus_amount
FROM Teacher t
LEFT JOIN Class c ON t.Teacher_ID = c.Teacher_ID
LEFT JOIN Salary s ON t.Teacher_ID = s.Teacher_ID;

Get Students with Active Gym Memberships

SELECT 
    s.Sname,
    s.Ssurname,
    g.gymFullName,
    g.memberType,
    g.userRegDate,
    g.medicalCondition
FROM Student1 s
INNER JOIN Gym g ON s.Sid = g.Sid;

Best Practices

Use Prepared Statements

Always use prepared statements or parameterized queries to prevent SQL injection attacks

Validate Foreign Keys

Ensure Parent_ID, Class_ID, and Teacher_ID exist before inserting student or class records

Data Validation

Validate email formats, date ranges, and required fields at the application level

Index Performance

Consider adding indexes on foreign key columns for better query performance

The current codebase uses string concatenation for SQL queries, which is vulnerable to SQL injection. Migrate to prepared statements using mysqli_prepare() and mysqli_bind_param().

Database

Pages

Documentation Index

​Overview

​Tables

​Student1

​Relationships

​Example Query

​Parent

​Relationships

​Example Query

​Teacher

​Relationships

​Example Query

​Class

​Relationships

​Example Query

​Gym

​Relationships

​Membership Duration

Bronze

Silver

Gold

Diamond

​Example Query

​Salary

​Relationships

​Example Query

​Contact

​Example Query

​admin_login

​Example Query

​Database Relationships Diagram

​Common Queries

​Get All Students with Parent and Class Information

​Get Teacher with Their Classes and Salary

​Get Students with Active Gym Memberships

​Best Practices

Use Prepared Statements

Validate Foreign Keys

Data Validation

Index Performance

Build docs developers (and LLMs) love

Overview

Tables

Student1

Relationships

Example Query

Parent

Relationships

Example Query

Teacher

Relationships

Example Query

Class

Relationships

Example Query

Gym

Relationships

Membership Duration

Example Query

Salary

Relationships

Example Query

Contact

Example Query

admin_login

Example Query

Database Relationships Diagram

Common Queries

Get All Students with Parent and Class Information

Get Teacher with Their Classes and Salary

Get Students with Active Gym Memberships

Best Practices