Re: Activity DB Design Discussion
Cool man, I don’t want anyone to think I only just complain, hehe. I have never been good at sugar coating stuff, and my wife gives me hell all the time about it. Btw, I have a 2 y/o and a pregnant wife, so I reserve the right to be snarky, lol.
Check out my registry for auto-embedding rich media services. I think it could be adapted to work for activity streams, although I guess it would have to be de-PHP5’d 
Registry & Service Template Methods
https://plugins.trac.wordpress.org/browser/buddypress-links/tags/0.2-RC1/bp-links-embed-classes.php
Services
https://plugins.trac.wordpress.org/browser/buddypress-links/tags/0.2-RC1/bp-links-embed-services.php
To solve the performance problem, each “service” would need to define a method that denormalizes itself for storage in the table that is queried (it gets hammered). This is very similar to format_activity(), except the abstract activity registry class would define strict rules for the format of data that is returned, then format the output itself from this “cached” denormalized data. We are back to square one with the need to refresh the denormalized data periodically, but I can think of a few ways to lessen the hit. One would be to have a timestamp (or other freshness indicator) column so only records that have changed are refreshed. It would be ideal to refresh the data with a pure SQL query, but then the components would have to provide the SQL to do it, and that would get very messy.
In the end you have an activity stream class interface that basically says… you want to record activity? Ok, extend me and define these methods. You have to return data in exactly this format. Add yourself to my registry, and if you followed directions, I will do all of the heavy lifting for you. Otherwise, get lost.
LMK if any of this sounds good to you, or if we are way off of the same page.
