Activity DB Design Discussion
-
This thread has been created from this thread since the topic of discussion changed from syncing content to database and component architecture.
First of all, let’s clear a few things up:
1. The activity table is not a cache table, so the preconceived notions of what “cache” means don’t apply when considering solutions. It may contain data that has been pre formatted, but it still contains all the pointers back to the original content. It is quite possible to reconstruct all of the data from the information stored in the activity table. Remember “format_activity” functions?
The table is named “_cached” because this is a leave over from how the component used to work. I have not found a decent reliable way to change the name of the table that will not break a percentage of installations. This leads me onto number 2.
2. What people have talked about so far is the way the activity component used to work. It used to just be a table containing only pointers back to the original content. The stream would be re-built every four hours. The result would be populated in the “_cache” table which is a small part of what you see today. This would then be used to render the stream.
In reality this was just too expensive. Of course we can normalize the database until the cows come home, but in reality this isn’t going to cut it on a low end server. What I don’t want is for BuddyPress to be limited to high end servers with beautiful caching solutions. In reality, I want this thing to run on cheap hosts where someone can just throw it up there and it doesn’t take the server down.
Now, none of that is an excuse for poor database design – and I’m not saying BuddyPress has poor database design – but I’ll be the first to admit it could be better. Rome wasn’t built in a day, and I have no doubt that the schema is going to improve significantly over the next several versions and your help as a developer is appreciated. My philosophy is release early, often and iterate. No one is going to build a perfect solution straight off the bat, let’s get it out there and get people using it.
What we don’t want to do is start talking and discussing changes that fit the way things work right now. Here’s something to consider. I think the activity stream is going to be “the” central point to everything in BuddyPress. As an example, when the photos/album/gallery component is built, images will just be uploaded as an activity stream entry and appear on the activity stream. Any comments on the photo/photos will be activity stream comments. When you look at the permalink for that photo, it will just be an activity permalink with the comments showing.
What I’m saying is *everything* is an activity, regardless of what the content actually is. Rather than thinking of the activity stream as just a place where everything is aggregated, the activity stream will be the place where everything actually happens/is stored. The activity stream has almost everything you need now, powerful filtering, threaded commenting, permalinks and the ability to delete (and soon edit).
Separate interfaces could be built to perhaps display uploaded images in a different fashion, or private messages in a typical email style. However, in reality all of these are still activities.
You could compare this to the posts table in WordPress which is the heart of the software. Everything on the database level is considered a post, but on all levels above the database the content is defined by the “post_type”. Rather than creating multiple new tables for each content type, why not just use the activity table but be able to denote different types of content using identifiers or types?
These are all just ideas and nothing is anywhere near set in stone, however this all something to consider when talking about changes to the schema or debating how it works.
Let’s start some positive discussions about suggestions for small iterative improvements. But — I don’t want to hear about how WP does it wrong. At the end of the day WP is wildly successful and runs on most servers, fulfilling most people’s needs without a problem. Let’s keep it constructive.
-
Okay, as I was thinking about and writing a reply to Andy’s OP, there have been many posts. So, I will post what I have to say first before reading the rest of this thread. I’ll post again if I have anything to say with regards to other people’s comments
First of all, I want to say thank you for starting this thread. This type of discussion is not something that can been done effectively on IRC. Secondly, it’s clear that the trajectory of the referring thread has struck a nerve in you.
My comments were an attempt to start a healthy debate and not meant as a major criticism. Your work on the BuddyPress core is very commendable and much appreciated. So, let me reiterate, as I stated in my first post in that thread, I’m excited with the direction BuddyPress is headed. I believe that v1.2 will be a major, beneficial update to the platform.
The vision that you’ve outlined here helps frame the discussion going forward. Up until now, we, or at least I, did not have a clear idea about your vision. The roadmap is of course only a listing of features. It is not a statement of your design philosophy or design goals. All that the majority of us could discern about your vision (and here I particularly mean developers) was from what we saw in each changeset and a few morsels gleamed from IRC here and there.
With that said, I think this is the most crucial statement:
What I don’t want is for BuddyPress to be limited to high end servers with beautiful caching solutions. In reality, I want this thing to run on cheap hosts where someone can just throw it up there and it doesn’t take the server down.
This is a lofty but worthy goal! In my mind, I look at WPMU and BuddyPress as platforms best utilized on more robust setups. To that end, my ideas, my suggested approaches, have been geared toward a higher-end user. But now that this particular goal of yours has been stated, I have a very clear idea of what guides your BuddyPress design decisions.
Your ideas about the activity stream are interesting.
Rather than creating multiple new tables for each content type, why not just use the activity table but be able to denote different types of content using identifiers or types?
This will require some thought if blog posts, forum posts, and other content types are to be successfully integrated into this vision. I guess for purposes of backend discussion, it might be best to refer to this envisioned table, or set of related tables, as content tables instead of activity tables.
Activity of any type would be recorded in the appropriate content table and thought of as primary content and secondary content. Primary content is the object of the activity—a post (in a blog or forum), a picture (any piece of media), an accepted friendship, etcetera. Secondary content would be any response to the primary content—comments, retweets, etcetera.
As far as proposed schema changes, that all depends on how this vision is charted out. So, perhaps it’s best to figure that out before debating, or even professionally arguing, about DB design.
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.
I’d ultimately love to see things go in that direction, and have been sifting through code the past few days thinking of how to get more red in the trac than green.
However, if this was the case, and activity had a component scope and a serialized array of return values, then you could replace private messaging with a threaded activity stream that only the users involved in that thread can view. You could send activity to multiple groups at a time, or only 1, or all of your groups, or the entire site, or any other registered components serialized set of values that mean whatever they mean.
You can almost replace the notifications class all together too, because if you take a count of the number of activities a user has directed at them and store it in usermeta, when the user returns if that number is higher, there’s your total notifications. Filter those new activities and now you can see how many of each. Actually, the code already exists within both WP and BP to do this.
In that regard, the activity stream does start to replace core communication components like forums, private messages, status updates, notifications, and the wire. At that same time, BuddyPress becomes less about separate components with their own API’s and classes and subsets of functions, but more about creating new methods for users to interact with each other in specific scopes of communication.
You could take things like twitter lists and make them work both ways, where you could send a message/comment/update/picture to only specific users instead of only viewing tweets from users. You could create an endless array of ways and names for collaborative tools to develop the platform from within the platform. It’s basically the blog post_types concept attached to people’s activity instead of re-categorizing blog posts.
The way to further develop groups would need a whole new topic, but I’ve got some ideas for that too.
@mrmaz, what you propose is the fundamentals of how I’ve approached my development before finding WP; start with small basic classes and work my up in functionality. There very easily could be a basic “bp_component” class where every new component just extends off of that, and sets the needed vars and assigns the needed functions accordingly.
- The topic ‘Activity DB Design Discussion’ is closed to new replies.
