How to build a fast, good scaling user notification system pt. 1

Problem definition:

Most social media sites have something called user notifications. Systems specially designed to generate user actions. Every notification, now matter how insignificant can generate a lot of click traffic. But how about the inside of the system? In school, or some book on SQL you would find something like this:

1. Make a table with users;
2. Make a table with notifications;
3. Make a table with many to many relation that describes who has what notification;

What actually happens is You have to make one big or two small queries. The big one is a JOIN query that selects the relations of the notifications and the data itself/ It ultimately looks something like this:

Select * from user_to_notifications u JOIN notifi_data n on u.n_id = n.id WHERE u.user_id = 1 ORDER BY u.date DESC

The two small queries would be a select for relations and a "where id in" select of the data.

There are a lot of problems with this solution. First the data is a unique notification that will be used/viewed only one time and won't ever change "Your friend [friends login] has birthday in [calculated time from friends birth time]". If you have many unique actions like user activity notifications that generates some other user activity, you will have two very large tables, very fast. Event though you can buy another SQL sever and try to scale horizontally, web server scaling is much cheaper, easier and gives better results in such problems.

To summarize the problem definition:

1. A whole lot of unique notifications - the more notifications, the more clicking;
2. Big read-write ratio - users are usually notified in the main screen about every action, generally generating a lot of selects and only some inserts;
3. Limited pagination of notifications - your users don't need to have 50+ pages of notifications, 10 suffice;
4. Complicated (slow) SQLs with joins and order bys - you don't need those ;-)

Well that's all for now, Christmas if a busy season... In the second part of my post I will discuss some techniques you can use to avoid such problems and build a great notifications system.