I have comments, ratings, blog posts, media posts, connecting with users etc etc on my site. Obviously, each feature has it's own set of tables, classes and methods. How would I go about creating an activity stream featuring all of these things for the entities that the user owns, but also the the users friend owns? Here are a couple of options:
1. just loop through all the users friends and request that data from lots of tables - lots of sub-queries to get appropriate media, names etc.
Pro: fresh, unduplicated data. Con: heavy processing - even if we cache the results.
2. query as above, but store each users feed in a file (xml or something). Run a cron job to regularly update the file, e.g every 12hrs. Then on viewing the stream, request and merge all relevant XML files.
Pro: easy format to use in other applications (like RSS feed) Cons: one hell of a cron job to be constantly running!
3. duplicate the data into a "feeds" table. For example, as a comment is written, as well as storing it to the comment table, store that data in a "feeds" table. I think elgg does something similar to this.
Pro: easy SQL lookup for all activity Con: duplicate data, got to make the storage generic enough to handle any type of activity.
Any thoughts, methods, projects that could help me find the best solution?
Thanks.