School Management System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a school management system. Planning to build your own school management system? Here is my own simple version of the system, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

 

SCHOOL MANAGEMENT SYSTEM

This is not some “crazy complex system”, I generalized the system into 3 groups of entities:

• Users – The elephant in the room. Every system definitely has users.
• Course & Classes – Of course, it’s a school that offers education.
• Attendance & Grades – To keep track of the student’s progress.

 

FOR REFERENCE ONLY

Before the dumb trolls start to scream “the diagram is not accurate”, here’s a quick disclaimer on the relationship between the entities – This is only for reference and it is subjective.

• A user is tied to zero or many courses.
  • A student may take up multiple courses, a teacher may also teach multiple courses.
  • A system admin or technical staff is not related to any of the courses.
  • But of course, if your system only has “students and teachers” – Then it’s definitely one-to-one/many.
• A course can have one or multiple classes. If your school is running “one-day crash courses”, then it’s a one-to-one relationship. Feel free to remove the classes.

Yep, the list can go on, but you are the one building the system now. Make your own changes and decide your own rules.

 

 

 

PART 1) USERS

1A) USERS TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary Key.
user_role	VARCHAR	Up to you to decide. “T”eacher, “S”tudent, “A”dmin.
user_name	VARCHAR	User’s full name.
user_email	VARCHAR	Unique, user’s email.
user_password	VARCHAR	Password. Encrypt in your own system.

Some people prefer to create separate tables for teachers, students, admin, etc… What baffles me is that all the fields are the same. A single table with user_role field gets the job done? Really, don’t create multiple tables with the same dataset, unless you have good reasons to do so.

P.S. Feel free to add more fields as required – Address, telephone, gender, etc…

 

1B) USERS TO COURSES TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary and foreign key.
course_code	VARCHAR	Primary and foreign key.

Ties the user to the courses. For example, a student taking up multiple courses, or a lecturer teaching multiple courses.

 

 

PART 2) COURSES

2A) COURSES TABLE

COLUMN	DATA TYPE	NOTES
course_code	VARCHAR	Primary Key.
course_name	VARCHAR	The course name itself.
course_description	VARCHAR	Full description of the course.
course_start	DATE	Start date of the course.
course_end	DATE	End date of the course.

Courses that the school offers. The idea behind the start/end dates is such that we don’t have to create a separate “cohort” table. For example:

• With cohort table: Create a “Computer Engineering” entry in the courses table. Then, add “Computer Engineering 2023”, “Computer Engineering 2024” to the separate cohort table.
• This courses table: Just create “Computer Engineering 2023”, “Computer Engineering 2024”, etc…

Although there’s no right or wrong here. If you feel that “cohort” needs to be a separate table, go ahead and do that in your project.

 

2B) CLASSES TABLE

COLUMN	DATA TYPE	NOTES
class_id	INTEGER	Primary Key.
course_code	VARCHAR	Foreign Key.
class_description	VARCHAR	Optional, description of the class.
class_start	DATE TIME	Class start date and time.
class_end	DATE TIME	Class end date and time.

• For example, “Computer Engineering – Database Basics” and “Computer Engineering – Basic Python”.
• Once again, some of you may be thinking “those are different subjects”. Shouldn’t there be a separate courses-subjects table?
• Yep, I have omitted the “subjects” table here for simplicity. Feel free to add your own “subjects” table if you think it is necessary.

 

 

PART 3) PROGRESS TRACKING

3A) ATTENDANCE TABLE

COLUMN	DATA TYPE	NOTES
class_id	INTEGER	Primary and foreign key.
user_id	INTEGER	Primary and foreign key.
attend_time	DATE TIME	Timestamp when attendance is taken.

The “did this student go to school” table. Feel free to add another “attendance taken by” column if you want more “security”.

 

3B) GRADES TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary and foreign key.
course_code	VARCHAR	Primary and foreign key.
grade_score	VARCHAR	This can also be an integer, depending on your scoring system.

Finally, to keep track of the grades of the students.

P.S. Add another “grade_type” column here if you want to differentiate between “project”, “course work”, “assignment”, and “exam”.

 

 

 

LINKS & REFERENCES

• “I Was Here” PHP Student Attendance System – Code Boxx
• Entity-Relationship Diagram Symbols and Notation – Lucid Chart