Attendance Management System ERD (Simple Version)

Last Updated: November 10, 2023

Welcome to a quick sharing of an ERD of an attendance management system. Now, “attendance” can cover a wide range of topics – From schools to events to companies, etc… So for this example, let us just fix it to “school”. You can change the entity names in your own project if that is not the case, read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

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

 

ATTENDANCE MANAGEMENT SYSTEM

Attendance Management System ERD (click to enlarge)

That’s right, this is kept as simple as the title of this post claims. This system is generalized into 3 groups of entities.

  • Users – The elephant in the room, the users of the system. Teachers, lecturers, students, staff members, etc…
  • Course & Classes – Courses that the school offers, and the classes held.
  • Attendance – Another “Captain Obvious”, to keep track of attendance.

P.S. If you are not working with a school, feel free to change “courses and classes”. For example, a company may be holding talks on a regular basis and wants to track attendance. In this case, it will be “venue and events”.

 

 

FOR REFERENCE ONLY

Before the not-so-intelligent ones start to scream “the diagram is not accurate” and “this is useless” – This is only an example. It is for reference only, and assumptions were made.

  • A user can be tied to zero or many courses.
    • A student can sign up for multiple courses, a teacher can 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 is a one to one/many relationship.
  • A course can have one or multiple classes. If your school is only offering “one-day courses”, then feel free to remove classes entirely.

The list can go on forever. You are building your own system now, so you decide the 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. Can also be VARCHAR if you use some sort of a “staff code” system.
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, and admin. Some people prefer to create more “link tables” – user-teachers and user-students. I don’t quite get it. 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 users to the courses. This can be rather tricky, what if a teacher can also sign up as a student? Create 2 different user accounts, or add the user role field to this table instead? You decide.

 

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.

Self-explanatory table, the courses that the school offers. Take note of the start and end dates, it is here to eliminate the need for a separate “semester” table. For example:

  • With semester table: Create a “Basic English” course. Then add cohorts to a separate semester table – Basic English 2023, Basic English 2024, etc…
  • With this table: Just create the courses and name them accordingly – “Basic English 2023 “, “Basic English 2024”, etc…

There are no right or wrong ways here. If you have a good reason why “semester” has 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, “Basic English Lesson 1” and “Basic English Lesson 2”.
  • Once again, some of you may be thinking “a course can have different subjects”. For example, “Basic English Grammer” and “Basic English Composition”.
  • 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) ATTENDANCE TRACKING

COLUMN DATA TYPE NOTES
class_id INTEGER Primary and foreign key.
user_id INTEGER Primary and foreign key.
signed_by INTEGER Foreign key.
attend_status VARCHAR Up to you to decide – 1 present, 0 absent, L late, E left early.
attend_time DATE TIME Timestamp when attendance is taken.
attend_notes TEXT Optional, notes on attendance.

Finally, the attendance table itself. Just a small note on “User ID” and “Signed By” –

  • “User ID” Attendance for this user/student.
  • “Signed By” Attendance taken by who – The teacher, staff, OR if you allow the student to “self-sign” with biometric. We don’t live in the past anymore. 😆

 

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

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!