Merging user activities into one stream with a common representation

Question

My social network website currently has

• forum threads and posts
• gallery pictures
• albums on the users' profiles
• group discussions
• classifieds

I would like to create one "stream" of activity. It would be based on friend relationships or as a subscription. Once you are a friend with another member or you subscribe to their account, all the activity of this member would show up in your stream.

There are a couple of issues with this. It must be quite a common problem too, I suppose, although most apps only deal with one type of data (images, forum posts, ...).

1. The information currently is stored in separate MySQL tables. There is one table for uploads to the album pictures (and a related table about the albums), one for threads, one for posts, etc. I would need to query all the activities of each user that a member is subscribed to. I did try to create a VIEW with a UNION, as mentioned here. But this will create a huge query and the page load time will be insufficient to support this feature. What is the best and most efficient way to merge all this activity of a user?

2. From a graphical design perspective, what is the best way to merge these diverse datasets into a common representation? The pictures obviously are best represented with a thumbnail. I could show them in a pinterest style gallery. The forum posts, on the other hand, may contain text only. This would break the layout.

Answer 1

For the data side of this. You'll have to decide whether to use a relational database? Some other database variants might be more suited.

If you choose to use a relational database, you could use the following tables:

1. A table that models the temporal dimension of the activity stream, E.g.:

   <activity_id, user_id, timestamp>

2. A table containing tumbnails/fragments to show as summary in the timeline/activity stream. Using blobs or file system references to store the tumbnails. Checkout the question performance - Storing Documents as Blobs in a Database - Any disadvantages? on Stackoverflow. The table would look like:

   <activity_id, mime_type, summary_blob>

   The mime type is used to interpret and render the summary_blob. It allows supporting a large and extendible set of types of activities and storing all in the same table.

3. A table containing the full activities (e.g. textual posts, full size images, video, etc.) as blobs or as file system references, i.e.:

   <activity_id, mime_type, full_blob>

To following consideration and options are available:

• Combine the timestamp table with the summary_blob table. The proposed solution has these as separate tables to allow other relations besides the temporal activity stream (e.g. activities per topic, conversations between persons, and activities explicitly linking to others). But if you only want time based activity streams, the timestamp and summary_blob tables can be combined into a single table.

• The summary and full_blob tables are separate to avoid loading the full_blob into memory when only the summary is required. If you use filesystem resources for the full_blobs, these tables can be combined.

Alternatively you could use one activity stream table with separate tables for each type of activity referencing back to the activity stream keys. This might be similar to you current not performing within requirements solution, depending on the current implementation. Again you can use summaries to spead up loading. Checkout question oop - Designing SQL database to represent OO class hierarchy on stackoverflow. For example:

Activitystream table: <activity_id, activity_type, user_id, timestamp>
Image uploads table: <activity_id, image_data>
Text posts table: <activity_id, post>

Here you still have to search through all tables to create the activity stream, but only in the activitystream table you have to search through the timestamps, in the other tables you can pick the relevant records by key. Do not try to merge all tables, but let the image rendering routing use the image uploads tables, let the text post displayer use the text posts table, etc.