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.

  • Why do you want to combine graphical design with database design? Aren't these two separate questions? Can't the graphical representation of activities and activity streams be insulated from the data about the activities? – Kasper van den Berg Jan 28 '15 at 14:39
  • I don't necessarily want to merge everything. But I am thinking how I can best show posts next to images (in a single stream of activities). – reggie Jan 28 '15 at 15:20

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.

  • Thank you for this useful answer. Instead of querying all the different information just-in-time, I will hook into the creation processes instead. – reggie Jan 30 '15 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.