subscription algorithm that scales

Question

Web application PHP,Mysql. Users can write articles. How to build algorithm that allows users to subscribe (follow) to other users articles and then see list of last articles added by subscribed users? Algorithm must scale, so that one user can subscribe to 10 000 users and 10 000 users can subscribe to one user and all parts works quick - when new article added and when users looks for last articles from subscribed users.

Answer 1

create table `user`(
  `id` INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT
);

create table `subscribes_to` (
  `subscriber_user_id` INT(10) NOT NULL,
  `subscribed_to_user_id` INT(10) NOT NULL, # Receiver of the subscription  
  PRIMARY KEY(`subscribe_user_id`, `subribed_to_user_id`),
  KEY `subscriber(`subscribe_user_id`),
  KEY `subscribed(`subscribed_to_user_id`);
);


# Users subscribed to role 100
SELECT distinct u.* FROM user u
  JOIN subscribes_to st ON st.subscriber_user_id = u.id
WHERE
  st.subscribded_to_user_id = 100;

# User 100's subsriptions   
SELECT distinct u.* FROM user u
  JOIN subscribes_to st ON st.subscribded_to_user_id = u.id
WHERE
  st.subscriber_user_id = 100;  

Additional Schema to show relationship with articles:

article ( id int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255), body TEXT, date_created DATETIME, date_updated DATETIME, author_user_id int(10) );

# Create new article
INSERT INTO `article` VALUES (NULL, "Hello", "This is the body", NOW(), NOW(), 1);

# Find the last 10 articles posted that user 15 suscribes to
# the author of
SELECT a.* FROM article a
    JOIN user ON u.id = a.author_user_id
    JOIN subscribes_to st ON st.subscribed_to_user_id = u.id
WHERE st.subscriber_user_id = 15 ORDER BY a.date_created DESC LIMIT 10;