Design pattern : notification system

Really this falls under the how do I design a car? type question category ...

The implementation depends on the design, where it will go in future and the environment you will implement in. You might choose a twitter-esque implementation, a SAN-backed system of XML, Relational Databases, Hadoop and tons of others.

A good knowledge of web technologies, experience, trial and error is the only way you can design any feature with certainty that you are doing it is the right(ish) way.

What are your performance needs? Traffic levels? User requirements? Do you want to distribute later (through webhooks for example?).

Your question needs to be more specific.

I personally would have a table of "notification-types" acting like an enum ... and then a user<>notification table handling the relationship between notification and user.

With some good coding, you can splice the user<>notification table across many servers and key on userid or similar ... and replicate the types table across each of those nodes for local cache/reference. Something like that.

Create a system queue, each message added to this queue has a list of "consumers" and the content. The main message pump processes each message and sends the message to all consumers.

Lets say 2 people befriend each other. You add a message to the main system queue that A is friends with B and consumers are both A and B. When your message "pump" (processor) sees this message it adds it to the queue of A and the queue of B. So now user A and user B have a new message that they are friends. In turn each user has a message processor, so when it sees a message called "I am friends with [someone]" it processes this as add a new entry to the "wall" visible to friends of A that "A is friends with B", etc.

This is overly simplistic but hopefully shows how message queues can be used for this (very similar system is used as the windows UI framework) so there is already an existing example and there are plenty of synchonized message queue patterns you can use.

Rest is up to you to design.