Design pattern to build a GitHub-inspired timeline with relational database?

Question

Is there a design pattern for building a GitHub-inspired timeline? I'm trying to write a somewhat complex and versatile timeline system for my application. It is based around this concept:

[Subject] [Verb] [DirectComplement] [IndirectComplement] (metadata: [date])

So, in practice:

John created a new post called Beautiful Post (12/01 00:01)

John is the subject, created is the verb, Beautiful Post is the direct complement.

John commented "OMG" on Beautiful Post  (12/01 00:00)

John is the subject, commented is the verb, "OMG" is the direct complement and Beautiful Post is the indirect complement.

I'm working with Symfony2 and Doctrine, running MySQL. I have created an entity named Timeline which stores, as string, the model of the Subject, DirectComplement and IndirectComplement, as well their IDs. Then, manually, i make the proper queries in order to fetch the objects of each one.

Is there a proper way of doing this with Doctrine and MySQL? A more elegant and versatile approach which does not make me crazy and forcing me to make an absurd amount of queries and foreachs?

Answer 1

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:

1. Group related notifications by user, type or context
2. Filter notifications by action type, context type and specific actors (through join)
3. 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:

• What's the best manner of implementing a social activity stream?
• How to implement the activity stream in a social network
• Facebook like notifications tracking (DB Design)
• Building a notification system
• Database design to store notifications to users