Notification System Using PHP and MySQL: From Triggers to Aggregation
Implementing a notification system in a PHP/MySQL web application often starts with a desire for simplicity and automation. In scenarios where traffic is moderate, such as an internal school portal receiving 500â1,000 daily visitors, developers frequently reach for database triggers to handle the heavy lifting of event logging. An AFTER INSERT trigger on a table like homeworks can automatically populate a notifications table with details like the sender ID, the subject title, and the class ID.
While this approach effectively decouples the notification logic from the core business logic, it introduces a critical limitation: the inability to efficiently track read status per individual user without inflating the data volume. When you query notifications using a join between notifications and user_class, you might retrieve hundreds of rows for a single user simply because every homework assignment in their class generates a row. This makes determining whether a specific notification has been "read" by a specific student impossible unless you introduce a granular read flag.
To solve the read/unread problem, the trigger must evolve from a simple INSERT statement into a more complex operation that iterates over the relevant users. Instead of blindly inserting one row for a class event, the trigger should check which students belong to the affected class and insert a record for each of them. In MySQL, this is achieved using a subquery within the trigger context:
CREATE TRIGGER `notify_new_homework` AFTER INSERT ON `homeworks`
FOR EACH ROW
INSERT INTO notifications (from_id, note, student_id, is_read)
SELECT
new.user_id,
CONCAT('A New homework titled: "', LEFT(new.title, 50), '" was added'),
uc.user_id,
'0' AS is_read
FROM user_class uc
WHERE uc.class_id = NEW.class_id;
This ensures that if a teacher posts an assignment, every student in that class receives their own distinct notification record. This granularity allows you to set is_read to '1' when a specific student clicks the link, without affecting other students in the same class.
However, this row-per-user model brings us to the next challenge: aggregation. Raw triggers create noise; they generate a new row for every single event. If 20 students comment on an assignment, you end up with 20 separate rows stating "X commented." Users find this overwhelming. The goal is often to group these events, perhaps displaying "15 people commented" instead of a long list.
Achieving aggregation requires shifting from pure trigger-based insertion to application-level logic or scheduled cleanup jobs. You cannot easily aggregate inside the AFTER INSERT trigger because you need historical data to determine if an existing notification should be updated rather than inserted.
The standard pattern involves querying for the most recent notification for a specific user regarding a specific resource (e.g., homework ID). If no row exists, insert one. If it does exist and the message count changes, update the note field to reflect the new total or append the name of the commenter if the threshold hasn't been met. This requires careful handling of string concatenation in SQL updates to avoid exceeding column size limits or creating malformed messages.
// Example PHP logic to handle aggregation
$notification = Notification::where('student_id', $userId)
->where('resource_id', $homeworkId)
->orderBy('created_at', 'desc')
->first();
if (!$notification) {
// Insert new aggregated notification
Notification::create([
'from_id' => $teacherId,
'note' => "Teacher posted an assignment",
'resource_id' => $homeworkId,
'student_id' => $userId,
'is_read' => 0
]);
} else {
// Update existing notification with aggregation logic
Notification::where('id', $notification->id)
->update([
'note' => "You and {$count - 1} others commented on this",
'updated_at' => now()
]);
}
This hybrid approach combines the reliability of triggers for initial data seeding with PHP logic for maintaining a clean, user-friendly feed. It acknowledges that while triggers are great for automation, they are not a silver bullet for complex UI requirements like aggregation and status tracking. For high-traffic public applications, you might eventually consider moving to an event queue or a dedicated messaging service, but for closed internal systems, this SQL/PHP combination remains robust and cost-effective.