About database schema
ActivityStrea.ms is a standard proposal for social activity streams like the one you want. There's a lot of similar posts here on SO mostly regarding database design of these activity streams (links at the end). Please, don't underestimate the reading of ActivityStrea.ms JSON Schema. I'm sure you will learn a lot from it.
I would suggest you to use this database design:
user_id | INTEGER | user being notified
actor_id | INTEGER | user performing the action
activity_type | STRING | classname/type of the object being notified
activity_id | INTEGER | id of the object being notified
context_type | STRING | classname/type of the object's parent
context_id | INTEGER | id of the object's parent
read/view_at | DATETIME | timestamp of when user saw it
So, for instance, if someone comments a post you would have something like:
$notification = array(
'user_id' => $id_of_the_user_being_notified
'actor_id' => $comment->user_id
'activity_type' => 'comment'
'activity_id' => $comment->id
'context_type' => 'post'
'context_id' => $comment->post_id
'read_at' => NULL
);
It seems unnecessary to have all those fields, but they will surely pay their cost.
With that design you can:
- Group related notifications by user, type or context
- Filter notifications by action type, context type and specific actors (through join)
- Easily purge notifications from objects that are deleted (suppose an user deletes a post that was commented. Notifications of its comments should disappear)
Note: Timestamps (created_at/updated_at) aren't really necessary. Since you will load the activity object (the comment record in that case), you will have its timestamps already. The only reason to have them duplicated is to query "notifications" by timestamps (you won't be able to use JOIN here). Anyway, feel free to add them as you see fit.
About Doctrine and Symfony
I can't say much for Symfony, but I'm sure Doctrine supports polymorphic queries. With that in mind, it should play nicely with that design.
On Laravel we use the approach of models implementing a NotifiableInterface. That way, different models can have their own logic of who gets notified by it and which is its context.
The application itself listen to model's create method and generates notifications when fit, so models and controllers don't have to deal with notifications themselves, are nicely decoupled and changing storage should be as easy as possible.
Example of NotifiableInterface
This is a pretty simple example of a NotifiableInterface. You should use it as inspiration and adapt it according to your needs.
interface NotifiableInterface {
// Returns a string representation of the type
// It may be a get_class($this), the model table
// or anything you like really.
public function get_type();
// Returns an identifier for the object (ie its ID)
// get_key is for compatibility with Laravel models
// but you may use get_id.
public function get_key();
// Returns the context object for this entity.
// It's advisable that this object also implements
// NotifiableInterface, so it also has get_type and get_key
public function get_context();
// Returns the user_ids to be notified.
// A comment, for instance, should notify the post owner
// as well as anyone else that commented that post.
public function should_notify();
}
Related Questions
Here are a few posts with plentiful information on that topic:
