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

Diagram Tables The End

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

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.

 

RELATIONSHIP CARDINALITY

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 can be tied to zero or many courses. A student may take up multiple courses, but a system admin is not related to any of the courses. If your system only has “students and teachers”, then it’s a definite one-to-many relationship.
  • 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.

 

 

TABLE DETAILS

Now that we are done with the overview, let us get into more details on the entities or tables.

 

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 have separate tables for teachers, students, admin, and whatever… I find it kind of redundant. A single table with user_role field gets the job done.

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.

Well, courses that the school offers. My idea behind the start/end columns is such that we don’t have to create a separate “cohort” table. For example:

  • This single table will have multiple entries of “Computer Engineering YYYY”.
  • Instead of having a single “Computer Engineering” course entry, then another table to store multiple “Computer Engineer YYYY cohort” entries.

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, “Database Basics Lesson N” and “Python Programming Lesson M”. Once again, some of you may be thinking “those are different subjects”. 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 John 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, this is kind of an optional table. This is only useful if there are “exams” or “assignments” that are graded.

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

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.