I have a rather complicated community site (in php / mysql) with a number of “things” that registered users can “watch”.
"Things": messages, comments, event updates, user status changes, etc. At least 10 different types of “things” are available, and the user can observe any other “things” of the user (Think facebook)
“Watch” means: the user can watch any “thing” and see the feed of all updates / additions to each “thing” in all types of “things”.
I created a similar application with much fewer “things” to watch, and my setup is basically this:
'table tables' : id, userId, itemId [id in foreign table], itemClass [reference identifier of the external table]
The user can add a “clock”, an entry was created in the “watches” table, and then we polled this table (and cached it) as they looked, we could mark (in the user interface) if they were already viewing a certain subject or not, PLUS they could see and manage a list of everything in the system that they were observing. Pretty simple.
So (after this protracted introduction), this community site that we are building will need to scale more dramatically. Hundreds of “things” will be observed in thousands of users, and we will have to poll not only the “hours” table as such, but also each foreign connection table in order to get details for filling in each user chat.
If you are confused about what I mean, just think about how to feed your facebook friend, which you have on your Facebook homepage. How is this updated without starting a ton of queries?