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
Table Structure
Extras
The End
ER DIAGRAM
First, here is the entire ER diagram and a quick overview of the system.
ATTENDANCE MANAGEMENT SYSTEM
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
- “I Was Here” PHP Student Attendance System – Code Boxx
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
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!
