Twitter-like "follow user" and "watch this" problem

Question

What's the best way to handle many-to-many trigger relationships like the Twitter "follow this user" problem.

I have a similar problem with users "watching" threads for replies. If there are 10,000 users watching a thread, and someone replies, how best to notify the watchers? All I can think of is the following:

Upon insert, check the "watch table" [fields: user_id, thread_id] for any thread matching this thread's id. This is the list of users I need to notify. For each user that needs to be notified, insert a row into the "notification table" [fields: user_id, message, addedon, etc]. Now I can show any user their notifications via this table.

Problem is, this all sounds very, very expensive. Especially the 10,000 inserts part.

There must be a better way to do this... ideas?

Answer 1

In your watch table you could add a last_updated field and set that when the watched thread is updated. Also add a field for last_notified, set this when you notify the user, you will know you need to notify the user if last_updated > last_notified. When you decide that you need to notify the user just show them all messages from the thread with a post_date >= last_updated.