My social network website currently has
- forum threads and posts
- gallery pictures
- albums on the users' profiles
- group discussions
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, ...).
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?
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.