What's the easiest method to handle many-to-many trigger associations such as the Twitter "follow this user" problem.
I've got a similar trouble with customers "watching" threads for replies. If you will find 10,000 customers watching a thread, and someone replies, just how to inform the viewers? All I'm able to think about may be the following:
Upon place, look into the "watch table" [fields: user_id, thread_id] for just about any thread matching this thread's id. This is actually the listing of customers I have to inform. For every user that should be informed, place a row in to the "notification table" [fields: user_id, message, addedon, etc]. Now I'm able to show any user their notices via this table.
Issue is, all of this sounds very, very costly. Particularly the 10,000 card inserts part.
There has to be an easy method to get this done... ideas?
Inside your watch table you could include a
last_updated area and hang that after the viewed thread is up-to-date. Include a area for
last_notified, set this whenever you inform the consumer, you will be aware you have to inform the consumer if
last_updated > last_notified. When you choose you need to inform the consumer just demonstrate to them all messages in the thread having a
post_date >= last_updated.