1

I'm implementing a design for an activity feed using the suggestion from the question: How to implement the activity stream in a social network

If I had a schema like this, how could I best query for the activity feed where activities may have corresponding tags (meaning I need to join some other tables to get tag data).

Example:

Activity 1: user4 tagged publication with tag1, tag2, tag3
Activity 2: publication rated 3 by user2
Activity 3: publication subscribed to by user9`

Activity table:

id             
user_id       (int)
activity_type (tinyint)
source_id     (int)  
parent_id     (int)
parent_type   (tinyint)
time          (datetime but a smaller type like int would be better)

My ideas on how to implement this are:

1. Do 1 query with a LEFT JOIN then assign tags to corresponding activities with javascript.

Or

2. Store tags as JSON in a database field

Community
  • 1
  • 1
el_pup_le
  • 11,711
  • 26
  • 85
  • 142

1 Answers1

2

My choice would be the LEFT JOIN option. The tags don't have to be assigned with JavaScript though. If you are using a server-side language to output the stream, you can output tags at the same time.

Option #2 is bad because it prevents you from doing (efficient) queries on tags using SQL if ever you need to in the future.

simshaun
  • 21,263
  • 1
  • 57
  • 73
  • Wouldn't it be better to let the client side handle the workload for performance? Also, I've already got the tags with relationships in my database model, I'm just trying to figure out how to avoid excessive queries for my activity feed. – el_pup_le Feb 24 '12 at 01:39
  • For something as small as generating HTML for tags, you aren't really gaining anything by letting the client do the work. – simshaun Feb 24 '12 at 01:40
  • Even if it scales to millions of reads/second? Is there any harm in doing it with javascript? – el_pup_le Feb 24 '12 at 01:46
  • If you are having millions of reads/second, you'll have a lot more to worry about than generating a tiny bit of html for tags on the server-side. But, no, there is no harm in doing it with javascript, unless there are tons and tons of tags that could cause performance issues for visitors. The only thing you should probably avoid doing is making an AJAX request for every activity to fetch its tags. – simshaun Feb 24 '12 at 01:49